How to set Temporary variable(Flag)

  • 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?

  • 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".

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply