数据库存储过程 [英] database stored procedure

问题描述

您好，



我在dotnet上有一个表单，根据gridview中的过滤器数据提取大约有20个字段，所以我想制作一个存储过程根据过滤条件进行数据提取.........



简而言之，例如有国家，城市，产品，项目等字段，质量，位置等我希望如果我们只选择国家而不是根据这个数据获取，如果我们选择两个字段，如国家和城市，那么数据取决于那个等等

和一个更多的事情是这个领域彼此无关每个领域都是separte ....



请回复





谢谢和问候

Srishti

Hello ,

I have a form on dotnet in which there are around 20 fields according to this filter data fetch in gridview so i want to make a stored procedude in which data fetching is done accordind to filter condition .........

in short for example there are fields like country, city,product, item,quality,location etc so i want that if we select only country than according to this data fetched and if we select two fields like country and city then data is fetch accoding to that and so on
and one more thing that field are not related to each other each field are separte....

Please Reply


Thanks & Regards
Srishti

推荐答案

你可以做到，但它非常可怕凌乱，正如你在.NET中尝试做的那样，你可能已经看到了 - 这可能就是为什么你想把它转移到SQL所以你不必考虑它！ ：笑：



没有神奇的方法可以做到这一点：在.NET而不是SQL中实际上更容易做到，因为它的字符串范围很广操作更好，但在任何一种情况下原则都是相同的。



要在SQL中执行此操作，您必须将SELECT语句组装为varchar字符串，然后执行EXEC字符串。

我不打算为你做这件事，因为在SQL中它是一个单一的CASE WHEN语句，使用前缀来分隔子句：

0 ）将查询字符串设置为SELECT< your fields list> FROM< Your table>

1）以前缀设置为WHERE开头

2）检查第一个参数。如果它存在，则将前缀附加到查询字符串，然后将参数数据添加到该字符串。将前缀设置为AND

3）检查第二个参数。如果它存在，则将前缀附加到查询字符串，然后将参数数据添加到该字符串。将前缀设置为AND

重复。



它在.NET中更容易，更整洁，更易于维护 - 至少你有阵列！

You could do it, but it''s horribly messy, as you have probably seen when trying to do it in .NET - which is presumably why you want to move it to SQL so you don''t have to think about it! :laugh:

There is no magic way to do it: It''s actually easier to do in .NET rather than SQL because it''s range of string manipulations is better, but the principle is the same in either case.

To do it in SQL you have to assemble a SELECT statement as a varchar string, then EXEC the string.
I''m not going to do it for you, because in SQL it''s a monolithic CASE WHEN statement, using a prefix to separate clauses:
0) Set the query string to "SELECT <your fields list> FROM <Your table>"
1) Start with the prefix set to " WHERE "
2) Check the first parameter. If it is present, then append the prefix to the query string, then add your parameter data to that. Set the prefix to " AND "
3) Check the second parameter. If it is present, then append the prefix to the query string, then add your parameter data to that. Set the prefix to " AND "
Repeat.

It''s easier, tidier and more maintainable in .NET - at least you have arrays!

这篇关于数据库存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！