I am converting a stored procedure which I had previously written as a string then, using BIT parameters I decided whether to append certain WHERE/ON clauses
This sp is passed a number of comma-separated strings and then some of the dynamic WHERE clauses are like:
IF @pUse_Clause_A THEN SET @WhereClause = @WhereClause + ' AND [FIELD_A] IN (' + @pComma_Separated_List_A + ')'
In this case, @pComma_Separated_List_A is something like '1,3,6,66,22' ... a list of the things I want included.
Now I am changing these from strings into TVP,s so I can just use "real" SQL like AND [FIELD_A] IN (SELECT [TVP_FIELD] FROM @pTVP_A)
When I do this, I don't like the string-building method
However, I also don't like having to nest the IF statements.
IF A
ENTIRE SQL WHERE A
ELSE
ENTIRE SQL WITHOUT WHERE CLAUSE
The more parameters I add, the more complicated it gets:
IF A
IF B
SQL WHERE A AND B
ELSE
SQL WHERE A
ELSE
IF B
SQL WHERE B
ELSE
SQL
What I would rather do is something like this:
SELECT * FROM TABLE
WHERE 1=1
CASE USE_A WHEN 1 THEN
AND [FIELD_A] IN (SELECT A FROM TBP_A)
END
CASE USE_B WHEN 1 THEN
AND [FIELD_B] IN (SELECT B FR开发者_运维知识库OM TVP_B)
END
I know it ignored SQL outside the chosen "IF" result, but having all that duplicated statement seems sloppy
Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).
Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:
Dynamic Search Conditions in T-SQL by by Erland Sommarskog
The Curse and Blessings of Dynamic SQL by Erland Sommarskog
精彩评论