quotes in dynamic sql. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

quotes in dynamic sql.

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-21 : 22:36:25

I confused with a lot of quotes in dynamic sql.


create procedure dbo.xxx @emp
declare @begin varchar(50)
declare @end    varchar(50)
select @begin = '2007-1-1'
select @end   = '2009-1-1'
declare @condition varchar(255)
declare filterBy varchar(255) 
declare filterBy = 'and table1.value=''' + @emp+''''
declare @query varchar(8000)
select condition = ' select ...'
select condition = condition + 'where t between '''+@begin+' and '+ @end
select query = condition + @filterBy
exec(@query)

I know there are a lot of errors here.
But what is the correct format?

Thanks for your time and kind help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 00:06:01

quote:

Originally posted by zhshqzyc

I confused with a lot of quotes in dynamic sql.


create procedure dbo.xxx 
@emp varchar(100)
AS
declare @begin varchar(50)
declare @end    varchar(50)
select @begin = '2007-1-1'
select @end   = '2009-1-1'
declare @condition varchar(255)
declare @filterBy varchar(255) 
declare @filterBy = 'and table1.value=''' + @emp+''''
declare @query varchar(8000)
select @condition = ' select ...'
select @condition = @condition + 'where t between '''+@begin+''' and '''+ @end + ''''
select @query = @condition + @filterBy
exec(@query)

I know there are a lot of errors here.
But what is the correct format?

Thanks for your time and kind help.

try like above

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 00:07:28

Also see this to understand how sql server handles single quotes

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-22 : 08:18:49

if @query = @condition + @filterBy1 + @filterBy2
Do I need to add quotes around variables?

select @query = @condition '''+ @filterBy1 + @filterBy2 +'''

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-22 : 08:37:35

No, If you are using any strings in between vairables, there only we use quotes like as below

select @query = @condition + @filterBy1 + @filterBy2 + '''AND''' + @filterBy3

Did you see the visakh query? He gives an good example.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-22 : 08:56:21

[code]+ '''AND''' +[/code]
or
[code]
'''+AND+''' [/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 11:08:48

quote:
Originally posted by zhshqzyc

+ '''AND''' +

or

'''+AND+'''

nope not both just need

+ ' AND ' +

please read and understand link i posted.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-24 : 09:43:28

Can you look at the code below?
I need help.

 select @MfilterBy2 =' and TRANS_HEADER.BUS_DT between ''' + @BeginDate + ''' 12:00 and ''' + @EndDate + ''''



select @filterBy = ' '
select @groupBy = ' '

if datalength(ltrim(rtrim(@StoreID))) > 0
        select @filterBy = ' and T.STR_ID = ''' + @StoreID + ''''

select @filterBy = @filterBy + ' and L.RGST_MDE = 0 '
select @filterBy = @filterBy + ' and T.TND_CD = 5 '
select @filterBy = @filterBy + ' and T.ACT_FG <> 0 '

if @RegID <> 'ALL'
        select @filterBy = @filterBy + ' and L.RGST_ID = ''' + @RegID + ''''



select @groupBy = ' group by T.TND_DESCR '

exec('insert #TmpRptData
select
		EmpID           = ''' + @Operator + ''',
        RegID           = ''' + @RegID + ''',
        RptGrp          = 5,
        GrpName         = '''',
        ColName         = T.TND_DESCR,
        subColAmt       =sum(L.TND_ACCM_AMT),
        subColCount     =sum(L.TND_TRAN_CNT),
        ColAmt          = NULL,
        ColCount        = NULL
from  TENDER T inner join  TILL_TND_CUR L

     on   T.TND_CD = L.TND_TYP_CD
        and T.STR_ID = L.STR_ID
where '''
       + @MfilterBy2 + @filterBy + @groupBy + '''')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:57:08

quote:

Originally posted by zhshqzyc

