How to set Temporary variable(Flag)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » How to set Temporary variable(Flag)

How to set Temporary variable(Flag)

Rate Topic

Display Mode

Topic Options

Author

Message

sar_kan25

Posted Thursday, December 11, 2008 9:09 PM

Grasshopper

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

Vladan

Posted Friday, December 12, 2008 12:31 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 05, 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

Grant Fritchey

Posted Friday, December 12, 2008 4:22 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187

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 »

Permissions