Printable View
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..
Can you show us the code for the SPs?
Wouldnt you just do an ...
Code:IF something
BEGIN
exec sp_something
END
ELSE
BEGIN
exec sp_someothersp
END
...
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?
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..
Just iterate through the recordsets...
This is some ugly code that reads two recordsets:
This one loops - not knowing in advance how many recordsets:VB Code:
Those output parameters are most likely not going to be accessible in VB until you traverse those recordsets.VB Code:
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.
Quote:
Originally Posted by szlamany
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. :thumb:
Chub.
Code: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
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.