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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
11752 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
11752 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  
 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.05 seconds. Powered By: Snitz Forums 2000