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