hi i am dojng a stored procedure which conatains a cursor...cursor has a dynamic sql query ..this query has dynamic value..which is @industry..then i put the results in a temporary table..but the problem is when i execute the procedure results are not coming...

this is my stroedproc in sqlserver

ALTER PROCEDURE GETARTISTDETAIL  AS
BEGIN
    DECLARE @INDUSTRY VARCHAR(40)
    DECLARE @ID INT
    DECLARE @SQL1 VARCHAR(1000)
    DECLARE @SQL VARCHAR(1000)
    SET @INDUSTRY='''BollyWood'',''TollyWood'',''HollyWood'''
CREATE TABLE #TEMPTBL([NAME] VARCHAR(20),[AGE] INT ,[MAILID] VARCHAR(20))   
    --SET @SQL='SELECT ARTISTID FROM ARTIST WHERE INDUSTRY IN ('+@INDUSTRY+')' 
    DECLARE TEMPCRS CURSOR LOCAL SCROLL STATIC FOR SELECT ARTISTID FROM ARTIST WHERE INDUSTRY IN (@INDUSTRY)
    --PRINT @SQL
    --exec (@SQL)
    OPEN TEMPCRS
    FETCH NEXT FROM TEMPCRS INTO  @ID
    WHILE @@FETCH_STATUS=0  
    BEGIN
    INSERT INTO #TE开发者_开发问答MPTBL SELECT [NAME],[AGE],[MAILID] FROM ARTIST WHERE ARTISTID=@ID
    FETCH NEXT FROM TEMPCRS INTO  @ID
    END
    CLOSE TEMPCRS
    DEALLOCATE TEMPCRS
    SELECT * FROM #TEMPTBL
    DROP TABLE #TEMPTBL
END

---

You don't need a cursor to do this, just use a subquery:

DECLARE @INDUSTRY VARCHAR(40)
DECLARE @SQL VARCHAR(1000)

CREATE TABLE #TEMPTBL([NAME] VARCHAR(20),[AGE] INT ,[MAILID] VARCHAR(20))  

SET @INDUSTRY='''BollyWood'',''TollyWood'',''HollyWood'''

SET @SQL='INSERT INTO #TEMPTBL SELECT [NAME],[AGE],[MAILID] 
          FROM ARTIST WHERE ARTISTID IN (
            SELECT ARTISTID FROM ARTIST WHERE INDUSTRY IN ('+@INDUSTRY+')
         )' 

EXEC (@SQL)

SELECT * FROM #TEMPTBL
DROP TABLE #TEMPTBL