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

Awesome Coding !! :)

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

Output Parameter

----------------

Double Digit

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions