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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-28 : 17:20:16
|
Valter Borges writes "I read your article on Using Coalesce to Build a Comma-Delimited String. I tried using it the problem occurred when
I used a variable in the where clause see below @table_name
@table name is being obtain in a while loop from a cursor and for some reason when I ran code 1 I got the same string for each time the cursor ran with the same table name even though when I printed the @table_name variable it was changing. Then I tried creating a string and using with sp_executesql as shown bellow but I can't seem to get @column_names to return correctly but when I print inside @str_sql string it's fine. What is going on here is it some kind of SQL SERVER caching?? Anyone???
SELECT @str_sql =
'
SET NOCOUNT ON
DECLARE @column_names nvarchar(4000)
SELECT @column_names = COALESCE(@column_names + '', '', '''') +
TABLE_NAME + ''.'' + COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME =''' + @table_name + ''' AND COLUMN_NAME NOT IN (''widget_ID'', ''fam_ID'')
'
EXEC @column_names = sp_executesql @str_sql" |
|
|
|
|
|
Using Coalesce Function with dynamic where clause