0

I am trying to create a stored procedure which will concatenate a SQL statement which will then be executed , al la 

DECLARE @sql nvarchar(max)
generate statement - concatenate string into @sql
EXEC @sql

It needs to contain this as part of the where clause:

S.Name_Last , ISNULL(', ' + S.Name_First, '')

Problem I am having is stringing along this part of the select clause into the select statement, i.e. the number of "ticks" needed to successfully concatenate the ISNULL part of the select statement. No matter what I try, cannot get the right "tick" count to imbed the single quote into my statement.

Any suggestions?

Scott
  • 165
  • 1
  • 3
  • 15
  • what's the where clause actually look like? – S3S Oct 29 '18 at 14:31
  • Why are you trying to do such a thing in the first place? The very idea is flawed and exposes you to SQL Injection attacks. A hacker can *always* add one more `'` and inject code. If you *really* need dynamic queries, use an ORM like Entity Framework or a microORM like Dapper. – Panagiotis Kanavos Oct 29 '18 at 14:32
  • 1
    While dynamic SQL is cumbersome, it's not complicated: systematically double up all the apostrophes with search/replace every time you lift something into a string. Do this right before you add the outer apostrophes. Do not count them -- if you make a mistake, just start over. – Jeroen Mostert Oct 29 '18 at 14:32
  • 1
    How does `S.Name_Last , ISNULL(', ' + S.Name_First, '')` become part of the `WHERE` clause? it's not a boolean expression. – Thom A Oct 29 '18 at 14:39
  • I would guess it's part of a larger function like `CONCAT`. We need more to go on here or OP can just use @JeroenMostert approach. – Jacob H Oct 29 '18 at 14:41
  • Would be good to see a sample of the desired final query. Also backing up @panagiotis, best to understand Dynamic SQL before using it. (Erland Sommarskog's article at http://www.sommarskog.se/dynamic_sql.html is pretty definitive on the subject.) – Philip Kelley Oct 29 '18 at 14:42

0 Answers0