HV-Lima Peru

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?

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.

2. Logic-Flowchart:
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)

–Case 9-16
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)
        –Case 4
        –Case 3
        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?)
       –Case 6
       IF @strOrElse = GOTO FIN –Case5 (0100)
       GOTO EELSE –D there is Else-Input=1 (0101)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: