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:
Post a Comment