Click here to Skip to main content
15,670,137 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I need to develop a dynamic stored procedure to search for an item into table columns

I have developed a Static SQL Stored Procedure.
It is as follows:

CREATE PROCEDURE [dbo].[spUserInfo_SearchSp]
    @SearchItem varchar(50),<pre></pre>
    @SearchBy varchar(50)
        UserName, FirstName, LastName, EmailId, IsActive
    From UserInfo
       (@SearchBy='UserName' AND UserName like '%'+@SearchItem+'%')
    OR (@SearchBy='FirstName' AND  FirstName like '%'+@SearchItem+'%')
    OR (@SearchBy='LastName' AND LastName like '%'+@SearchItem+'%')
    OR (@SearchBy='EmailId' AND EmailId like '%'+@SearchItem+'%');

It works just fine.
But I need the same thing be done with Dynamic SQL.
Can anybody help me with this?

I don't get it. You already have a Stored Procedure in place. It takes couple of input parameters in order to return back some data.

What you need now is simply give these input parameters a value at runtime through your code and call this stored procedure.

I don't think you need to create a dynamic stored procedure for your requirement. A static one with different parameter values is all you need.
Share this answer
Hello Sandeep,
Thanks for ur reply.

But I want to use dynamic SQL here.
As I m learning dynamic SQL, I can't find way how to do it?
Can u help me to achive it?
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900