RobDog888, szlamany..?Multiple Selects from Stored Procedure

[Chubby]

Chaps (and anyone else who may be able to help)...
The outer sp iterates a cursor to find an available record. If it doesnt find one it returns a negtive condition, if it finds one it returns the record's ID...

I have the nested sp working fine in my outer SP, sadly, calling the Outer SP via VB gives me back the first result from the first nested call ?

SET NOCOUNT does not work for this. Is there a SET or something that will suppress the inner call SELECT ??

Cheers

Chubby..

[si_the_geek]

Can you show us the code for the SPs?

[RobDog888]

Wouldnt you just do an ...

IF something 
    BEGIN
        exec sp_something
    END
ELSE
    BEGIN 
        exec sp_someothersp
    END
...

[jcis]

You are saving the retuned value to a OUTPUT variable or getting the value in a variable in your outer SP, then returning it with SELECT @Varname?

[Chubby]

Hi,

Thanks for the replies.

I'm returning a string from an inner SP to an OUTPUT variable and using this in the calling SP. The inner SP can be called up to three times in a cursor. The calling SP then returns a record to a calling application.

My problem is that when I call the outer SP from my application I get multiple recordsets, one for each inner SP call and the last one for the outer sp call.

SET NOCOUNT ON and OFF don't work. If I'm unable to get a resolution to this I will need to re-write two fairly painful SP's....

Thanks

Chubby..

[szlamany]

Just iterate through the recordsets...

This is some ugly code that reads two recordsets:

objCmd.CommandText = "GetAppEle_P"
    objCmd.CommandType = adCmdStoredProc
    
    objCmd.ActiveConnection = gCn
    Set rsTemp = objCmd.Execute
    
    ReDim gEleRoleBits(rsTemp(0), 1 To 2)
    
    Do While rsTemp.EOF = False
        Select Case rsTemp(1)
            Case Is < 33
                gEleRoleBits(rsTemp(0), 1) = gEleRoleBits(rsTemp(0), 1) Or (2 ^ (rsTemp(1) - 1))
            Case Is < 65
                gEleRoleBits(rsTemp(0), 2) = gEleRoleBits(rsTemp(0), 2) Or (2 ^ (rsTemp(1) - 33))
        End Select
        rsTemp.MoveNext
    Loop
    
    Set rsTemp = rsTemp.NextRecordset
 
    ReDim gstrFormReports(0 To 0)
 
    Do While rsTemp.EOF = False
        ReDim Preserve gstrFormReports(0 To UBound(gstrFormReports) + 1)
        gstrFormReports(UBound(gstrFormReports)) = rsTemp(0) & "/" & rsTemp(1) & "/" & rsTemp(2)
        rsTemp.MoveNext
    Loop
    
    rsTemp.Close
    Set rsTemp = Nothing
    Set objCmd = Nothing

This one loops - not knowing in advance how many recordsets:

Do While rsInquire.State <> 0
    
        lngRecCnt = 0
        
.
.
.
        Do While rsInquire.EOF = False
.
.
.
            rsInquire.MoveNext
        Loop
.
.
.
        Set rsInquire = rsInquire.NextRecordset
        
    Loop

Those output parameters are most likely not going to be accessible in VB until you traverse those recordsets.

There is some trick to get an output parameter prior to reading recordsets - but I think I read in my ADO book that it was an undocumented feature and likely to disappear in the future.

[Chubby]

Just iterate through the recordsets...

szlamany... it hurrrtsss !.......

I kindof gave up and trashed the whole lot back into one SP, got NOCOUNTS all over the place and Query Analyzer brings one select back, yet ADO sees two, the first being closed !!!

Just out of spite, can you see anywhere I've missed ?
By the way, thanks for the reply.

Chub.

CREATE  PROCEDURE spGetNextCampaignRec1(@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 CHAR(32)
DECLARE @CMD NVARCHAR(2000)
 DECLARE @mySQL NVARChar(1000)

DECLARE @RESCODE INT
DECLARE @OB_ERR_NO_SUCH_CAMPAIGN NVARCHAR(3)
DECLARE @OB_ERR_AGENT_NOT_ASSOCIATED  NVARCHAR(3)
DECLARE  @OB_ERR_CAMPAIGN_FINISHED  NVARCHAR(3) 
DECLARE  @OB_ERR_DATABASE_ERROR  NVARCHAR(3) 
DECLARE  @OB_ERR_NO_RECS  NVARCHAR(3)  
DECLARE @OB_ERR_NO_ACTIVE  NVARCHAR(3) 
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
	SET NOCOUNT ON
	SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE callResult =  ' + '''Create gen. CB'''  + ' AND STATUS=0 AND RetryTime < 70000.3'    
	print   'FIRST'
	exec sp_executesql @CMD,
              N'@RECID NVARCHAR(32) OUT', @RECID out
	print   @RECID
	IF @RECID IS NULL
	BEGIN	
		print   'SECOND'
		SET NOCOUNT ON
		SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE callResult !=  ' + '''Create gen. CB'''  + ' AND STATUS=0 AND RetryTime < 70000.3'    
		exec sp_executesql @CMD,
                   	N'@RECID NVARCHAR(32) OUT', 
                   	@RECID out
		IF @RECID IS NULL
		BEGIN
			
			Print 'Trying Last'
			SET NOCOUNT ON
			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  @RECID IS NULL
			BEGIN	
				SET @RECID = NULL
				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
SET NOCOUNT ON
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
SET NOCOUNT ON
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

	SET NOCOUNT ON
	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' 	
	
	SET NOCOUNT ON
	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 @RECID IS NULL
	BEGIN
		print 'First Rec minus ten'
		IF @TODActive = 1 --Not Active, Go Away.
		BEGIN
			SET NOCOUNT ON	
			SET @CMD = 'SELECT TOP 1 @RECID = RECID FROM [' + rtrim(@CampaignName) +'] WHERE callResult !=  ' + '''Create gen. CB'''  + ' AND STATUS=0 AND RetryTime < 70000.3'    
			exec sp_executesql @CMD,
                   		N'@RECID NVARCHAR(32) OUT', 
                   		@RECID out
			Print @CampaignName + ' RECID:' +  @RECID + 'NormCB'
			IF  @RECID  IS NULL
			BEGIN
				SET NOCOUNT ON
				Print 'Trying Last'
				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 @@ERROR
					
				Print @CampaignName + ' RECID:' +  @RECID + ' NewRec'
				IF  @RECID  IS NULL
				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_:
    SET NOCOUNT ON
    FETCH NEXT FROM CSR_ACTIVECAMPAIGNS INTO @CampaignName, @ID




END_:
PRINT 'ENDIN'
PRINT @RECID
IF @Assigned = 1 
BEGIN
  CLOSE CSR_ACTIVECAMPAIGNS
  DEALLOCATE CSR_ACTIVECAMPAIGNS
END

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

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

[szlamany]

That is a lot of code to look through

First thing I notice - PRINT statements all over the place. They are evil and cannot be left in production SPROCS. The interfere with the returning of recordsets and output parameters - as they go into the ERROR COLLECTION.

A really great way to see how a SPROC is going to execute and return data is to execute it in QUERY ANALYZER but with Results to TEXT set. This can be set by either clicking CTRL/T print to executing the SPROC or by using the QUERY menu - you will see an option for "Results to TEXT".

The nice thing about this is that you get one single TEXT WINDOW of all the returned results - in order. You can see what PRINT statements and what RESULTSETS are all getting passed back to the client side.