How to pass where condition as parameter of stored procedure

Question

Post reply

How to pass where condition as parameter of stored procedure

sidharthgs6

SSC Veteran

Points: 238
More actions
April 29, 2013 at 9:26 pm

#293225

How to pass where condition as parameter of stored procedure

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

The best way is not to do it.

_____________
Code for TallyGenerator

DAVNovak Hall of Fame Points: 3120 More actions · Answer 2

I agree with Sergei is that it is best not to do it. That being said, there are ways to pass the where clause to a SP.

One way that comes to mind is to great a Sp with a large varchar input parameter. This parameter would be used to pass in your where clause.

In the Sp you then would have to use dynamic sql and build you sql statement and execute it.

It is not very pretty and opens up the possibility of SQL injection attacks and chances of errors.

Dave

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

Agreed about not sending in a where clause. I would guess that if you are wanting to pass in a where clause it is because you are building a search type of proc.

Take a look at this article from Gail about catch-all queries.

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I would advise against any architecture or design that requires the passing of portions of queries as parameters. It's messy, it causes a hell of a lot of problems.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass