"Joseph Anderson" <josnd***@yahoo.com> дÈëÏûÏ¢ÐÂÎÅ:OSXlEWnwGHA.4***@TK2MSFTNGP03.phx.gbl...
> Dear all,
>        I have sql scrips like the following lines details
> --starts here
> DECLARE @my_cursor CURSOR
> DECLARE @sqlstr VARCHAR(50)
> DECLARE @strTable VARCHAR(1000)
>
> SET @my_cursor = CURSOR FORWARD_ONLY STATIC FOR  SELECT  DISTINCT name
> FROM tbl_temp   --LINE 1
>
> OPEN @my_cursor
> FETCH NEXT FROM @my_cursor INTO @sqlstr
>
> WHILE(@@FETCH_STATUS=0)
>  BEGIN
>   PRINT @sqlstr
>
>   FETCH NEXT FROM @my_cursor INTO @sqlstr
>  END
>
> CLOSE @my_cursor
> DEALLOCATE @my_cursor
> --ends here
>
> my questions is ,if i use the following 3 lines to replace LINE 1, I just
> can not compile this code, why?
>
>
> DECLARE @strQuery VARCHAR(1000)
> SET @strQuery = 'SET @my_cursor = CURSOR FORWARD_ONLY STATIC FOR  SELECT
> DISTINCT name FROM '+'tbl_temp'
> PRINT (@strQuery)
>
> Could anyone tell me why? Because the fact is the table I need to query is
> a input param of this stored procedure, so I need to figure out what's
> problem!
>
> Any helpful ideas would be highly appreciated!
>
> --ja
>

> ja wrote:
>> Sorry ,I use the following 3 lines to replace LINE 1
>>
>> DECLARE @strQuery VARCHAR(1000)
>>  SET @strQuery = 'SET @my_cursor = CURSOR FORWARD_ONLY STATIC FOR  SELECT
>> DISTINCT name FROM '+'tbl_temp'
>>  EXEC (@strQuery)
>>
>> The problem still exists,why?
>
> Dynamic queries execute in their own batch and cannot see any local
> variables created from outside. Therefore your @my_cursor variable does
> not exist as far as the dynamic SQL goes.
>

> ja wrote:
>> Thanks Chris!
>> You are right, but I still need to use dynamic query and I also need to
>> use
>> cursor, what can I do next?
>
> Rather than using a cursor, you could use a WHILE loop over a temp
> table, and have your dynamic sql populate the temp table.
>
> CREATE TABLE #temp1(
> name ...
> )
>
> SELECT @SQL = 'INSERT #temp1 SELECT DISTINCT name FROM ' + @Table
>
> EXEC(@SQL)
>
> SELECT @name = MIN(name) FROM #temp1
>
> WHILE @name IS NOT NULL
> BEGIN
>   PRINT @name
>   SELECT @name = MIN(name) FROM #temp1 WHERE name > @name
> END
>