I apologize if this has been asked and I am to dense to grok the answer.
I am trying to determine the best way to do the following:
Declare @sql nvarchar(4000)
, @Filter nvarchar(500)
SET @filter = 'WHERE value1 IN (''A'',''B'',''C'')'
SET @sql = '
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
'+@filter+'
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = @field
@filter = '+@filter+'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
' print @sql execute sp_executesql @sql
The problem here is that it translates to
DECLARE @Field nvarchar(50)
DECLARE process CURSOR FOR
SELECT value2
FROM table
WHERE value1 IN ('A','B','C')
OPEN process
FETCH NEXT FROM process
INTO @field
WHILE @@Fetch_Status = 0
BEGIN
EXEC stored_procedure
@field = 'N'
@filter = 'WHERE value1 IN ('A','B','C')'
FETCH NEXT FROM process
INTO @field
END
CLOSE process
DEALLOCATE process
Of course in this leads to a syntax error at A, B, and C
I have tried a number of variations of replace(@filter,','') to correct this, but this doesn't seem to do me any good.
Ideas please?
