table name as parameter to stored proc

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 2005 Forums

Transact-SQL (2005)

table name as parameter to stored proc

Author

Topic

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 01:29:12

how to pass tablename as a parameter for a stored procedure and use the same in the select query..

any code sample would be great.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-13 : 01:33:05

You can do this with dynamic SQL, but it is highly not recommended. It is very bad code to do it that way.

What are you trying to achieve and why?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-13 : 01:33:15

--Use Dyanamic Sql

create proc test(@Table_name sysname) as
Begin
exec ('Select * from '+@Table_name)
End

exec test 'sys.objects'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-13 : 01:33:54

<Rolls eyes> Still is very bad code even if T-SQL allows it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-13 : 02:29:05

Before you start writing dynamic sql, make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

ibin
Starting Member

26 Posts

Posted - 2009-10-13 : 02:29:08

Hi i basically want to update the #main table 's actdate and date column with values from #temp.the tables are as below. How can i do this.??

CREATE TABLE #Temp (
LinkID varchar(6),
Desc1-actdate datetime,
Desc1-date datetime,
Desc2-actdate datetime,
Desc2-date datetime)
)
INSERT INTO #Temp (LinkID , Desc1-actdate, Desc1-date,Desc2-actdate, Desc2-date) VALUES ('ABC','1/1/2009','1/2/2009','1/3/2009','1/4/2009')
INSERT INTO #Temp (LinkID , Desc1-actdate, Desc1-date,Desc2-actdate, Desc2-date) VALUES ('def','3/1/2009','5/2/2009','1/3/2009','1/4/2009')
INSERT INTO #Temp (LinkID , Desc1-actdate, Desc1-date,Desc2-actdate, Desc2-date)VALUES ('ghi','4/1/2009','6/2/2009','1/3/2009','1/4/2009')

CREATE TABLE #Lookup(
ID varchar(6),
Desc nvarchar(max))
INSERT INTO #Lookup(ID , Desc) VALUES ('a1','Desc1')
INSERT INTO #Lookup(ID , Desc) VALUES ('a2','Desc2')
INSERT INTO #Lookup(ID , Desc) VALUES ('a3','Desc3')

CREATE TABLE #Main (
LinkID varchar(6),
LookupID varchar(6),
actdate datetime,
date datetime
)
INSERT INTO #Main (LinkID , LookupID , actdate ,date) VALUES ('ABC','a1','1/2/2009','1/3/2009')
INSERT INTO #Main (LinkID , LookupID , actdate ,date) VALUES ('ABC','a2','1/2/2009','1/3/2009')
INSERT INTO #Main (LinkID , LookupID , actdate ,date) VALUES ('def','a1','3/1/2009','5/2/2009')
INSERT INTO #Main (LinkID , LookupID , actdate ,date) VALUES ('ghi','a3','4/1/2009','6/2/2009')
since i did not get anysolution thought of having individual select statements for each description and making it a union and finally updating the main table. which i know is not a good solution...

n here i want to pass the temptable name as parameter

query would look something like this.

Update #Main blah blah blah...

from #Main main JOIN

(

Select LinkID as idLink, 'a1' as LookupID ,
Desc1-actdate as actdate, Desc1-date as date from #Temp
UNION

Select LinkID as idLink, 'a2' as LookupID ,
Desc2-actdate as actdate, Desc2-date as date from #Temp

)tmpckl on blah blah blah

here this #temp i want to pass as parameters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources