sql server swap data between rows problem

Question

I was asking b4 about swaping query to swap data between rows in same table and i got that qurey

ALTER PROCEDURE [dbo].[VehicleReservationsSwap]
        -- Add the parameters for the stored procedure here
        (@FirstVehicleID int, 
        @secondVehicleID int,
        @WhereClause nvarchar(2000))
    AS
    BEGIN

Create Table #Temp
(
    VehicleID int 
    ,VehicleType nvarchar(100) 
    ,JoinId int
)


DECLARE @SQL varchar(8000)
SET @SQL ='Insert into #Temp (VehicleID,VehicleType,JoinId) SELECT 
        VehicleID,VehicleType,CASE WHEN VehicleID = ' + Cast(@FirstVehicleID as varchar(10)) + ' then ' + Cast(@secondVehicleID as varchar(10)) + ' ELSE ' + Cast(@FirstVehicleID as varchar(10)) + ' END AS JoinId
        FROM Reservations
        WHERE VehicleID in ( ' + Cast(@FirstVehicleID as varchar(10)) + ' , ' + Cast(@secondVehicleID as varchar(10)) + ' )' + @WhereClause
EXEC(@SQL)

--swap values 

UPDATE y 
    SET y.VehicleID = #Temp.VehicleID
        ,y.VehicleType = #Temp.VehicleType
    FROM Reservations     y 
        INNER JOIN #Temp  ON y.VehicleID = #Temp.JoinId 
    WHERE y.VehicleID in (@FirstVehicleID,@secondVehicleID) 


Drop Table #Temp


END

this query take 2 parameters and swaping all rows returned for each parameter.

the problem is the query swaps just if each parameter (forign key) has values I need to make swaping in case if one of them has no vlue.

I hope if some one can help me in that .

Thanks,

Answer 1

I got it

ALTER PROCEDURE [dbo].[VehicleReservationsSwap]
    -- Add the parameters for the stored procedure here
    (@FirstVehicleID int, 
    @secondVehicleID int,
    @WhereClause nvarchar(2000))
AS
BEGIN

Create Table #Temp
(
    VehicleID int 
    ,VehicleType nvarchar(100) 
    ,JoinId int
)


DECLARE @SQL varchar(8000)
SET @SQL ='Insert into #Temp (VehicleID,VehicleType,JoinId) SELECT 
        VehicleID,VehicleType,CASE WHEN VehicleID = ' + Cast(@FirstVehicleID as varchar(10)) + ' then ' + Cast(@secondVehicleID as varchar(10)) + ' ELSE ' + Cast(@FirstVehicleID as varchar(10)) + ' END AS JoinId
        FROM Reservations
        WHERE VehicleID in ( ' + Cast(@FirstVehicleID as varchar(10)) + ' , ' + Cast(@secondVehicleID as varchar(10)) + ' )' + @WhereClause
EXEC(@SQL)

--swap values 
declare @count1 int
declare @count2 int
set @count1 = (select COUNT(reservationid) as count1 from Reservations where VehicleID = @FirstVehicleID)
set @count2 = (select COUNT(reservationid) as count2 from Reservations where VehicleID = @secondVehicleID)

if @count1 > 0 and @count2 > 0
 begin
    UPDATE y 
    SET y.VehicleID = #Temp.VehicleID
        ,y.VehicleType = #Temp.VehicleType
    FROM Reservations     y 
        INNER JOIN #Temp  ON y.VehicleID = #Temp.JoinId 
    WHERE y.VehicleID in (@FirstVehicleID,@secondVehicleID)     
 end
 else if @count1 <= 0 and @count2 > 0
 begin
  UPDATE y 
    SET y.VehicleID = #Temp.JoinId
        ,y.VehicleType = #Temp.VehicleType
    FROM Reservations     y 
        INNER JOIN #Temp  ON y.VehicleID = #Temp.VehicleID 
    WHERE y.VehicleID = @secondVehicleID    
 end
 else if @count1 > 0 and @count2 <= 0
 begin
  UPDATE y 
    SET y.VehicleID = #Temp.JoinId
        ,y.VehicleType = #Temp.VehicleType
    FROM Reservations     y 
        INNER JOIN #Temp  ON y.VehicleID = #Temp.VehicleID 
    WHERE y.VehicleID = @FirstVehicleID     
 end

Drop Table #Temp


END