hi guys, this is making my head hurt.Background:I am building a generic emailing process that gets its data from the database. Each email will contain a table of data. The columns, and number of columns will be different for each email and so I am trying not to embed anything in the stored procedure that would limit what I can send.As there will be more than row of data, I will need to go through the resutls set with a cursor.Becasue I don't know how many field I need to return, I decided I would build a dynamic SQL statement by cursoring through the field names and adding the <tr>, <td> and </td> as required so that the query actually only ever returns one field (already formatted)This means the dynamic statement will have to be called with exec sp_executesql.I have the following select statementSELECT '<tr><td>'+cast([dob] as varchar(100))+'</td><td>' + cast([PatientName] as varchar(100))+'</td></tr>' FROM ##table
Which returns me nicely formatted table rows.[dob] and [PatientName] need to be built into the string as variablesso the first step is to insert variables in their place with the correct single quotesso that the sring is parsed correctlydeclare @var1 as varchar(100), @var2 as varchar(100)set @var1 = 'dob'set @var2= 'PatientName'select '<tr><td>'+cast(@var1 as varchar(100))+'</td><td>'+cast(@var2 as varchar(100))+'</td></tr>' from ##table
does't work - I get 'dob' and 'PatientName' on each lineBuilding the string dynamically works as the @var is replaced with the cursor value.The problem comes when you try to save this as a string that the sp can read, you need to protect the single quotes. Can anyone take the above statement and correctly protect it so that it works with sp_executesql
Protecting single quotes.
Posted - 2009-05-14 : 12:04:16
