[RESOLVED] Stored Procedure return values problem

*Edit*

Hi Chaps.

This is breaking my head, wonder if any of you nice people could help me out?

I have a stored procedure that calls another stored procedure to return a string

The syntax from SQL books to return the value is to use an output parameter and this works in Query Analyzer:

Code:

---


EXEC spGetCalleeRecIDSQL 2,@CampaignName,@REC = @RECID

---

The problem is in the procedure when I check @RECID its is blank when it comes back ?

I have traced thru the inner procedure and it works fine but the code in the outer procedure does'nt get past the EXEC line.

Help please ???? humbly grateful... please ???... call me a muppet if you need to, just need to fix this... :mad:

Cheers

Chubby..

What database are you using and version?

If its SQL Server, do you have execute nested sp calls checked?

Can you post your sp codes?

Thanks RobDog....


Using SQL Server 2000, can't seem to locate the nested SP check ?

Code for Proc that returns the ID:

Code:

---

CREATE PROCEDURE [spGetCalleeRecIDSQL] (@Action int, @CampaignName NVARCHAR(32),@REC NVARCHAR(32) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CMD NVARCHAR(1000)
DECLARE @RECID NVARCHAR(32)

DECLARE @RESCODE INT
DECLARE @OB_ERR_NO_SUCH_CAMPAIGN INT
DECLARE @OB_ERR_AGENT_NOT_ASSOCIATED INT
DECLARE  @OB_ERR_CAMPAIGN_FINISHED INT
DECLARE  @OB_ERR_DATABASE_ERROR INT
DECLARE @OB_ERR_NO_RECS INT 
DECLARE @WHERE Char(20)
DECLARE @PRIORITY INT

SET @RESCODE = 0

SET @OB_ERR_NO_SUCH_CAMPAIGN = -1
SET  @OB_ERR_CAMPAIGN_FINISHED = -2
SET @OB_ERR_DATABASE_ERROR  = -3
SET @OB_ERR_AGENT_NOT_ASSOCIATED = -9
SET @OB_ERR_NO_RECS = -10



---- First Find Gen Callbacks

IF @Action = 1
BEGIN
SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE callResult =  ' + '''Create gen. CB'''  + ' AND STATUS=0 AND RetryTime < 70000.3'   

--print  @CMD

exec sp_executesql @CMD,
                  N'@RECID NVARCHAR(32) OUT',
                  @RECID out


IF @@ERROR <> 0
        BEGIN
                SET @WHERE = 'ERROR GEN CB'
                SET @RESCODE = @OB_ERR_DATABASE_ERROR
                GOTO END_
        END
             
If  NOT @RECID IS NULL
BEGIN
        ---SET @WHERE = 'Create gen. CB'
        GOTO END_

END
ELSE
BEGIN
        SET @RESCODE = @OB_ERR_NO_RECS
        GOTO END_
END
END
--- Second Check if TOD is active, if it is, then look for Any Other Callback, will only get here if the first check has failed.
--print 'Second Check'

IF @Action = 2
BEGIN
SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE STATUS=0 AND RetryTime < 70000.3'   

--print  @CMD
exec sp_executesql @CMD,
                  N'@RECID NVARCHAR(32) OUT',
                  @RECID out
Print @RecID + ' GenCB'

        IF @@ERROR <> 0
        BEGIN
                SET @WHERE = 'ERROR Other CB'
                SET @RESCODE = @OB_ERR_DATABASE_ERROR
                GOTO END_
        END
             
        If  NOT @RECID IS NULL
        BEGIN
                SET @WHERE = 'Other CB'
                GOTO END_
        END
        ELSE
        BEGIN
                SET @RESCODE = @OB_ERR_NO_RECS
                GOTO END_
        END

END

--- Third Check if any other new records exist and return the first one.
IF @Action = 3
BEGIN

SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE STATUS=0 AND RetryTime =  70000.3'   

--print  @CMD

        exec sp_executesql @CMD,
                  N'@RECID NVARCHAR(32) OUT',
                  @RECID out

        IF @@ERROR <> 0
        BEGIN
                SET @WHERE = 'ERROR NEW REC'
                SET @RESCODE = @OB_ERR_DATABASE_ERROR
                GOTO END_
        END
           
        IF  NOT @RECID IS NULL
        BEGIN
                SET @WHERE = 'NEW REC'
                GOTO END_
        END
        ELSE
        BEGIN
                SET @RESCODE = @OB_ERR_NO_RECS
                GOTO END_
        END       
END

END_:


IF @RESCODE <> 0
BEGIN       
        SET @RECID =  @RESCODE
       
END
ELSE
BEGIN

IF @RECID IS NULL
        BEGIN                       
                SET @RECID =  @OB_ERR_CAMPAIGN_FINISHED
        END
       
END


END
GO

---

Sorry for the long Proc :)

But using Select @RECID returns the record to Query Analyser, but Select @RECID in the testsp retuens NULL ?

Code:

---


CREATE PROCEDURE SPTEST AS
DECLARE @mySQL NVARChar(1000)
DECLARE @RECID NVARCHAR(32)
SET NOCOUNT ON
EXEC spGetCalleeRecIDSQL 1,null,@REC = @RECID

Select @RECID

---

Thanks for the help. Im sure this is a muppet issue... :)

Ok, after going over it, it looks like we should first start with the variable @RecID. For the sp_executesql stored procedure you need to use parameters that are text, ntext, or image. Your @RecID is nvarchar.

Also,

Quote:

---

More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed.

---

When you Print @CMD what does your output statement look like?

Quote:

---

Originally Posted by RobDog888

Ok, after going over it, it looks like we should first start with the variable @RecID. For the sp_executesql stored procedure you need to use parameters that are text, ntext, or image. Your @RecID is nvarchar.

Also,
When you Print @CMD what does your output statement look like?

---

Hi RobDog,

Thanks for your reply, the help is very much appreciated.

I'm a wee bit confused here as the INNER SP executes fine and return expected parameters (nVarchar aside)

The @CMD looks like this:

Code:

---

SELECT TOP 1 @RECID = RECID FROM [CORNMARKET] WHERE callResult =  'Create gen. CB' AND STATUS=0 AND RetryTime < 70000.3

---

And the return value into @RECID is correct in all cases.

Issuing a Select @RECID at the end of the inner SP returns the row correctly.
Sadly the OUTPUT parameter doesn't seem to be getting set ??

Cheers

Chubby..

Right, but when executed via the sp_executesql stored procedure there are restrictions that are different from if you just executed the raw sql statement in QA.

See here for the specifics on the sp. :)

http://msdn2.microsoft.com/en-us/lib...1(SQL.90).aspx

Thanks RobDog, but the sp_executesql is working and returning the RECID in the inner SP ?? When the OuterSP tries to ge that value it's empty ?

Where are you returning the @RecID to the outter sp? I do see that you have an output parameter that I think is being unused?

@REC NVARCHAR(32) OUTPUT

Hi RobDog,

Take your point on the output parameter, I set up a simple test and got back a parameter.
Outer SP

Code:

---

CREATE PROCEDURE SPTEST AS
DECLARE @RECID CHAR(100)
SET NOCOUNT ON

EXEC spGetTest @REC = @RECID

SELECT @RECID
GO

---

Inner SP

Code:

---

CREATE PROCEDURE spGetTest  @REC CHAR(10)  OUTPUT as
  SET NOCOUNT ON
  SET @REC = 'hello'
  SELECT @REC
  RETURN
GO

---

Yet myProduction outer SP still fails, is this maybe a type issue as you mentioned ? This is nuts as Idefinitely get a result from the Inner SP.

Here's the Outer SP, a label RobDog points to the position where I get nothing back ??

Code:

---

CREATE  PROCEDURE spGetNextCampaignRec(@AgentLogin varchar(32),@Assigned BIT,@CN NVARCHAR(32) = NULL)
AS

SET NOCOUNT ON
DECLARE @DayNumber INT
DECLARE @Time CHAR(5)
DECLARE @DialSequence CHAR(20)
DECLARE @Pto CHAR(5)
DECLARE @hr CHAR(2)
DECLARE @Min CHAR(2)
DECLARE @CampaignName CHAR(32)
DECLARE @ID CHAR(32)
DECLARE @TODActive BIT
DECLARE @RECID NVARCHAR(32)
DECLARE @CMD NVARCHAR(2000)
 DECLARE @mySQL NVARChar(1000)

DECLARE @RESCODE INT
DECLARE @OB_ERR_NO_SUCH_CAMPAIGN INT
DECLARE @OB_ERR_AGENT_NOT_ASSOCIATED INT
DECLARE  @OB_ERR_CAMPAIGN_FINISHED INT
DECLARE  @OB_ERR_DATABASE_ERROR INT
DECLARE  @OB_ERR_NO_RECS INT
DECLARE @OB_ERR_NO_ACTIVE INT
DECLARE @WHERE Char(20)
DECLARE @PRIORITY INT
SET @RESCODE = 0

SET @OB_ERR_NO_SUCH_CAMPAIGN = -1
SET  @OB_ERR_CAMPAIGN_FINISHED = -2
SET @OB_ERR_DATABASE_ERROR  = -3
SET @OB_ERR_AGENT_NOT_ASSOCIATED = -9
SET @OB_ERR_NO_RECS = -10
SET @OB_ERR_NO_ACTIVE = -11


IF @Assigned = 0

BEGIN
       
        SET @CampaignName = @CN
        /*
        EXEC spGetCalleeRecIDSQL 1, @CampaignName,@REC = @RECID ---Get Gen Callback
       
        IF @RECID = -10
        BEGIN
               
                SET @RECID = NULL
                EXEC spGetCalleeRecIDSQL 2, @CampaignName,@REC = @RECID --- Get Normal Callback (busy, no answ etc)
                IF  @RECID = -10
                BEGIN
                        SET @RECID = NULL
                        EXEC spGetCalleeRecIDSQL 3,@CampaignName,@REC = @RECID --- Get Normal Call
                        IF  @RECID = -10
                        BEGIN
                                SET @RESCODE = @OB_ERR_CAMPAIGN_FINISHED ---hopefully the loop should occur
                                GOTO NEXT_
                        END
                        ELSE                                       
                        BEGIN
                                IF LEN(@RECID)  > 3
                                BEGIN
                                        SET @RESCODE = 0
                                        GOTO END_
                                END
                                ELSE
                                BEGIN
                                        SET  @RECID = NULL
                                        SET @RESCODE = @RECID
                                        GOTO END_
               
                                END                                       
                        END
                END
                ELSE
                BEGIN
                        IF LEN(@RECID)  > 3
                        BEGIN
                                SET @RESCODE = 0
                                GOTO END_
                        END
                        ELSE
                        BEGIN                               
                                SET  @RECID = NULL
                                SET @RESCODE = @RECID
                                GOTO END_
                        END
                END
               
               
        END
        ELSE
        BEGIN
                ---SQL OK
                IF LEN(@RECID)  > 3
                BEGIN
                        SET @RESCODE = 0
                        GOTO END_
                END
                ELSE
                BEGIN
                        --- Error from Select so return it
                        SET  @RECID =NULL
                        SET @RESCODE = @RECID
                        GOTO END_
                END
               
        END
       
*/
END

ELSE

BEGIN

-- Current Day Number
SELECT @DayNumber = DATEPART(dw, GETDATE())

---Current Time
SELECT @HR = LTRIM(CAST(DATEPART(hh, GETDATE()) AS CHAR(2)))
SELECT @MIN = LTRIM(CAST(DATEPART(mi, GETDATE()) AS CHAR(2)))

IF LEN(@HR) < 2 SET @HR = '0' + @HR
IF LEN(@MIN) < 2 SET @MIN  = '0' + @MIN

SELECT @Time = @HR + ':' + @MIN
--print @Time

DECLARE CSR_ACTIVECAMPAIGNS CURSOR FOR SELECT Distinct  C.CampaignName, ABC.AgentLogin,  C.Priority  FROM Campaigns C
INNER JOIN AgentsbyCampaign ABC ON C.ID = ABC.CampaignID
WHERE C.Active = 1 AND ABC.AgentLogin = @AgentLogin Order by C.Priority



OPEN CSR_ACTIVECAMPAIGNS

FETCH NEXT FROM CSR_ACTIVECAMPAIGNS INTO  @CampaignName,@ID,@PRIORITY
Print @@FETCH_STATUS
IF @@FETCH_STATUS = -1
BEGIN
        SET @RESCODE = @OB_ERR_AGENT_NOT_ASSOCIATED
        SET @RECID = NULL
        GOTO END_
END


WHILE @@FETCH_STATUS = 0
BEGIN


        IF EXISTS(SELECT CampaignID FROM Campaignbyperiod CBP  WHERE pday = @DayNumber  AND pfrom <= @Time AND pto > @Time)
        BEGIN
                SET @TODActive = 1
        END
        ELSE
        BEGIN
                SET @TODActive = 0
        END

        Print  'FIRST'       
        EXEC spGetCalleeRecIDSQL 1, @CampaignName,@REC = @RECID ---Get Gen Callback
       
        --- ROBDOB HERE, this won't print a bean (and neither will SELECT)
        print @RECID
       
       
        IF @RECID = @OB_ERR_NO_RECS
        BEGIN
                print 'First Rec minus ten'
                IF @TODActive = 1 --Not Active, Go Away.
                BEGIN
                        SET @RECID = ''
                        EXEC spGetCalleeRecIDSQL 2,@CampaignName,@REC = @RECID --- Get Normal Callback (busy, no answ etc)
                        Print @CampaignName + ' RECID:' +  @RECID + 'NormCB'
                        IF  @RECID = @OB_ERR_NO_RECS
                        BEGIN
                                SET @RECID = ''
                                EXEC spGetCalleeRecIDSQL 3, @CampaignName,@REC = @RECID --- Get Normal Call
                                Print @CampaignName + ' RECID:' +  @RECID + ' NewRec'
                                IF  @RECID = @OB_ERR_NO_RECS
                                BEGIN
                                        SET @RESCODE = @OB_ERR_CAMPAIGN_FINISHED ---hopefully the loop should occur
                                        GOTO NEXT_
                                END
                                ELSE                                       
                                BEGIN
                                        IF LEN(@RECID)  > 3
                                        BEGIN
                                                SET @RESCODE = 0
                                                GOTO END_
                                        END
                                        ELSE
                                        BEGIN
                                                SET  @RECID = NULL

                                                SET @RESCODE = @RECID
                                                GOTO END_
               
                                        END                                       
                                END
                        END
                        ELSE
                        BEGIN
                                IF LEN(@RECID)  > 3
                                BEGIN
                                        SET @RESCODE = 0
                                        GOTO END_
                                END
                                ELSE
                                BEGIN                               
                                        SET  @RECID = NULL
                                        SET @RESCODE = @RECID
                                        GOTO END_
                                END
                        END
                END       
                ELSE
                BEGIN
       
                        SET @RECID = NULL
                        SET @RESCODE = @OB_ERR_NO_ACTIVE
                        GOTO END_

                END               
        END
        ELSE
        BEGIN
                ---SQL OK
                IF LEN(@RECID)  > 3
                BEGIN
                        SET @RESCODE = 0
                        GOTO END_
                END
                ELSE
                BEGIN
                        --- Error from Select so return it
                        SET  @RECID = NULL
                        SET @RESCODE = @RECID
                        GOTO END_
                END
               
        END
        END
END
NEXT_:
        Print 'Next'
    FETCH NEXT FROM CSR_ACTIVECAMPAIGNS INTO @CampaignName, @ID




END_:

CLOSE CSR_ACTIVECAMPAIGNS
DEALLOCATE CSR_ACTIVECAMPAIGNS


IF @RESCODE  <> 0 AND @RECID IS NULL
        BEGIN               
                SET NOCOUNT OFF
                SELECT @RESCODE as RECID
        END

ELSE
        BEGIN
                --BEGIN TRANSACTION
                SET @CMD = 'UPDATE [' + rtrim(@CampaignName) + '] SET Status = 1, NumCalls = NumCalls  + 1,WriteTime = GetDate() WHERE RecID = ' + @RECID
               
                Print @CMD
                /*
                EXEC (@CMD)
                IF @@ERROR <> 0
                BEGIN
                        ROLLBACK TRANSACTION       
                        SELECT @OB_ERR_DATABASE_ERROR AS RECID               
                END
                ELSE
                BEGIN
                        COMMIT TRANSACTION
                        SET @CMD = 'SELECT * FROM  [' + @CampaignName + ']  WHERE RecID = ' + @RECID
                        EXEC (@CMD)
                END
        */
        END
GO

---

Thanks very much !

Chubby.

Where is the text for "spGetCalleeRecIDSQL"?

I have a hunch but could you take out the SELECT TOP 1 ... and make it SELECT * FROM or such, just take out the TOP 1 part.

Hi RobDog,

I kinda see where you're going but the return from spGetCalleeRECIDSQL is either a string representing a recordID or its a negative return value. This does work if you call spGetCalleeRECIDSQL in Q-A so I'm not sure how taking out the TOP 1 from this would be the issue (it either returns a recordID or a negative, it cannot return nothing)...
This is the end statement of the inner SP (SELECT has been changed to PRINT)

Code:

---



IF @RESCODE <> 0
BEGIN       
        SET @REC =  @RESCODE
       
END
ELSE
BEGIN

IF @RECID IS NULL
        BEGIN                       
                SET @REC =  @OB_ERR_CAMPAIGN_FINISHED
        END
ELSE
        BEGIN
                SET @REC = @RECID
        END
END
PRINT @REC
RETURN

END

---

This prints -10 as expected....



Thanks

Chubby..

I dont see where your passing the @RECID value back to your calling sp. Also, your not specifying that @RECID is an OUTPUT variable. So that could be why its NULL all the time when called this way.

Hi RobDog, I'm sorry this is causing so much of an issue and thanks for your patience.

I'm coercing @RECID into @REC to return it, @REC is defined as an output Parameter:-

Code:

---

CREATE PROCEDURE [spGetCalleeRecIDSQL] (@Action int, @CampaignName NVARCHAR(32),@REC NVARCHAR(32) OUTPUT)
AS

---

@REC is set as an output Parameter and at the bottom of the SP:

Code:

---

IF @RESCODE <> 0
BEGIN       
        SET @REC =  @RESCODE
       
END
ELSE
BEGIN

IF @RECID IS NULL
        BEGIN                       
                SET @REC =  @OB_ERR_CAMPAIGN_FINISHED
        END
ELSE
        BEGIN
                SET @REC = @RECID
        END
END
PRINT @REC
RETURN

END

---

The Print @REC actually shows the correct value but in the calling SP the @RECID which is passed in to correspond with @REC is empty??

Thanks

Chubby.

Hmm, I'm not the SQL Guru but I do know who is. szlamany is the resident SQL Guru around here. I'll send him a PM and ask him if he can lend a hand.

Hold on...

Are you still calling the sproc like this:

Code:

---

EXEC spGetCalleeRecIDSQL 2,@CampaignName,@REC = @RECID

---

The @REC=@RECID format is odd to me.

To see a proper EXEC call syntax for a sproc go into QUERY ANALYZER

Make sure you have the OBJECT BROWSER open on the left (F8 does this).

Open the DB branch - the STORED PROCEDURE branch and then...

RIGHT CLICK on the SPROC and DRAG into the QUERY WINDOW - let go of the mouse...

Choose the EXECUTE option from the drop-down menu.

This gives you a template for calling your sproc - does the EXEC line look anything like @REC = @RECID?

Chaps,

I knew this was a muppet issue and so it turns out to be. Looking V.Hard are the code etc I finally noticed a missing OUTPUT statement at the following:

Code:

---

spGetCalleeRecIDSQL 2, @CampaignName,@REC = @RECID

---

It should read:

Code:

---

spGetCalleeRecIDSQL 2, @CampaignName,@REC = @RECID OUTPUT

---

Muppet am I and most humbly grateful for your time & help RobDog & your help szlamany.

As a by the way, yes, the code is not as rational as it once was and will be cleaned, tidied and rationalised dramatically prior to release.

Again,

Thanks Chaps, it really is appreciated to have suh immediate help.

Chubby.

Thank You Steve :thumb: I knew it was something with the OUTPUT but didnt catch where.

Yet again you have proven yourself a true SQL Guru™ :D