Karl Prosser wrote:
> with dynamic SQL i'm of all sort of quoting issues and the ugliness of
> such code to maintain etc.. i've had a couple of simple ideas to deal
> with quoting and inserting variables into dynamic sql, i just want to
> pass them pass people just in case there is a huge glaring hole in my
> logic
>
> declare @ratdog varchar(3000)
> declare @firstname varchar(20)
> set @firstname = 'mark'
>
> set @ratdog =
> ' select top 100 *
> from employee
> where em_lname like "pr%"
> and em_fname = "<<FIRSTNAME>>"
> '
> set @ratdog = REPLACE(@ratdog,'<<FIRSTNAME>>',@firstname)
> set @ratdog = REPLACE(@ratdog,'"','''')
> exec (@ratdog)
>
> basically typing " inside the text so you don't haev to to + ''' +
> 'bla' + ''' sort of thing all over the time (or even just the + @qt +
> 'bla' + @qt sort of and you don't have to break up the string for
> inserting variables into the equation . i,e the <<FIRSTNAME>> , you
> only really have to break up the string for if then claues for whether
> code is to be added to the query (optional where clauses for
> instance..
>
> are there any holes in this solution.. should i use something other
> than " for quotes and << >> for variables?
> i know that for such a system, i could easily make a program to
> convert any plain sql into statements like the above.
>
> Karl

"Karl Prosser" <klumsy@xtra.co.nz> wrote in message
news:7156880c.0409172126.57f110f0@posting.google.com...
> with dynamic SQL i'm of all sort of quoting issues and the ugliness of
> such code to maintain etc.. i've had a couple of simple ideas to deal
> with quoting and inserting variables into dynamic sql, i just want to
> pass them pass people just in case there is a huge glaring hole in my
> logic
>
> declare @ratdog varchar(3000)
> declare @firstname varchar(20)
> set @firstname = 'mark'
>
> set @ratdog =
> ' select top 100 *
> from employee
> where em_lname like "pr%"
> and em_fname = "<<FIRSTNAME>>"
> '
> set @ratdog = REPLACE(@ratdog,'<<FIRSTNAME>>',@firstname)
> set @ratdog = REPLACE(@ratdog,'"','''')
> exec (@ratdog)
>
> basically typing " inside the text so you don't haev to to + ''' +
> 'bla' + ''' sort of thing all over the time (or even just the + @qt +
> 'bla' + @qt sort of and you don't have to break up the string for
> inserting variables into the equation . i,e the <<FIRSTNAME>> , you
> only really have to break up the string for if then claues for whether
> code is to be added to the query (optional where clauses for
> instance..
>
> are there any holes in this solution.. should i use something other
> than " for quotes and << >> for variables?
> i know that for such a system, i could easily make a program to
> convert any plain sql into statements like the above.
>
> Karl