?
Solved

Can i Customise the where query of a stored procuedure based on an input parameter

Posted on 2009-12-30
11
Medium Priority
?
187 Views
Last Modified: 2012-05-08
Can i Customise the where query of a stored procuedure based on an input parameter e.g.

Lets say that i have a query such as

select * from table1 where column1='a'

But lets say i wanted to use this in a stored procedure and optionally add another condition if say @param=1

e.g.
if then @param=1
then we'd run select * from table1 where column1='a' and column2='b' instead.

I know i can do this via if else statments but the real code is rather large and complex and would result in 10x the length. Thus the reason im trying to see if this can be done a different way.

Thanks
Webbo

0
Comment
Question by:Webbo_1980
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 10

Accepted Solution

by:
lucius_the earned 2000 total points
ID: 26145650
One way is to use dynamic SQL code... It means you compile your code as a string (varchar) and then you execute it via sp_executesql. See here:
http://www.sommarskog.se/dynamic_sql.html
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 26145683
u can use case statement like

case when @param=1 then << run your query>> when @param=1 then << run your another query>>   else  <nothing> end ;

try this..
0
 
LVL 3

Expert Comment

by:tsqlguy
ID: 26145699
Yeppp this is how you would write it :-)

CREATE PROC dbo.StoredProcedureNameGoesHere
(
@param int = 0
)
AS
BEGIN

IF @param = 0
BEGIN
select * from table1 where column1='a'
END

IF @param = 1
BEGIN
select * from table1 where column1='a' and column2='b'
END

END
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 4

Expert Comment

by:Ram4020
ID: 26145804
select * from table1
where column1 = isnull (@col1, column1)
and column2 = isnull (@col2, column2)

Make sure input parameter @col1 and @col2 are made as NULL if empty string at the begining of stored proc.

There is a chance that performance is slow as even if only @col1 is supplied the query will check for both columns.
0
 
LVL 4

Expert Comment

by:igni7e
ID: 26145818
select *
from table1
where
(
   column1='a'
   or (@param=1 AND column1='a' AND column2='b')
)

=)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26146062
SELECT *
from table1
where column1 = 'A' and (  (@param=1 and  column2='b' ) or 1 =1  )
0
 

Author Comment

by:Webbo_1980
ID: 26146140
Thanks lucius_the:
But sorry to everyone else - i didnt see the additional comments prior to accepting this solution, if i had i would have shared the points, as i've since used a combination of apporaches having ready everything above.
0
 
LVL 3

Expert Comment

by:tsqlguy
ID: 26146237
Hrm... the guy who link spams takes the cake.. :-( sad day for those trying to actually help :-(
0
 
LVL 3

Expert Comment

by:tsqlguy
ID: 26146261
Although if you're still looking for more to read, this is always helpful...

http://lmgtfy.com/?q=using+parameters+in+sql+stored+procedures
0
 

Author Comment

by:Webbo_1980
ID: 26146352
Sorry tsqlguy it was a genuine mistake i promise
0
 
LVL 10

Expert Comment

by:lucius_the
ID: 26146355
If the author thinks my post was not helpful, I have no objection for a refund.

to tsqlguy: My response was exactly what the author was looking for. Why would I retype the procedure when there are pages explaining the whole thing in detail ?? Also, the author clearly noted that he could solve the problem with a CASE statement (or IF-s). Your post was actually not helpful - you were giving a solution he already had, and was not looking for that. And you even complain :) Come on...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question