CODEWITHSUNDEEP
sql-server
user6180198
user6180198

Reputation: 33

Is there any way to add dummy column in dynamic query execution in SQL Server?

Is there any way to add dummy column in dynamic query execution in SQL Server?

declare @sql nvarchar(max)

declare @centerid int

@sql= 'Select ms.SetTitle,se.Quantity as ccp ,'' as dlp  from StockEntry_Center se
             inner join Mast_Set ms on se.SetID=ms.SetID where se.IsDeleted=''False'' and se.CenterID='+convert(nvarchar,@centerid)

exec(@sql)

In the above dynamic query I want to add a dummy column '' as dlp. But it shows an errors. Thanks in advance.

Upvotes: 1

Views: 433

Answers (3)

FDavidov
FDavidov

Reputation: 3675

Re-reading your select (and making it easier to walk-through):

@sql = 'Select ms.SetTitle,se.Quantity as ccp ,
               ''                      as dlp  
          from   StockEntry_Center se
         inner join 
                 Mast_Set          ms 
         on      se.SetID = ms.SetID 

    where se.IsDeleted=''False'' 
      and se.CenterID='+convert(nvarchar,@centerid) ;

This converts to:

Select ms.SetTitle,se.Quantity as ccp ,
       '                       as dlp  
      from   StockEntry_Center se
   inner join 
             Mast_Set          ms 
     on      se.SetID = ms.SetID 

 where se.IsDeleted=''False'' 
   and se.CenterID=convert(nvarchar,@centerid) ;

As you can see, there is a single ' at the left of as dlp.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

You are getting an error because of the single quotes as gofr1 has already explained but I would also make a few other changes in your query: 

declare @sql nvarchar(max), @centerid int = 100

SET @sql= N' Select ms.SetTitle
                   ,se.Quantity as ccp 
                   ,'''' as dlp  
             from StockEntry_Center se
             inner join Mast_Set ms on se.SetID = ms.SetID 
             where se.IsDeleted=''False'' 
             and se.CenterID = @centerid'

Exec sp_executesql @sql
                  ,N'@centerid int'
                  ,@centerid

Use sp_executesql and pass the parameters as Parameters rather then concatenating it to your dynamic sql, you have only an Integer variable in this case but if you ever do this concatenation with a string variable you and exposing yourself to a possible sql-injection attack, better to be safe than sorry.

Upvotes: 4

gofr1
gofr1

Reputation: 15987

Use '''' as dmp or NULL as dmp. I bet you got errors because of quotes.

Upvotes: 0

Related Questions