Optional Parameters In SQL Server Reporting Services

Optional parameters In SSRS means if parameters have been selected, it would be passed back to SQL query as condition in WHERE clause, and when no parameter(s) have been selected, the WHERE condition should be not in use. Dynamic SQL is not the best solution. Following solution is much better:

SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)

What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.

There is no programmatic solution so far. However, following approach may help:

1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.

2, Classify parameters into hierarchy.

3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.

4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:

; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...

What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:

SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab

If you have problem on how to position this NULL value record, please refer to following article:

Special Function of ORDER BY Clause in T-SQL

Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.

Done.


http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

Special Function of ORDER BY Clause in T-SQL

Compare to MS Access Jet Engine SQL, T-SQL's ORDER BY clause is flexible, which can point either the column name or alias of the column. Which special function can be used for special purpose. Here is any example. Let us say, you need a data set in order:

SELECT DISTINCT Name
FROM Tab
ORDER BY Name

Then you need to add a record called "Unknown" at the top:

SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab

There is problem, you won't able to add ORDER BY clause in T-SQL because you used UNION. Following is next step:

SELECT Name
FROM
( SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

You won't be able to get the right result, because "Unknown" is not on the top. Solution is:

SELECT CASE WHEN Name = '0000' THEN 'Unknown' ELSE Name END AS New_Name
FROM
( SELECT '0000' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

Why use '0000'? Because it would be on the top after sort. In this case, if you use

ORDER BY New_Name

it won't work.


http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html

Picture of Information Technology

From Koncord Partners

Rounding Issue In SELECT TOP (n) PERCENT In T-SQL

There is a rounding issue In SELECT TOP (n) PERCENT In T-SQL, basically, TOP (n) PERCENT applies the CEIL function rather than FLOOR function. For instance, if there are total 4 valid rows, when 0 < n <= 25 would return 1 row, when 25 < n <= 50 would return 2 rows. If that is what you want, that is fine. If you want FLOOR function, it does not work. Following is the solution:

SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM dbo.dataset
WHERE Key_Column IS NOT NULL
) *
FROM dbo.dataset
WHERE Key_Column IS NOT NULL

Here, 4 is to generate 25% rows, and * is for all columns.

Various Sizes And Lengths In Database

But, it is suggested to use 30 as maximum column name length in SQL Server.

Feature, SQL Server 2000, Oracle 9i Database

database name length, 128, 8
column name length, 128, 30
index name length, 128, 30
table name length, 128, 30
view name length, 128, 30
stored procedure name length, 128, 30
max columns per index, 16, 32
max char() size 8000, 2000
max varchar() size, 8000, 4000
max columns per table, 1024, 1000
max table row length, 8036, 255000
max query size, 16777216, 16777216
recursive subqueries, 40, 64
constant string size in SELECT, 16777207, 4000
constant string size in WHERE, 8000, 4000


http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm

Dynamic SQL in T-SQL

First of all, the dynamic SQL is not part of the stored procedure, but constitutes its own scope. So, it is a independent thread. Your task indeed is to pass the bind variable and/or parameter into that thread and retrieve the result back to your stored procedure.

There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.

In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.

If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.

To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:

SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'

In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.

After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.

Okey, here is an example:

DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs

In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see

, @EachColumn = @EachColumn
, @result = @result

The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.


http://www.sommarskog.se/dynamic_sql.html

Error Message: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position...

This SQL Server full error message is: "The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."

It happens when a bind variable is placed in ORDER BY clause. For dynamic SQL, the solution is Database Engine Stored Procedures sp_executesql. For example:

Your failed original statement:
SET @stddev = SELECT STDEVP(@EachColumn)
FROM Temp_IQM_Input
ORDER BY @EachColumn

The solution:
DECLARE @stddevString NVARCHAR(1000)
SET @stddevString = ' SELECT @out = STDEVP(@para) '
+ ' FROM Temp_IQM_Input '
+ ' ORDER BY @para '
DECLARE @stddev INT
EXECUTE sp_executesql @stddevString
, N'@para VARCHAR(32), @out INT OUTPUT'
, @para = @EachColumn
, @out = @stddev OUTPUT
;

For sp_executesql statement, the 1st line is the sql query in string, 2nd line to define the bind variables, if the parameter used for output, it needs to be stated. The rest lines are for variables assignments.

It is painful because there are too many WIP parameters; but it is the way.

Set Vs Select in T-SQL

Select will allow more than one records to to assigned to variable:

SELECT @var = singleColumn
FROM tab

Set either allow one record, or generate an error while the original value of variable remains unchanged:

SET @var =
( SELECT singleColumn
FROM tab
)


http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx