SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 How to use a parameter in place of a literal?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 10/05/2007 :  10:40:14  Show Profile  Send kirknew2SQL a Yahoo! Message  Reply with Quote
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

Slovenia
11751 Posts

Posted - 10/05/2007 :  10:44:33  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 - 10/05/2007 :  10:49:45  Show Profile  Send kirknew2SQL a Yahoo! Message  Reply with Quote
Any suggestions for sites with examples?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 10/05/2007 :  10:57:53  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 - 10/05/2007 :  11:01:49  Show Profile  Send kirknew2SQL a Yahoo! Message  Reply with Quote
Thanks. I'll check it out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000