sql server swap data between rows problem
- by AmRoSH
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,
