如何在存储过程中使用if之后的条件(How to use if condition in stored procedure after where)

我写过这个方法来读取数据库的数据。

string query = @"SELECT IDproduct, name_product, first_price, final_price, max_registered_price, date_record_shamsi, final_date_view_shamsi, count_views, image_1, collection_1, city, model FROM Table_khodro WHERE active = 0 "; public DataSet ViewProductKhodro(string name, int firstPrice, int finalPrice, string subCollection, byte state,Int16 model) { if (name !="no name") query += @" AND (name_product LIKE '%'+(@name_product)+'%')"; if (finalPrice != 0) query += @" AND (first_price BETWEEN @first_price AND @final_price )"; if (subCollection !="انتخاب کنید") query += @" AND (collection_1 = @collection_1 )"; if (state != 0) query += @" AND (id_state = @id_state )"; if(model != 0) query += @" AND (model = @model )"; SqlCommand cmd = new SqlCommand(query); if (name != "no name") { cmd.Parameters.AddWithValue("@name_product", name); } if (finalPrice !=0) { cmd.Parameters.AddWithValue("@first_price", firstPrice); cmd.Parameters.AddWithValue("@final_price", finalPrice); } if (subCollection != "انتخاب کنید") cmd.Parameters.AddWithValue("@collection_1", subCollection); if (state !=0) cmd.Parameters.AddWithValue("@id_state", state); if (model != 0) cmd.Parameters.Add("@model",model ); // Fill the dataset return FillDataSet(cmd, "Table_khodro"); }

现在我想为这个方法编写一个存储过程。

是否可以使用一个存储过程？

如何？

请帮忙

I have written this method for read the data of database.

string query = @"SELECT IDproduct, name_product, first_price, final_price, max_registered_price, date_record_shamsi, final_date_view_shamsi, count_views, image_1, collection_1, city, model FROM Table_khodro WHERE active = 0 "; public DataSet ViewProductKhodro(string name, int firstPrice, int finalPrice, string subCollection, byte state,Int16 model) { if (name !="no name") query += @" AND (name_product LIKE '%'+(@name_product)+'%')"; if (finalPrice != 0) query += @" AND (first_price BETWEEN @first_price AND @final_price )"; if (subCollection !="انتخاب کنید") query += @" AND (collection_1 = @collection_1 )"; if (state != 0) query += @" AND (id_state = @id_state )"; if(model != 0) query += @" AND (model = @model )"; SqlCommand cmd = new SqlCommand(query); if (name != "no name") { cmd.Parameters.AddWithValue("@name_product", name); } if (finalPrice !=0) { cmd.Parameters.AddWithValue("@first_price", firstPrice); cmd.Parameters.AddWithValue("@final_price", finalPrice); } if (subCollection != "انتخاب کنید") cmd.Parameters.AddWithValue("@collection_1", subCollection); if (state !=0) cmd.Parameters.AddWithValue("@id_state", state); if (model != 0) cmd.Parameters.Add("@model",model ); // Fill the dataset return FillDataSet(cmd, "Table_khodro"); }

Now I want to write a stored procedure for this method.

Is it possible with one stored procedure?

How to?

Please help

最满意答案

您可以在查询中包含所有这些参数。

SELECT IDproduct , name_product , first_price , final_price , max_registered_price , date_record_shamsi , final_date_view_shamsi , count_views , image_1 , collection_1 , city , model FROM Table_khodro WHERE active = 0 AND (@NAME IS NULL OR NAME_PRODUCT LIKE @NAME) AND (@FIRSTPRICE IS NULL OR FIRST_PRICE BETWEEN @FIRSTPRICE AND @FINALPRICE)

因此，您的存储过程将具有所有必需的参数。您还必须传入您认为可能为null或可能不为null的参数。例如，如果@firstprice为null，则sql查询将在不考虑First_price列的情况下执行，否则它将使用介于价格之间并带来相应的结果集

希望能帮助到你。

You can include all those parameters in your query.

SELECT IDproduct , name_product , first_price , final_price , max_registered_price , date_record_shamsi , final_date_view_shamsi , count_views , image_1 , collection_1 , city , model FROM Table_khodro WHERE active = 0 AND (@NAME IS NULL OR NAME_PRODUCT LIKE @NAME) AND (@FIRSTPRICE IS NULL OR FIRST_PRICE BETWEEN @FIRSTPRICE AND @FINALPRICE)

So your stored procedure would have all the required parameters. You will also have to pass in those parameters which you think might or might not be null. For example if @firstprice is null your sql query will execute without considering First_price column else it will use the between price and bring the appropriate result set

Hope it helps.

更多推荐