Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.
------------------
set @parameter_String=N'@compare_string nvarchar(4000)'
set @sqlStatement='Set @compare_string=(Select ' +
@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'
Print @sqlStatement
EXECUTE sp_executesql @sqlStatement,@parameter_String,@compare_string
Print @compare_String
------------------
When I print the value of @compare_String in the end its a NULL.
However, if I run the same query without the set @compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?
Thanks,
"Robin Tucker" <id*************************@reallyidont.com> wrote in message news:<bs*******************@news.demon.co.uk>...
You need a parms string and an exec string, like this:
SET @Parms = `@compare_string`
set @sqlStatement='Set @compare_string=' + '(Select ' +
@group_column_list_mod + ' from ' + @Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@loop_counter))) + ')'
EXECUTE sp_executesql @sqlStatement, @Parms, @compare_string
SET @Error = COALESCE ( NULLIF ( @Error, 0 ), @@ERROR )
exec(@sqlStatement)
The error message that I get is as follows:
Must declare the variable '@compare_string'.
Here @compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).
I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!
Thanks in advance.