如何在存储过程中附加动态where条件 [英] How to append dynamic where condition in stored procedure

问题描述

大家好

我在项目中使用存储过程.现在我要实现搜索
因此，每当我的项目条件发生变化时，它就会起作用.因此，我通过了存储过程中的条件.

Hello Everybody

I am using stored procedure in my project. Now i want to implement search
function so, everytime where condition of my project will change. so, i pass where condition in storedprocedure.

e.g @Command=where firstname="shailesh" and lastname="prajapati

存储过程:

Stored procedure:

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

我遇到错误...请依靠我...

I am getting error... Please rely me...

推荐答案

您可以使用EXECUTE 运行位于字符串变量内的语句.请参阅: http://msdn.microsoft.com/en-us/library/ms188332.aspx [ ^ ]

例如:

You can use EXECUTE to run a statement that''s inside a string variable. See: http://msdn.microsoft.com/en-us/library/ms188332.aspx[^]

For example:

EXEUTE @command

因此，将整个命令添加到变量中，然后使用EXEC运行它.

So add the whole command to the variable and then use run it using EXEC.

尝试此


在后面的代码中创建查询，并将其作为字符串参数传递给存储过程
查询+ =其中UserMaster.UserId =" + UserId +和OrderMaster.OrderDate> =""+ DateFrom +""+ DateTime.MinValue.ToLongTimeString()+"";


在sql server中

声明@Query varchar(150)
设置@Query =``您的查询''
exec(从表中选择*" + @Query)

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)

试试看，

mahen25是正确的，但他为您提供了一般化的方法，但在这里我尝试通过考虑您的情况为您提供答案，

只需将此行添加到您的代码中，
@ Command ="where firstname ="''+ strFirstName +''和lastname ="''+ strLastName +''";

在这里，
strFirstName是一个字符串变量，用于保存您从任何控件累积的名字(例如，像String strFirstName = text1.Text这样的TextBOx)

而且strLastName是一个字符串变量，用于保存您的姓氏，并从任何控件进行累加，如上所述.

然后只需通过传递@Command作为参数来调用存储的产品，

ALTER过程[dbo].[madissues_searchAllMyFriends]
(
@UserId bigint，
@command varchar(200)
)
如
开始
从madissues_registration @command
中选择MIuser_id，名字，姓氏，配置文件图像 结束


希望对您有帮助
享受

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

这篇关于如何在存储过程中附加动态where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！