Stored Procedure and Output value

• osxman

  Old Hand

  Points: 366

  More actions

  March 20, 2019 at 1:48 pm

  #371758

  Hi,

  I have created a stored procedure with two outputs. The @RowsUpdated works fine, but I am struggling to get a value for @id_user. I have tried using   if EXISTS(SELECT @id_user = id_user from tbl_Users where username=@username)
  but when saving the SP it says there's a syntax error near =

  I am calling the SP from within a C# application.

  ALTER Procedure [dbo].[sp_MarkAsLeaver]
  @username varchar(50),
  @LeaverDetails varchar(100),
  @RowsUpdated int output,
  @id_user varchar(50) output
  AS
  if EXISTS(SELECT id_user from tbl_Users where username=@username)
  BEGIN
  UPDATE tbl_users SET username=@LeaverDetails where username = @username
  END
  SET @RowsUpdated = @@ROWCOUNT

  GO

  
  Any help is much appreciated.

  Thanks

• ScottPletcher

  SSC Guru

  Points: 101118

  More actions

  March 20, 2019 at 2:14 pm

  #2025062

  Maybe this?:
  
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER Procedure [dbo].[sp_MarkAsLeaver]
    @username varchar(50),
    @LeaverDetails varchar(100),
    @RowsUpdated int output,
    @id_user varchar(50) output
AS
SET @id_user = NULL;
SELECT @id_user = id_user FROM tbl_Users WHERE username=@username
IF @id_user IS NOT NULL
BEGIN
    UPDATE tbl_users SET username=@LeaverDetails WHERE username = @username
    SET @RowsUpdated = @@ROWCOUNT
END /*IF*/
ELSE
BEGIN
    SET @RowsUpdated = 0
END /*ELSE*/
/*end of proc*/
GO

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• osxman

  Old Hand

  Points: 366

  More actions

  March 20, 2019 at 2:28 pm

  #2025067

  That'll do the trick :O)

  Thanks!