April 2, 2014 at 5:21 pm
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply