SP should return both out parms and SELECT Result

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP should return both out parms and SELECT Result

Author

Topic

metallian1
Starting Member

5 Posts

Posted - 2009-07-27 : 02:51:44

Hello everybody

I am somewhat new to SQL Server 2008 and I have a question concerning stored procedures:

All my stored procedures return a standard set of parameters to a c# application.

Here is the standard header of all my stored procedures:

ALTER PROCEDURE [dbo].[dtsp_GetAgentAllQueues]
@ID int,
@SessionGUID uniqueidentifier,
@ReturnCode int out,
@ReturnShortMessage nvarchar(50) out,
@ReturnLongMessage nvarchar(255) out,
@HasError bit = 1 out,
@ExecutionTime int = 0 out
-- Additional variables for each SP added here
AS

So far so good.

But what do I do when my SP should return a select statement (i.e. SELECT * FROM tblQueues)?

Running the stored prcoedure I always run into an error

Msg 16924, Level 16, State 1, Procedure dtsp_GetAgentAllQueues, Line 31
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

Here are the final lines of my SP

-- Return Data
SET @ReturnCode = 500
SET @HasError = 0
SELECT QueueName FROM dbo.tblQueue WHERE ID IN (SELECT DISTINCT QueueID FROM dbo.tblAgentToQueue
WHERE (SecurableMatchCode IN ( SELECT * FROM dbo.view_GetReadAccessSecurables))
AND (AgentID = @AgentID)) AND (IsDisabled = 0)

Execution_End:
SET @ReturnShortMessage = dbo.dtfunc_GetSystemMessage(@ReturnCode,1)
SET @ReturnLongMessage = dbo.dtfunc_GetSystemMessage(@ReturnCode,2)
SET @ExecutionTime = CONVERT(int, DATEDIFF(MILLISECOND,@StartTime,GETDATE()))

END

Is there any way in TSQL to handle that?

I hope I could explain my problem understandably. Have a pleasant day !!

Greets from Hamburg, Germany

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 03:37:23

Hi

Can you check the colored code..

SELECT QueueName
FROM   dbo.tblQueue
WHERE  ID IN
             (SELECT DISTINCT QueueID
             FROM             dbo.tblAgentToQueue
             WHERE (
                                               SecurableMatchCode IN
                                               ( SELECT            *
                                               FROM    dbo.view_GetReadAccessSecurables
                                               )
                              )
                          AND (
                                               AgentID = @AgentID
                              )
             )
   AND (
              IsDisabled = 0
       )

-------------------------
R..

metallian1
Starting Member

5 Posts

Posted - 2009-07-27 : 04:05:37

Hello rajdaksha

thanks for your answer.

I forgot to mention that the SELECT statement itself works absolutely fine if fired directy from the query editor.

The thing I want to know is how to technically create a stored procedure that either returns my standard set of out parameters AND the result dataset created by the SELECT statement.

rgds

Jan

metallian1
Starting Member

5 Posts

Posted - 2009-07-27 : 05:45:35

Hello

solved the thing by myself

The errormessage came from a UDF in my Stored procedure.

rgds

Jan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources