Home Forums SQL Server 2005 T-SQL (SS2K5) Can a CURSOR be populated by firing a stored procedure? RE: Can a CURSOR be populated by firing a stored procedure?

  • not directly like that, no.

    If you have a stored procedure that returns data, you can insert it into a temp table, and have the cursor go through THAT.

    no, adding a bit of peer review, In general, if a cursor was going to do something to data, it can and should be replaced with a set based operation instead.

    There's a very good chance that whatever your cursor was going to do, can be replaced with a single command that does the same work at least an order of magnitude faster; if you'd like help with that, post more details.

    as far as a specific code example, here's an example creating a temp table with the results of sp_who2 for SQL2005; from there, you could create a cursor selecting from the temp table.

    CREATE TABLE #Results (

    [ResultsID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [SPID] CHAR (5) NULL,

    [INSERTDT] DATETIME NULL DEFAULT(GETDATE()),

    [STATUS] VARCHAR(30) NULL,

    [LOGIN] VARCHAR(30) NULL,

    [HOSTNAME] VARCHAR(30) NULL,

    [BLKBY] VARCHAR(30) NULL,

    [DBNAME] VARCHAR(30) NULL,

    [COMMAND] VARCHAR(30) NULL,

    [CPUTIME] INT NULL,

    [DISKIO] INT NULL,

    [LASTBATCH] VARCHAR(30) NULL,

    [PROGRAMNAME] VARCHAR(200) NULL,

    [SPIDINT] INT NULL,

    [REQUESTID] INT NULL

    )

    --table exists, insert some data

    INSERT INTO #Results(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT,REQUESTID)

    EXEC sp_who2

    SELECT * FROM #Results

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!