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

Gordon Linoff · Accepted Answer · 2014-05-26 13:49:56Z

7

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.

edited May 26, 2014 at 13:49

answered May 23, 2014 at 13:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Steve Pettifer Over a year ago

Beat me to the practical example. Will dump my answer.

M.Ali Over a year ago

@StevePettifer this should work and I am sorry sir to edit your answer so many times :)

Steve Pettifer Over a year ago

Not my answer squire!! The main thing is it's ended up as a correct, runnable answer. How we got there doesn't really matter too much.

M.Ali Over a year ago

I was apologizing to Gordon :) . Man is a genius I only wish I knew 1/10 of what he knows :)

Andriy M Over a year ago

What is the point of QUOTENAME(cancelled)? (Or, perhaps, I should ask what cancelled is in that context.)

|

georstef · Accepted Answer · 2014-05-23 15:55:26Z

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 '...

edited May 23, 2014 at 15:55

answered May 23, 2014 at 13:30

georstef

1,3883 gold badges12 silver badges18 bronze badges

3 Comments

Gordon Linoff Over a year ago

I don't believe the table #result is in scope for the embedded SQL, but I don't have SQL Server 2000 to test it on.

T.S.G Over a year ago

I'm using server 2008 r2 and thanks it worked but got new error now 'Conversion failed when converting the nvarchar value' is the error and then the whole dynamic string is included in it

georstef Over a year ago

@T.S.G: try to escape single quotes with chr(39) but Gordon Linoff's answer should cover you for sqlserver2008

Collectives™ on Stack Overflow

How to use variable in where clause in dynamic sql

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related