Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

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


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= ?,
@ID = ?

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

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

if(@Input = 3)
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)

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


Group: General Forum Members
Last Login: Tuesday, November 22, 2016 12:47 AM
Points: 1,804, Visits: 749
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



Group: General Forum Members
Last Login: Yesterday @ 6:22 AM
Points: 17,171, Visits: 32,132
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 Query Performance Tuning
SQL Server Execution Plans

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

Add to briefcase

Permissions Expand / Collapse