Offshore your .NET/SQL Development to Latin America! Languages: en-de-es
How to join in T-SQL logically a multi-value Reporting Services Parameter with a Freetext Entry?
November 18, 2007Posted by on
From Entry-Logic to the final T-SQL Program:
The most interesting aspect in the following procedure is the one-to-one mapping between the discovered cases in 1., the flowchart in 2. and the T-SQL program in 3..
Initially apparently not very elegant goto-programming in SQL brings here two big advantages:
a. Nothing is easier to discover as a coding error, so when in later years another programmer needs to change the SQL logic, every aspect can be followed, reconstructed and changed, step-by-step.
b. Any ending goto-branch in T-SQL can be implemented by its own stored procedure, which leads to the best possible utilization of precompiled SQL execution plans.
1. Case Construction:
– in cases 9 to 16: it doesn’t matter what else the user clicks or if she enters any freetext, ALL incl. NULL means ALL.
– since SSRS doesn’t accept no entry in a multi-value field, the value NOTHING was invented. This means: to enter nothing in the multi-value field, the value Nothing must be selected.
The 16 cases from above are flagged (A, B, C, D)
3. T-SQL Program:
Like above, the 16 cases are identified in parentheses (abcd)
IF CHARINDEX(‘All’,@SearchValues)<>0 GOTO ALLL –A ‘All’=1??? selected
IF CHARINDEX(‘Nothing’,@SearchValues) = 0 — ‘Nothing’=0 not selected (00)
IF @SearchValues <> ” — there is Multi-Input=1 (001)
IF @strOrElse <> ” — there is Else-Input=1 (0011)
GOTO MULTIORELSE –C
GOTO MULTI –B — there is NO Else-Input=0 (0010)
IF CHARINDEX(‘Nothing’,@SearchValues) <> 0 –‘Nothing’=1 selected (01)
–Cases 7 and 8 will be considered in later release
–if @SearchValues <> ” GOTO FIN — there is Multi-Input=1 (011?)
IF @strOrElse = ” GOTO FIN –Case5 (0100)
GOTO EELSE –D there is Else-Input=1 (0101)