Can you look at the code below?
I need help.

 select @MfilterBy2 =' and TRANS_HEADER.BUS_DT between ''' + @BeginDate + ''' 12:00 and ''' + @EndDate + ''''



select @filterBy = ' '
select @groupBy = ' '

if datalength(ltrim(rtrim(@StoreID))) > 0
        select @filterBy = ' and T.STR_ID = ''' + @StoreID + ''''

select @filterBy = @filterBy + ' and L.RGST_MDE = 0 '
select @filterBy = @filterBy + ' and T.TND_CD = 5 '
select @filterBy = @filterBy + ' and T.ACT_FG <> 0 '

if @RegID <> 'ALL'
        select @filterBy = @filterBy + ' and L.RGST_ID = ''' + @RegID + ''''



select @groupBy = ' group by T.TND_DESCR '

exec('insert #TmpRptData
select
		EmpID           = ''' + @Operator + ''',
        RegID           = ''' + @RegID + ''',
        RptGrp          = 5,
        GrpName         = '''',
        ColName         = T.TND_DESCR,
        subColAmt       =sum(L.TND_ACCM_AMT),
        subColCount     =sum(L.TND_TRAN_CNT),
        ColAmt          = NULL,
        ColCount        = NULL
from  TENDER T inner join  TILL_TND_CUR L

     on   T.TND_CD = L.TND_TYP_CD
        and T.STR_ID = L.STR_ID
where '
       + @MfilterBy2 + @filterBy + @groupBy )

modify like above

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 10:04:03

Not to mention SQL INJECTION!
Use parametrized queries.

E 12°55'05.63"
N 56°04'39.26"

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-24 : 15:33:02

Can you tell me what's wrong here?
I haven't figured it out.

Thanks

select @qry ='insert #TmpData
select
		EmpID           = ''' + @Operator + ''',
        RegID           = ''' + @RegID + ''',
        RptGrp          = 1,
        GrpName         = '''',
        ColName         = ''Cash'',
        subColAmt       =null,
        subColCount     =null,
        (select sum(L.TND_ACCM_AMT) from TENDER T inner join TILL_TND_CUR L on T.TND_CD = L.TND_TYP_CD and T.STR_ID = L.STR_ID) as ColAmt,
        (select sum(L.TND_TRAN_CNT) from TENDER T inner join TILL_TND_CUR L on T.TND_CD = L.TND_TYP_CD and T.STR_ID = L.STR_ID) as)ColCount

from  TENDER T , TILL_TND_CUR L

where '
      + @MfilterBy1 + @filterBy + @groupBy
exec(@qry)

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-24 : 15:37:10

ignore it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:57:05

quote:
Originally posted by zhshqzyc

ignore it.

he was telling this method is prone to SQL Injection attack and suggesting to use parameterised query instead.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-25 : 00:05:09

The easiest way to troubleshoot these types of queries is print the value that you are trying to exec, so do this

DECLARE @sql varchar(max)
SET @sql = <your statements>
--EXEC (@sql)
PRINT @sql

Then you can see exactly what the code that your statements generates looks like and you can copy it and try to execute it and see where the errors are.
BUT - if you don't know what SQL Injection is, then look it up and make sure you aren't going to be opening a security hole.

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2008-11-25 : 15:07:29

quote:

Originally posted by visakh16

quote:

Originally posted by zhshqzyc

Can you look at the code below?
I need help.

 select @MfilterBy2 =' and TRANS_HEADER.BUS_DT between ''' + @BeginDate + ''' 12:00 and ''' + @EndDate + ''''



select @filterBy = ' '
select @groupBy = ' '

if datalength(ltrim(rtrim(@StoreID))) > 0
        select @filterBy = ' and T.STR_ID = ''' + @StoreID + ''''

select @filterBy = @filterBy + ' and L.RGST_MDE = 0 '
select @filterBy = @filterBy + ' and T.TND_CD = 5 '
select @filterBy = @filterBy + ' and T.ACT_FG <> 0 '

if @RegID <> 'ALL'
        select @filterBy = @filterBy + ' and L.RGST_ID = ''' + @RegID + ''''



select @groupBy = ' group by T.TND_DESCR '

exec('insert #TmpRptData
select
		EmpID           = ''' + @Operator + ''',
        RegID           = ''' + @RegID + ''',
        RptGrp          = 5,
        GrpName         = '''',
        ColName         = T.TND_DESCR,
        subColAmt       =sum(L.TND_ACCM_AMT),
        subColCount     =sum(L.TND_TRAN_CNT),
        ColAmt          = NULL,
        ColCount        = NULL
from  TENDER T inner join  TILL_TND_CUR L

     on   T.TND_CD = L.TND_TYP_CD
        and T.STR_ID = L.STR_ID
where '
       + @MfilterBy2 + @filterBy + @groupBy )

modify like above

You remove 12:00 but I need it, can you tell me how to add it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources