Stored procedure with output parameter

Posted by Rajasekhar0544 under Sql Server on 9/11/2012 | Points: 10 | Views : 1339 | Status : [Member] | Replies : 3
i want an example on stored procedure with output parameter




Responses

Posted by: Ranjeet_8 on: 9/11/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
Below is the eg. of Stored Procedure with Output Parameter

CREATE PROCEDURE [dbo].[InsertCustomer]
(
@LastName varchar(50),
@FirstName varchar(50),
@Address varchar(50),
@City varchar(50),
@State char(2),
@ZIP varchar(10),
@CustomerID int OUTPUT,
@Modified timestamp OUTPUT
)
AS
SET NOCOUNT ON;

-- Inserts a Customer row
INSERT INTO Customer
( LastName, FirstName, Address, City, State, ZIP )
VALUES
( @LastName, @FirstName, @Address, @City, @State, @ZIP ) ;

IF @@ROWCOUNT>0 AND @@ERROR=0 -- Checks if the last statement produced an error
-- Selects the primay key value and the generated timestamp which is put back into the dataset
SELECT @CustomerID = CustomerID,
@Modified = Modified
FROM Customer
WHERE (CustomerID = SCOPE_IDENTITY());
RETURN


Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 9/11/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
here is an article that uses output parameter. Refer this:

http://www.dotnetfunda.com/articles/article1648-how-to-do-custom-pagination-in-the-gridview-to-achieve-better-performance-i.aspx
Regards,

Awesome Coding !! :)

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 9/11/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!
If OBJECT_ID('Usp_Proc1') Is Not Null

Drop Proc Usp_Proc1
Go
Create Proc Usp_Proc1
(
@INParameter INT,
@OUTParameter VARCHAR(50) OUTPUT
)
As
Begin
If (@INParameter between 0 and 9)
Select @OUTParameter ='Single Digit'
Else If (@INParameter between 10 and 99)
Select @OUTParameter ='Double Digit'
Else If (@INParameter between 100 and 999)
Select @OUTParameter ='Triple Digit'
Else
Select @OUTParameter ='Negative or Morethan Triple Digit(s)'
End
Go
Declare @Result Varchar(50)

Exec Usp_Proc1 50, @Result Output
Select @Result [Output Parameter]
Go
Result
Output Parameter

----------------
Double Digit


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rajasekhar0544, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response