help with dynamic SQL syntax

Question

help with dynamic SQL syntax

smknox

Hall of Fame

Points: 3275
More actions
April 20, 2012 at 8:13 am

#272965

I have a stored procedure that takes input parameters as shown:
ALTER PROCEDURE usp_ClinicalStudy_UpdateGroup
(
@DataPointID int=NULL
,@PrimaryKeyName varchar(100)=NULL
,@WhereClause varchar(1000)=null
,@TableName varchar(100)=NULL
)
I am trying to pass in a "WHERE" clause of filter parameters from the user and then using dynamic SQL and executing @CMD1:
EXEC sp_executesql @CMD1
My Where clause is ok when I pass in numbers, but having trouble with the quotes when it's text.
There Where clause that I want in the dynamic SQL is:
WHEN v.ErInterpret = 'negative' AND v.PrInterpret = 'negative'
Hope this is enough info. What would be the syntax to get that where clause into my dynamic SQL?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

smknox Hall of Fame Points: 3275 More actions · Answer 1

Additional comment:

Another approach would be to build the entire SQL statement in the frontend and skip the dynamic SQL on the backend. Which is preferred or industry standard?

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

You need to provide a bit more detail. What is @cmd1?

To be honest this has the look of a catch all query. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

smknox (4/20/2012)
Additional comment:
Another approach would be to build the entire SQL statement in the frontend and skip the dynamic SQL on the backend. Which is preferred or industry standard?

Building sql in the frontend is incredibly dangerous. Anytime you want to take user input as part of a query you need to be cautious about sql injection. I don't really get the point of the dynamic sql here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Could you post the code for the stored procedure plus some sample data to be passed into the procedure so we can see how it works (parameters that currently work for you) and doesn't work (parameters that cause problems).

smknox Hall of Fame Points: 3275 More actions · Answer 5

Thanks All: I found a typographical error and resolved the issue. Meanwhile I really appreciate the link to the "Catch All query" debate. Very helpful!