Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

exec storedProc get return value Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 4:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:41 AM
Points: 441, Visits: 670
Hi guys,

I am very experienced in T-SQL but am still fairly new to SSIS and am trying to run before I walk. I have an Execute SQL task which runs a stored procedure that updates some database records. It takes two input parameters which are supplied in package variables. so far so good - the code runs and succeeds.

The storedProc does some validation (e.g. does the ID passed in exist in the database) and returns -1 if there is an issue or 99 if it runs to completion as expected. There is no other results set.

How do I configure the SQL statement and the results set.

Current config is as follows:

Execute SQL Task:
ResultSet:- None
ConnectionType:- OLE DB
SQLSourceType:- Direct input
SQL Statement:- exec OperationalBatchGenerateFile ?, ?
IsQueryStoredProcedure:- false
BypassPrepare:- True

Parameter Mapping:
User::BatchID, Input,SHORT,0
User::TimeStamp,Inout,NVARCHAR,1

Post #1383600
Posted Monday, November 12, 2012 9:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
-Create a variable to hold your return value.
-Change your ResultSet: None to SingleRow
-Map your variable to your result set
Post #1383744
Posted Tuesday, November 13, 2012 4:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:41 AM
Points: 441, Visits: 670
Thanks Daniel, I am struggling with this concept... I did as you suggested and get processing errors..
Here is the storedProc that is being called from the SQL task

ALTER PROCEDURE [dbo].[OperationalBatchGenerateFile](@BatchID int, @timestamp nvarchar(50))
AS
BEGIN
SET NOCOUNT ON
DECLARE @msg nvarchar(255)
DECLARE @Cancel as int
SET @Cancel = 0

/* check if the batch ID exists */
IF (SELECT count(*) from opsBatch where batchID = @BatchID) = 0
BEGIN
SET @msg = 'Batch ID ' + cast(@BatchID as nvarchar(5)) + ' is not recognised'
raiserror(@msg,1,1)
SET NOCOUNT OFF
RETURN -1 -- quit the routine with error: This should never happen because the data has been extracted from the batch id
END

/* if we have passed all of the above tests then we can update the batch and the lines */

BEGIN TRANSACTION -- all pass or all fail
UPDATE
opsBatchRecord
SET
mailStatus = 'Exported',
MailStatusDate = cast(convert(nvarchar(20),getdate() ,112) as datetime)
WHERE
BatchID = @BatchID
UPDATE
opsBatch
SET
Status = 'Exported',
ExtractFileName = cast(@BatchID as nvarchar(5)) + '_' + @timestamp + '.txt'
WHERE
BatchID = @BatchID

COMMIT TRANSACTION

Return 99
SET NOCOUNT OFF
END


in Mgmt Studio, if you run

EXEC OperationalBatchGenerateFile 1234, '20121113_104300'

Then the procedure runs but does not return a results set.

If you use the right click->Execute on the sp and you get the following script
DECLARE	@return_value int

EXEC @return_value = [dbo].[OperationalBatchGenerateFile]
@BatchID = 10111,
@timestamp = N'20121113_114300'

SELECT 'Return Value' = @return_value

GO

then the return value is 99 or -1 depending on what happens. It is this value that I want to interrogate.

Can I get at this return value without a results set or alternatively pickup the raiserror()
Post #1384031
Posted Tuesday, November 13, 2012 5:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:41 AM
Points: 441, Visits: 670
Quick update.

One problem I was having was that I had not renamed the default resultset to 0 to return the first column.

Now I get the error message in the Progress tab

Error: No result rowset is associated with the execution of this query.

So it am still not getting the return value

Post #1384037
Posted Tuesday, November 13, 2012 11:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
Try making the variable an object type. Although you are really only returning a single value, the task views it as a set that might contain more than one column value.
Post #1384209
Posted Tuesday, November 13, 2012 3:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:41 AM
Points: 441, Visits: 670
The solution is to write the SQL statement as
EXEC ?= usp_myStoredProc ?,?

and set the parameters as Param0 = ReturnValue, Param1 = Input, Param2=Input and assign Param0 to the variable you want to carry through.

Thanks to everyone for the help offered
Post #1384323
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse