Query converted into Dynamic SQL string within Stored Proc yields syntax error

• jabberpunch

  Valued Member

  Points: 54

  More actions

  January 7, 2011 at 4:51 pm

  #388584

  I've created a stored procedure that executes a dynamically generated query string based on a single input parameter. The resulting data looks something like this:

  emp_id display_name

  1 Alan (234)

  2 Bob (245234)

  3 Carl ()

  4 Christina (9556)

  5 Doug ()

  etc.

  This is the working SELECT statement that I use to retrieve the data:

  'SELECT emp_id, emp_name + '' '' + ''('' + CAST(COALESCE(emp_number,0) AS varchar(10)) + '')'' AS display_name FROM Employee ORDER BY emp_name'

  If an individual does not possess an emp_number the parentheses will still display regardless, and I wanted to clean this up a bit and remove them so I modified the above SELECT statement to look like this:

  'SELECT emp_id, emp_name + COALESCE(NULLIF(''('' + COALESCE(CAST(emp_number AS varchar(10)),'') +' ')'',''('' + '')''),'') AS display_name from Employee ORDER BY emp_name'

  This new query executes in SSMS Query Designer and yields the correct results (no parentheses without an accompanying emp_number), however, when I add this to the stored proc and execute it I receive "Incorrect syntax near ','."

  I've started to go blind trying to figure out where I went wrong with doubling up my quotes and was hoping another few sets of eyes could help me determine where I've gone wrong.

  Thanks!

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 7, 2011 at 5:27 pm

  #1270991

  jabberpunch (1/7/2011)

  ---

  I've created a stored procedure that executes a dynamically generated query string based on a single input parameter. The resulting data looks something like this:

  emp_id display_name

  1 Alan (234)

  2 Bob (245234)

  3 Carl ()

  4 Christina (9556)

  5 Doug ()

  etc.

  This is the working SELECT statement that I use to retrieve the data:

  'SELECT emp_id, emp_name + '' '' + ''('' + CAST(COALESCE(emp_number,0) AS varchar(10)) + '')'' AS display_name FROM Employee ORDER BY emp_name'

  If an individual does not possess an emp_number the parentheses will still display regardless, and I wanted to clean this up a bit and remove them so I modified the above SELECT statement to look like this:

  'SELECT emp_id, emp_name + COALESCE(NULLIF(''('' + COALESCE(CAST(emp_number AS varchar(10)),'') +' ')'',''('' + '')''),'') AS display_name from Employee ORDER BY emp_name'

  This new query executes in SSMS Query Designer and yields the correct results (no parentheses without an accompanying emp_number), however, when I add this to the stored proc and execute it I receive "Incorrect syntax near ','."

  I've started to go blind trying to figure out where I went wrong with doubling up my quotes and was hoping another few sets of eyes could help me determine where I've gone wrong.

  Thanks!

  Right where I put the asterisk (*) you had a space...

  'SELECT emp_id, emp_name + COALESCE(NULLIF(''('' + COALESCE(CAST(emp_number AS varchar(10)),'') +'*')'',''('' + '')''),'') AS display_name from Employee ORDER BY emp_name'

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]