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
 Other SQL Server Topics (2005)
 How to use a parameter in place of a literal?

Author  Topic 

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 10:40:14
I have a stored procedure that starts like this:

.
.
.
UPDATE Employees
set depth=0, hierarchy=NULL

UPDATE Employees
set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength)
where Child = Parent

WHILE EXISTS
(
SELECT *
FROM Employees
WHERE Depth=0
)
.
.
.
I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 10:44:33
there is none. you'll have to use dynamic sql

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 10:49:45
Any suggestions for sites with examples?
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 10:57:53
http://www.sommarskog.se/dynamic_sql.html


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-10-05 : 11:01:49
Thanks. I'll check it out.
Go to Top of Page
   

- Advertisement -