How to use variable in where clause in dynamic sql

Question

Hi how can I use a variable in the where clause in MSSQL. The @UI variable changes and is not static, I get an error in sql doing it like this. It works with the column names when I want to run an update or insert statement.

declare @UI varchar(50) 
set @UI = 'IDNumber'

exec 'set @ClientExist = isnull((select top 1 clientid from Clients 
                                 where '+ @UI +' = ''6001016119085'' 
                                     or '+ @UI +' = ''None'' 
                                     and cancelled <> 1 
                                 order by ClientID desc),0)'

            declare @sql nvarchar(max)
set @sql =  N'declare @StagingID int
    declare @ClientID int 
    declare @VehicleID int
    declare @tempID1 int
    declare @tempID2 int
    declare @ClientExist int

    set @tempID1 = 0
    set @tempID2 = 0
    set @ClientID = 0
    set @VehicleID = 0

    declare @rows integer
    declare @column1 nvarchar(500)
    declare @column2 nvarchar(500)
    declare @column3 nvarchar(500)
    declare @column4 nvarchar(500)
    declare @column5 nvarchar(500)
    declare @column6 nvarchar(500)
    declare @column7 nvarchar(500)
    declare @column8 nvarchar(500)
    declare @column9 nvarchar(500)
    declare @column10 nvarchar(500)
    declare @column11 nvarchar(500)
    declare @column12 nvarchar(500)
    declare @column13 nvarchar(500)
    declare @column14 nvarchar(500)
    declare @column15 nvarchar(500)
    declare @column16 nvarchar(500)
    declare @column17 nvarchar(500)
    declare @column18 nvarchar(500)
    declare @column19 nvarchar(500)
    declare @column20 nvarchar(500)
    declare @column21 nvarchar(500)
    declare @column22 nvarchar(500)
    declare @column23 nvarchar(500)
    declare @column24 nvarchar(500)
    declare @column25 nvarchar(500)
    declare @column26 nvarchar(500)
    declare @column27 nvarchar(500)
    declare @column28 nvarchar(500)
    declare @column29 nvarchar(500)
    declare @column30 nvarchar(500)
    declare @column31 nvarchar(500)
    declare @column32 nvarchar(500)
    declare @column33 nvarchar(500)
    declare @column34 nvarchar(500)
    declare @column35 nvarchar(500)
    declare @column36 nvarchar(500)
    declare @column37 nvarchar(500)
    declare @column38 nvarchar(500)
    declare @column39 nvarchar(500)
    declare @column40 nvarchar(500)
    declare @column41 nvarchar(500)
    declare @column42 nvarchar(500)
    declare @column43 nvarchar(500)
    declare @column44 nvarchar(500)
    declare @column45 nvarchar(500)
    declare @column46 nvarchar(500)
    declare @column47 nvarchar(500)
    declare @column48 nvarchar(500)
    declare @column49 nvarchar(500)
    declare @column50 nvarchar(500)

    Select * 
    into #temp 
    from Staging 
    where brokerID = '+ @BrokerID +' and ImportStatus <> ''Processed''

    set @rows = (select COUNT(1) from #temp)    

    WHILE @rows > 0
        BEGIN
            set @tempID2 = @tempID1

            select @StagingID = StagingID
            ,@column1 = column1
            ,@column2 = column2
            ,@column3 = column3
            ,@column4 = column4
            ,@column5 = column5
            ,@column6 = column6
            ,@column7 = column7
            ,@column8 = column8
            ,@column9 = column9
            ,@column10 = column10
            ,@column11 = column11
            ,@column12 = column12
            ,@column13 = column13
            ,@column14 = column14
            ,@column15 = column15
            ,@column16 = column16
            ,@column17 = column17
            ,@column18 = column18
            ,@column19 = column19
            ,@column20 = column20
            ,@column21 = column21
            ,@column22 = column22
            ,@column23 = column23
            ,@column24 = column24
            ,@column25 = column25
            ,@column26 = column26
            ,@column27 = column27
            ,@column28 = column28
            ,@column29 = column29
            ,@column30 = column30
            ,@column31 = column31
            ,@column32 = column32
            ,@column33 = column33
            ,@column34 = column34
            ,@column35 = column35
            ,@column36 = column36
            ,@column37 = column37
            ,@column38 = column38
            ,@column39 = column39
            ,@column40 = column40
            ,@column41 = column41
            ,@column42 = column42
            ,@column43 = column43
            ,@column44 = column44
            ,@column45 = column45
            ,@column46 = column46
            ,@column47 = column47
            ,@column48 = column48
            ,@column49 = column49
            ,@column50 = column50
            from #temp

            --Check for duplicate client, insert or update then
            declare @UIvalue varchar(50)

            set @UIvalue = (select StageColumn from ColumnStructure where [uniqueidentifier] = 1)

            set @ClientExist = isnull((select top 1 clientid from Clients where '+ @UI +' = @UIvalue or '+ @UI +' = ''None'' and cancelled <> 1 order by ClientID desc),0)
            if @ClientExist = 0
            Begin
                if len(''' + @ClientCCol + ''') > 0 and len(''' + @ClientSCol + ''') > 0
                Begin
                    Insert into Clients (' + @ClientCCol + ', System, DateAdded) Values (@' + @ClientSCol + ', ''Import'', getdate())
                    set @ClientID = (select @@Identity)
                End
            End
            ELSE
            Begin
                Update Clients set ' + @UpdateC + ' where clientid = @ClientExist
            End

            set @tempID1 = (select @@identity)

            if @ClientID > 0
            Begin
            Update Clients set Province = (select p.ProvinceID from Provinces p inner join clients c on c.ProvinceDesc = p.Province where clientid = @ClientID  and p.Province = c.ProvinceDesc)
                ,Country = (select co.CountryID from Countrys co inner join clients c on c.CountryDesc = co.Country where clientid = @ClientID  and co.Country = c.CountryDesc)
                ,Title = (select t.TitleID from Titles t inner join clients c on c.TitleDesc = t.Title where clientid = @ClientID  and t.Title = c.TitleDesc)
            where ClientID = @ClientID 
            End

            declare @Registration varchar(100)
            declare @EngineNum varchar(100)
            set @Registration = (select StageColumn from ColumnStructure where ClientsCol = ''Registration'' and BrokerID = ' + @BrokerID + ')
            set @EngineNum = (select StageColumn from ColumnStructure where ClientsCol = ''EngineNum'' and BrokerID = ' + @BrokerID + ')

            --Check for duplicate vehicle, insert or update then
            set @VehicleID = isnull((select top 1 isnull(VehicleID, 0) from VehicleInformation where clientid = @ClientID and Registration = @Registration and EngineNum = @EngineNum order by VehicleID desc),0)

            if @VehicleID = 0 
            Begin
                if len(''' + @VehicleCCol + ''') > 0  and len(''' + @VehicleSCol + ''') > 0
                Begin
                    Insert into VehicleInformation (' + @VehicleCCol + ', ClientID) Values (@' + @VehicleSCol + ', @ClientID)
                    set @VehicleID = (select @@Identity)
                End
            End
            Else
            Begin
                Update VehicleInformation set ' + @UpdateV + ' where VehicleID = @VehicleID
            End

            if @VehicleID > 0
            Begin
                Update VehicleInformation set Make = (select vm.MakeID from VehicleMake vm inner join VehicleInformation v on v.Makedesc = vm.Make where v.VehicleID = @VehicleID and vm.make = v.makedesc) where VehicleID = @VehicleID 
            End


            if (@tempID1 <> 0 or @tempID1 is not null) and @tempID1 > @tempID2
                update dbo.Staging
                set DateAdded = getdate(), ImportStatus = ''Processed''
                where StagingID = @StagingID
            Else
                update dbo.Staging
                set DateAdded = getdate(), ImportStatus = ''Error Occured''
                where StagingID = @StagingID

            delete from #temp where StagingID = @StagingID
            set @rows = (select COUNT(1) from #temp)

            set @VehicleID = 0
            set @ClientID = 0
    END

            drop table #temp'


        exec (@sql)

This is the actual query im running allt the variables in '+ +' im calling from outside..now this did work but when I put the @UI in it started gaving me errors...with the chr(39) in it wont compile, so will @gordon answer help met out in server 2008, and do i have to declare all the variables inside the quotes like @gordon did with his example with the @Clientid

Answer 1

Use sp_executesql. That way, you can pass variables in and out of a statement. In your case, the syntax is something like this:

declare @UI varchar(50);
declare @ClientId varchar(50);
set @UI = 'IDNumber';
declare @sql nvarchar(max);

SET @sql = N' select top 1 @ClientId = clientid
           from Clients
           where '+ QUOTENAME(@UI) + N' = ''6001016119085'' or ' 
           + QUOTENAME(@UI) + N' = ''None'' and cancelled <> 1';

exec sp_executesql @sql
                 , N'@ClientId varchar(50) OUTPUT'
                 , @ClientId = @ClientId OUTPUT

set @ClientExist = coalesce(@ClientId, 0); 

The documentation for sp_executesql is here.

Answer 2

in case it's sqlserver 2000 where sp_executesql does not work create a temp table and store the data there

declare @UI varchar(50) 
set @UI = 'IDNumber'
create table #result ( ClientExist bit ) -- temp table to store the data
declare @SQL varchar(4000)
set @SQL = 
  'insert into #result (ClientExist) 
   select isnull((select top 1 clientid 
   from Clients where '+ @UI +' = ''6001016119085'' or '+ @UI +' = ''None'' and cancelled <> 1 order by ClientID desc),0)'

execute(@SQL)

declare @ClientExist  bit
SET @ClientExist = (select top 1 ClientExist from #result)

I copied the select from the question "as is", I usually escape single quotes with chr(39) so it would be: ...where '+ @UI +' = '+chr(39)+'6001016119085'+chr(39)+' or '...