My Technical Notes

Friday, 1 March 2013

Stored Procedure Input and Output Parameters

The following is a test stored procedure with both an input and output parameter.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('dbo.TestProc') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[TestProc]
END

GO


CREATE PROCEDURE [dbo].[TestProc]
 @myInputParam nvarchar(max),
 @myOutputParam decimal(18, 2) OUT
AS
BEGIN
 
 SET NOCOUNT ON;
 
 SET @myOutputParam = 12.1
END
GO

The following is script which will call the above stored procedure


DECLARE @myInputParameter nvarchar(max) = 'tahir'
DECLARE @myOutputParameter decimal(18,2)
EXECUTE [dbo].[TestProc] @myInputParameter, @myOutputParameter OUTPUT

No comments: