macro substitution in sql

• Victor T. Alvarado

  Old Hand

  Points: 335

  More actions

  August 9, 2005 at 6:20 pm

  #88430

   

  hello to all, i just want butt-in into this discussion thread and know how to pass a condition to a sp, the condition contains a quote as the filter expr is string..

  exec stored_procedure 'select * from table1 where tag = '1''

  is this possible or is it better to do it with a script like this:

  declare @tagfilter char(1)

  set @tagfilter = '1'

  exec stored_procedure

       'db',

       'select * from table1 where tag = @tagfilter', 'c:\outputfile.txt'

  go

  im not sure if the script i presented above is correct, how is macro substitution done is sql? also, the stored_procedure im referring to is sp_dbm_query_to_file, i got it from this site, it claims to redirect the output of the passed query into a textfile, but so far im having difficulty producing the output file..

  any help will be appreciated, thanks ..

• noeld

  SSC Guru

  Points: 96590

  More actions

  August 9, 2005 at 6:39 pm

  #580887

  you can double the number of ' (single quotes)

  like

  exec stored_procedure 'select * from table1 where tag = ''1'''

  note that '' (two single quotes) <> " (one double quote)

  hth

   

  
  * Noel