Thursday 18 June 2009

Returning OUTPUT values from stored procedures

Because i'm forever revisiting old code for the simplest of functions,
here is my memory jogger for retrieving OUTPUT values from stored procs!

Fig 1 : Really easy stored procedure looking at an INFORMATION SCHEMA view.
Specify the OUTPUT cluase in the parameter definition.

CREATE PROCEDURE dbo.RoutineCount (
    @RoutineType VARCHAR(20),
    @RoutineCount INT OUTPUT )
AS
BEGIN
SELECT @RoutineCount = COUNT(*) 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = @RoutineType
END
GO


Fig 2 : Returning the value from the procedure.
Declare a variable to pass it into and specify the OUTPUT clause.
DECLARE @CountOfRoutines INT

EXEC dbo.RoutineCount 
    @RoutineType = 'procedure',
    @RoutineCount = @CountOfRoutines OUTPUT

SELECT @CountOfRoutines AS RoutineCount


NB : You need to specify OUTPUT both in the sp definition and the sql that calls the proc.

No comments: