Search using multiple parameter in Stored Procedure

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	"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

Latest Posts