Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to set Temporary variable(Flag) Expand / Collapse
Author
Message
Posted Thursday, December 11, 2008 9:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 15, 2009 10:23 PM
Points: 14, Visits: 56
declare @Input int,
@Record nvarchar(2000),
@ID int,
@Temp int

select @Input= ?,
@Record=?,
@ID = ?

if(@Input = 1)
begin
delete from Institutes
where @ID not in(select InstituteID from Res_UserEducationMapping)
end

if(@Input = 2)
begin
delete from Company
where @ID not in (select CompanyID from Res_UserExperienceMapping)
end

if(@Input = 3)
begin
delete from country
where @Record not in(select Country from User_AddressDetails)
and @Record not in(select Country from University)
and @Record not in(select Country from Res_UserExperienceMapping)
and @Record not in(select Country from Res_UserEducationMapping)
end


above Query will delete record from the table depending on Input parameter and where clause
Now the problem is that, I want to set the temporary variable depending on whether delete statement executes or not
Set @Temp = 1 if delete executes
set @Temp = 0 if not
how to do this?
can anubody help?
Post #618356
Posted Friday, December 12, 2008 12:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 5, 2011 1:38 AM
Points: 1,636, Visits: 604
Hello,
please specify what "if delete executes" means. Generally, delete statement will execute successfully, even if there are no rows to be deleted - but I suspect that what you mean is "if at least one row is deleted".



Post #618419
Posted Friday, December 12, 2008 4:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 15,556, Visits: 27,928
You can check the @@rowcount to verify how many, if any, records were deleted. Or you can use the OUTPUT clause to capture specifically which records were deleted.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #618489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse