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

sp with one or more input parameters Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 3:11 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:31 AM
Points: 50, Visits: 50
Hi,

I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.

What would be the most proficient way of achieving this?

I had initially looked at using IF, TRY ie:

       IF @SearchField= 'order_reference'
BEGIN TRY

select data
from mytables

END TRY

However I'm not sure this is the most efficient way to handle this.

Thanks for any advice
Post #1523579
Posted Tuesday, December 17, 2013 3:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:41 AM
Points: 356, Visits: 2,611
This sounds like a 'catch-all' query and can be achieved with dynamic SQL.

Have a look at the below for an excellent description and examples:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Post #1523585
Posted Tuesday, December 17, 2013 4:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:31 AM
Points: 50, Visits: 50
That looks like it will do the trick! Thank you!
Post #1523605
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse