Search using multiple parameter in Stored Procedure

Posted by Raj.Trivedi under Sql Server on 12/22/2013 | Points: 10 | Views : 794 | Status : [Member] [MVP] | Replies : 3
Hello Team,

I have a form which has option of Search Customer which has only one textbox
to enter CustomerId or Customer Account Number.

I want such a Stored Procedure where the user can enter either CustomerID or Customer Account Number and which either of any one matches the customer details should be fetched from the Table

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: vishalneeraj-24503 on: 12/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,you have to write Dynamic Stored Procedure and inside SP use Like operator to search,you can write Stored Procedure as

Create Procedure sp_search_customers(@customer_criteria_search varchar(100))

As
Begin
Declare @sql_query as varchar(max);
Set @sql_query = 'Select * from customer where customer_id like ''%' + @customer_criteria_search + '%'' or customer_name like ''%' + @customer_criteria_search + '%''';

print @sql_query;
Exec(@sql_query);
End


You can also print your query to track whether it's write by using print statement.


Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/23/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
No need of Dynamic SQL in the Stored procedure

DECLARE @Search varchar(10) = 101
SELECT * FROM TableName where CustomerID LIKE '%'+@Search+'%' OR CustomerAccNum LIKE '%'+@Search+'%'


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/3/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
"Mark as answer "

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response