Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to pass where condition as parameter of stored procedure Expand / Collapse
How to pass where condition as parameter of stored procedure
Poll ResultsVotes
How to pass where condition as parameter of stored procedure
0%
0
Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
Message
Posted Monday, April 29, 2013 9:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:22 PM
Points: 6, Visits: 14
How to pass where condition as parameter of stored procedure
Post #1447854
Posted Monday, April 29, 2013 9:31 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:10 PM
Points: 4,576, Visits: 8,351
The best way is not to do it.
Post #1447856
Posted Wednesday, May 1, 2013 2:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:57 PM
Points: 406, Visits: 210
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



Post #1448616
Posted Wednesday, May 1, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 13,471, Visits: 12,329
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448623
Posted Wednesday, May 1, 2013 3:03 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:55 AM
Points: 43,042, Visits: 36,196
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 2008, MVP
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

Post #1448625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse