December 21, 2005 at 3:43 am
Hi All,
In a stored procedure Iam building a string based on the parameters supplied and I want to execute the string (its a simple select statement) and assign the value to a sql variable.How do we Do that. I tried the the following. Please try it. It will b returning the output correctly as a row but not able to assign it to an sql variable :
DECLARE @IDxValue nvarchar(50), @PKeyColumnName nvarchar(50),@sTableName nvarchar(50),@UsedCount int
set @IDxValue='1'
set @PKeyColumnName='xyzIdx'
set @sTableName='XYZ'
DECLARE @SQLString NVARCHAR(500)
-- Build the INSERT statement.
SET @SQLString = 'SELECT fieldx FROM '+ @sTableName +' WHERE '+ @PKeyColumnName+'='+@IDxValue
EXEC @UsedCount=sp_executesql @SQLString,
N'@InsTableName nvarchar(50), @InsPKeyColumnName nvarchar(50), @InsIDxValue nvarchar(50)',
@sTableName, @PKeyColumnName, @IDxValue
print @UsedCount
Please Help. Thanks in Advance.
Best Regards,
Baby Mathew
Best Regards,
Baby Mathew
December 21, 2005 at 9:41 am
You cannot assign a rowset to a variable. They are different structures internally and it won't work. I believe that you are assigning the return value from sp_executesql to your variable.
Why are you assigning a rowset to a variable? What's the point?
December 21, 2005 at 9:54 pm
Hi Steve,
Iam trying to assign it in to a variable to do some calculations based on the value that is being returned. for example i may have to increment or decrement the value that is being returned and then update the same in the same table. So without assigning it in to a variable I dint find out any other means by which I can do the same. If you haver any other idea that will help me please post it i will be really gratefull....
Best Regards,
Baby Mathew
December 21, 2005 at 11:38 pm
I think u can use a temporary table to hold the result and you can fetch the result back to some variable.
December 21, 2005 at 11:56 pm
Thanks Prudhvi.....It was a Gr8 idea...Thanks a Lot
Best Regards,
Baby Mathew
December 27, 2005 at 8:52 am
So much for conventional wisdom...
This may not be what you want, but, try something like this:
Declare @Str VarChar(8000)
Set @Str=''
Select @Str=@Str+','Col1+','+Col2+','+...+Char(13)+Char(10) from dbo.[YourTable] where ...
Print @Str
You get the idea...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply