Stored Procedure OutPut Parameters

Question

i need to write a stored procedure which will return a string.logic is

when user try to insert a new record i need to check whether that record already exist.if exist need to return msg "Record exist" else return "Inserted"

following is what i have done for the moment and i'm stuck here.can some one help me to complete the procedure

CREATE PROCEDURE [dbo].[spInsetPurpose]
@Purpose VARCHAR(500),
@Type VARCHAR(6),
@Result VARCHAR(10)= NULL OUTPUT
AS
BEGIN
Declare @Position VARCHAR(20)
DECLARE @TempTable TABLE  (Purpose VARCHAR(500))

INSERT INTO @TempTable
SELECT Purpose FROM tblPurpose WHERE Purpose=@Purpose

INSERT INTO tblPurpose(Purpose,[Type]) VALUES(@Purpose,@Type) 

END

mabstrei · Accepted Answer · 2011-10-21 07:10:20Z

3

To check if the row already exists you can do

If Exists (Select Top 1 1 from tblPurpose where Purpose = @Purpose and [Type] = @Type)
Begin
   Insert Into tblPurpose
     (Purpose, [Type])
   Select 
     @Purpose, @Type

   SET @Result = 'Inserted'
End
Else
Begin
   SET @Result = 'Record exists'
End

edited Oct 21, 2011 at 7:10

answered Oct 21, 2011 at 7:02

mabstrei

1,2403 gold badges14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored Procedure OutPut Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related