How to use an OUTPUT parameter and check if a record exists

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  April 15, 2009 at 8:28 am

  #978118

  I do not know if you are using C# or VB so I used VB because I found out it is easier for C# folks to read the VB syntax then vice versa. 😀

  Dim SQLParam(2) As SqlParameter

  SQLParam(0) = New SqlParameter("@CommID", SqlDbType.Int)

  SQLParam(0).Value = CommID

  SQLParam(1) = New SqlParameter("@username", SqlDbType.VarChar, 50)

  SQLParam(1).Value = username

  SQLParam(2) = New SqlParameter("@status", SqlDbType.VarChar, 200)

  SQLParam(2).Direction = ParameterDirection.Output

  '.... execute your SP here.....

  Status = SQLParam(2).Value

  Make sure you always define the size of the SQLDBType.VarChar otherwise the SQL server will create a different execution plan for each length of the passed variable so you may have up to 50 executions plans just for the username variations.

  EDIT..- sorry, cound not get the code tags to work correctly for VB.

  ---------------------------------------------
  [font="Verdana"]Nothing is impossible.
  It is just a matter of time and money.[/font]

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  April 15, 2009 at 8:33 am

  #978126

  If you were to execute the procedure I posted above in SSMS, this is how you would do it:

  declare @id int,

  @uname varchar(50),

  @stat varchar(200);

  set @id = 1;

  set @uname = 'AUserName';

  exec dbo.checkComms @id, @uname, @stat output;

• Rafael-875076

  SSC Enthusiast

  Points: 186

  More actions

  June 4, 2009 at 12:06 pm

  #1004833

  I was involved with a similar scenario when I came across this posting. I modified the code which should work as expected. Notice that even though I'm using SELECT *..., it will not have a performance impact since the EXISTS condition will be met when the first record is found. Using a Count(*) will of course have a performance hit. Someone correct me if I am wrong. Thanks.

  alter procedure [dbo].[checkComms]

  @CommID int,

  @username varchar(50),

  @status varchar(200) OUTPUT

  as

  if exists

  (SELECT * FROM [Northwind].[dbo].[Comms] where storeCommID=@CommID and [UserName] = @username)

  begin

  SELECT @status ='Already Exists'

  end

  else

  Insert into [storeComms] (CommID, UserName) Values (@storeCommID,@UserName)

  SELECT @Status = 'Record Added!'

  go