我写过这个方法来读取数据库的数据。
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.
更多推荐
发布评论