Going Crazy with a variable in a SP

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

General SQL Server Forums

New to SQL Server Programming

Going Crazy with a variable in a SP

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-23 : 16:17:27

Help. This is half working. I have a SP that accept one argument, which is the name of a server.

--The Stored Procedure
alter procedure countdb3
	@servername sysname
as
select (select serverid from usslcrioprod.admin.dbo.servers where servername=@servername) as serverid, 
	    dbid, name, crdate, cmptlevel
from @servername.master.sys.sysdatabases

The last line of this kills the whole thing. Everything else is correct and if I replace the variable name with the static server name, its awesome. But this needs to be a variable. Can anyone help me here? Notice @servername is used in the select statement above and it works perfectly. HELP!

Craig Greenwood

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 16:37:01

try:
exec('select (select serverid from usslcrioprod.admin.dbo.servers where servername='+@servername+') as serverid,
dbid, name, crdate, cmptlevel
from '+@servername+'.master.sys.sysdatabases')

No, you're never too old to Yak'n'Roll if you're too young to die.

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-23 : 16:42:24

I hav tried that in the past, and tried it again now on your suggestion. The SP is altered successfully, but when I enter "exec countdb3 'usslcsqlt11'" I get an error message "Invalid column name 'USSLCSQLT11'. I've been going in circles with this for three days. What am I missing?

Craig Greenwood

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 16:50:21

Fred just missed a couple of quotes, but his code works with this small modification:


exec('select (select serverid from usslcrioprod.admin.dbo.servers where servername='''+@servername+''') as serverid, 
dbid, name, crdate, cmptlevel
from '+@servername+'.master.sys.sysdatabases')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 16:53:10

Oh yes - damned I missed that - @servername in quotes because it is a string value.
Cool man

No, you're never too old to Yak'n'Roll if you're too young to die.

craigwg
Posting Yak Master

154 Posts

Posted - 2009-07-23 : 16:54:46

My jaw is on the floor. It worked. I have been googling how to use apostrophies with SQL in Stored Procedures, as well as using variable in Stored Procedures. Nothing came close to explaining my problem. Can you provide links to that explain what is happening? Why are extra apostrophies needed for the where statement but NOT for the from statement?

Thanks for your solution.

Craig Greenwood

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-23 : 16:58:34

if you change the EXEC to PRINT I think you can see for yourself what's going on...but basically, since a single quote is the string identifier in SQL, then you need to escape it, by doubling it up. Gets a little confusing when writing out dynamic SQL with string variables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources