Dynamic Table Name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Table Name

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-23 : 09:55:02

Muhammad Iqbal writes "Dear SQL Team..
I have query statement in Query Analyzer that working well. I want to put that query statement into store procedure
But i also want if i execute that store procedure the table name just include in the parameter
exp : EXEC spRegCode tb_Customer

Thank's for your help


CREATE PROCEDURE [spRegCode]
AS
UPDATE EXP_0805 ---> I want the table name dynamic (from parameter)
SET REG_CODE =
    CASE 
	WHEN 	(SUBSTRING(MSISDN,5,4)>='5160' and SUBSTRING(MSISDN,5,4)<='5169') or
	     	(SUBSTRING(MSISDN,5,4)>='5180' and SUBSTRING(MSISDN,5,4)<='5189') or
		(SUBSTRING(MSISDN,5,4)>='6700' and SUBSTRING(MSISDN,5,4)<='6999')
		THEN '0101'

	WHEN 	(SUBSTRING(MSISDN,5,4)>='6320' and SUBSTRING(MSISDN,5,4)<='6320') or
   		(SUBSTRING(MSISDN,5,4)>='6325' and SUBSTRING(MSISDN,5,4)<='6326') or
		(SUBSTRING(MSISDN,5,4)>='5419' and SUBSTRING(MSISDN,5,4)<='5419') or 
		(SUBSTRING(MSISDN,5,4)>='5395' and SUBSTRING(MSISDN,5,4)<='5397')	
		THEN '0201'

	WHEN	(SUBSTRING(MSISDN,5,4)>='6300' and SUBSTRING(MSISDN,5,4)<='6319') or 
   		(SUBSTRING(MSISDN,5,4)>='6330' and SUBSTRING(MSISDN,5,4)<='6349') or 
   		(SUBSTRING(MSISDN,5,4)>='5377' and SUBSTRING(MSISDN,5,4)<='5379') or 
		(SUBSTRING(MSISDN,5,4)>='5339' and SUBSTRING(MSISDN,5,4)<='5339')
		THEN '0202'

	WHEN	(SUBSTRING(MSISDN,5,4)>='6360' and SUBSTRING(MSISDN,5,4)<='6369')
		THEN '0203'
		ELSE '0000'
    END

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-23 : 13:09:41

Search the site for Dynamic SQL
Also check out [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

HTH
Jasper Smith

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-08-23 : 13:52:41

Here's my stab at and hope this helps. Dynamic SQL is fun..not :)

Anthony
--**
create procedure [spRegCode]

@tablename varchar(100)

as
begin

declare @sql varchar(4000)

select @sql='update '+@tablename+' set reg_code = case
when
(SUBSTRING(MSISDN,5,4)>="5160" and SUBSTRING(MSISDN,5,4)<="5169") or
(SUBSTRING(MSISDN,5,4)>="5180" and SUBSTRING(MSISDN,5,4)<="5189") or
(SUBSTRING(MSISDN,5,4)>="6700" and SUBSTRING(MSISDN,5,4)<="6999")
then "0101"

when
(SUBSTRING(MSISDN,5,4)>="6320" and SUBSTRING(MSISDN,5,4)<="6320") or
(SUBSTRING(MSISDN,5,4)>="6325" and SUBSTRING(MSISDN,5,4)<="6326") or
(SUBSTRING(MSISDN,5,4)>="5419" and SUBSTRING(MSISDN,5,4)<="5419") or
(SUBSTRING(MSISDN,5,4)>="5395" and SUBSTRING(MSISDN,5,4)<="5397")then "0201"

when
(SUBSTRING(MSISDN,5,4)>="6300" and SUBSTRING(MSISDN,5,4)<="6319") or
(SUBSTRING(MSISDN,5,4)>="6330" and SUBSTRING(MSISDN,5,4)<="6349") or
(SUBSTRING(MSISDN,5,4)>="5377" and SUBSTRING(MSISDN,5,4)<="5379") or
(SUBSTRING(MSISDN,5,4)>="5339" and SUBSTRING(MSISDN,5,4)<="5339")
then "0202"

when
(SUBSTRING(MSISDN,5,4)>="6360" and SUBSTRING(MSISDN,5,4)<="6369")
then "0203"

else "0000"'

exec(@sql)
end
--**

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often and speaks to my voice mail :)

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-08-23 : 14:12:21

Wups...
--**
create procedure [spRegCode]

@tablename varchar(100)

as
begin

declare @sql varchar(4000)

select @sql='update '+@tablename+' set reg_code = case
when
(SUBSTRING(MSISDN,5,4)>="5160" and SUBSTRING(MSISDN,5,4)<="5169") or
(SUBSTRING(MSISDN,5,4)>="5180" and SUBSTRING(MSISDN,5,4)<="5189") or
(SUBSTRING(MSISDN,5,4)>="6700" and SUBSTRING(MSISDN,5,4)<="6999")
then "0101"

when
(SUBSTRING(MSISDN,5,4)>="6320" and SUBSTRING(MSISDN,5,4)<="6320") or
(SUBSTRING(MSISDN,5,4)>="6325" and SUBSTRING(MSISDN,5,4)<="6326") or
(SUBSTRING(MSISDN,5,4)>="5419" and SUBSTRING(MSISDN,5,4)<="5419") or
(SUBSTRING(MSISDN,5,4)>="5395" and SUBSTRING(MSISDN,5,4)<="5397")then "0201"

when
(SUBSTRING(MSISDN,5,4)>="6300" and SUBSTRING(MSISDN,5,4)<="6319") or
(SUBSTRING(MSISDN,5,4)>="6330" and SUBSTRING(MSISDN,5,4)<="6349") or
(SUBSTRING(MSISDN,5,4)>="5377" and SUBSTRING(MSISDN,5,4)<="5379") or
(SUBSTRING(MSISDN,5,4)>="5339" and SUBSTRING(MSISDN,5,4)<="5339")
then "0202"

when
(SUBSTRING(MSISDN,5,4)>="6360" and SUBSTRING(MSISDN,5,4)<="6369")
then "0203"

else "0000"
end'

exec(@sql)
end

"DBAs are overpaid and their jobs are easy...heck, I could do it."

-Quote from a former boss who calls often and speaks to my voice mail :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources