How to append dynamic where condition in stored procedure

try this


create your query in code behind and pass it as string parameter in stored procedure
Query += "where UserMaster.UserId = " + UserId + " and OrderMaster.OrderDate >= '" + DateFrom + " " + DateTime.MinValue.ToLongTimeString() + "'";


in sql server

declare @Query varchar(150)
set @Query = 'Your Query'
exec ('select * from Table'+ @Query)

Hi Try This,

mahen25 is right but he give you the generalize approach but here i try to give you answer by considering your condition,

Just Add this line into your code,
@Command="where firstname='" + strFirstName + "' and lastname='" +strLastName +"'";

Here,
strFirstName is a String Variable which Holds your FirstName cuming From Any Control ( Ex:TextBOx like this String strFirstName=text1.Text )

And strLastName is a String Variable which Holds your LastName cuming From Any Control as explain above.

Then Just Call Your Stored Produre By passing @Command as parameter,

ALTER Procedure [dbo].[madissues_searchAllMyFriends]
(
@UserId bigint,
@command varchar(200)
)
as
begin
select MIuser_id,firstname,lastname,profileimage from madissues_registration @command
end


I hope this will help you
Enjoy

Hi pratik it's not working. If you have any idea please tell me immediately.

Hi jainmilan002,

Try the below code..

create procedure [dbo].[TestDynamic]
@name varchar(50)=''
as
declare @id int
set @id=2
Declare @strSql varchar(8000)
Set @strSql = ' '

Set @strSql = @strSql + 'select * from tblEvents'
If @name is not null
 Begin
  Set @strSql=@strSql+' where eventid=' + Cast(@id as Varchar)
 End
Else
Begin
 Set @strSql = @strSql
End
Exec (@strSql)

Hope it helps..

Thanks..

Use dynamic query in sql stored procedure

set @Query = 'Select * FROM TableName where ''+ @Approval +'' order By Trans_Date'


print @query
Execute sp_executesql @query