| Author |
Topic |
|
bill_
Starting Member
38 Posts |
 Posted - 2010-05-19 : 15:18:33
|
| This calls a stored procedure: exec sp1 @param1='test'Getting error "must decl scalar var @param1", when I try this in sp1: declare @q set @q='select cname,cno from tblnames where cname=@param1' exec sp_executesql @qWhat am I doing wrong ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-19 : 15:21:44
|
Here's an example of how to properly use it: SET @sql = ' SELECT @indexName = [name], @disabled = is_disabled, @allowPageLocks = allow_page_locks FROM ' + QUOTENAME(@dbName) + '.sys.indexes WHERE [object_id] = @objectId AND index_id = @indexId' SET @parmDef = N' @objectId int, @indexId int, @indexName sysname OUTPUT, @disabled bit OUTPUT, @allowPageLocks bit OUTPUT' EXEC sp_executesql @sql, @parmDef, @objectId = @objectId, @indexId = @indexId, @indexName = @indexName OUTPUT, @disabled = @disabled OUTPUT, @allowPageLocks = @allowPageLocks OUTPUT This is directly from code that I have in production.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bill_
Starting Member
38 Posts |
Posted - 2010-05-19 : 15:37:16
|
| Sorry-was out for minute. Tried 2nd way while waiting and got invalid col name 'test'.Will try top way. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bill_
Starting Member
38 Posts |
Posted - 2010-05-19 : 16:03:07
|
| Sorry about that, meant 'test'. Editted that post. Only thing diff from origianl post was lineset @q='select cname,cno from tblnames where cname='+@param1whcich got error "invalid col name 'test'"Trying to immitate the original way you suggested now. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-19 : 16:12:02
|
change it to...set @q='select cname,cno from tblnames where cname='+ '''' + @param1 + '''' |
|
|
|
bill_
Starting Member
38 Posts |
Posted - 2010-05-19 : 16:23:17
|
| Thank you tkizer and vijayisonly.Will keep trying to understand the 1st tkizer exampl and for this first step, the vijayisonly example worked.What do the ''''s mean ? |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-19 : 16:28:19
|
Since @param1 is really a value in the table you are serching against, you need to enclose it with single quotation mark (') and thats what I've done.do a PRINT @q after your SET statement...and you should see what it does. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
sp_executesql problem