Stored Proc with two updates

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  April 2, 2014 at 5:21 pm

  #151335

  Hi All,

  I have written an Stored Procedure too delete records from two tables. The scripts work correctly when there run separately, but inside the SP only the first statement executes with message 1 row effected the second one produces 0 rows effected. but if i run the second statement by its self it achieves the expected results. So I am wondering if there is a SQL rule that I am not aware of for when running multiple statements in a SP or what?

  USE [XXX]

  GO

  /****** Object: StoredProcedure [dbo].[DisableUsers] Script Date: 01/07/2014 05:23:43 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  Create Proc [dbo].[DisableUsers](@gen_username nvarchar(50), @company_id int)

  as

  begin

  update [userproperties]

  set [gen_department] = 'Non User'

  where USER_ID in(select USER_ID

  from users

  where gen_Username = @gen_username ) and company_id = @company_id

  update [XXX].[dbo].[user_group_members]

  set group_id =(select ID

  from user_groups g

  where Name = 'Non User' and g.company_id = @company_id)

  where user_id in(select USER_ID

  from users

  where gen_Username = @gen_username)

  and group_id = (select ID

  from user_groups g

  inner join dbo.userproperties p

  on g.company_id = p.company_id and g.name = p.gen_department

  where p.user_id in (select USER_ID

  from users u

  where u.gen_Username = @gen_username)

  and p.company_id = @company_id)

  end

  GO

  exec disableusers @gen_username = 'cant', @company_id = 27

  Thanks for any help.