[RESOLVED] Stored Procedure return values problem

[Chubby]

*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:

 
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...

Cheers

Chubby..

[RobDog888]

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?

[Chubby]

Thanks RobDog....


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

Code for Proc that returns the ID:

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 ?

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...

[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,

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?

[Chubby]

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:

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..

[RobDog888]

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

[Chubby]

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 ?

[RobDog888]

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

[Chubby]

Hi RobDog,

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

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

EXEC spGetTest @REC = @RECID

SELECT @RECID
GO

Inner SP

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 ??

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.

[RobDog888]

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.

[Chubby]

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)

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..

[RobDog888]

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.

[Chubby]

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:-

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:

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.

[RobDog888]

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...

[szlamany]

Are you still calling the sproc like this:

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?

[Chubby]

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:

spGetCalleeRecIDSQL 2, @CampaignName,@REC = @RECID

It should read:

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.

[RobDog888]

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

Yet again you have proven yourself a true SQL Guru™