SQL - Create Stored Procedures with optional parameters

If you want to make your code generic, say, sometimes you want to put certain criteria to the code to make it run faster,  and sometimes you just want to see all data no matter how long it will take. You can specify your declared parameter part like this:


ALTER PROCEDURE  dbo.sp_NAWAT_MISC_SpotfireInternalQC
(@state_name nvarchar(255) = NULL,
 @play_name  nvarchar(255) =  NULL
 )

 AS
BEGIN 

    ---- your code
           WHERE         (state_name = @state_name OR state_name is null)
                        AND  (play_name = @play_name OR  play_name is null) ;

END 
GO

or
(reference:SQL - Passing multiple values through one parameter in a stored procedure)

 AS
BEGIN 
  SET @Sql = '

    ---- your code
   

Spotfire - Some properties in Expressions

Properties in Expressions

---

There are three types of properties available in TIBCO Spotfire: document properties, data table properties and column properties. All of these can be used in expressions that control one or many settings. The properties can be interpreted in two different ways, either as actual values or as text values that can be used instead of column names or measures.

Insert as Text

When you want to use a string property in an expression where it should be interpreted as a column name or a measure you must use a specific syntax. The name of the property should be enclosed in the following: “${“ and “}”. So, for a document property this could look something like: ${MyProperty}. For a data table property, the first part should be the data table name within the symbols specified above, followed by a period and the property name also within curly brackets: ${My Data Table}.{MyProperty}. For a column property, the column name is also required: ${My Data Table}.{Column Name}.{MyProperty}.

When the $ sign is encountered in an expression, the property will be replaced by its corresponding value before the actual expression is evaluated (a preprocessing step). This means that if you type an expression like: sum(${Property1}) -${Property1}-${Property2}, and the value of Property1 is the column name Column1, and the value of Property2 is Column2, then the resulting expression will be: sum([Column1]) -[Column1]-[Column2].

If you do not want the property value to be interpreted as a column name but as a literal string value, you should put the property call within quotation marks. For example, if you have a property called "MyProperty" with the value "MyValue", the expression ${MyProperty} would return the resulting expression MyValue, where a column called "MyValue" is retrieved. However, the expression "${MyProperty}" would return the text string "MyValue" instead. If the string value consists of several words (e.g., "My Value"), then you should use $esc() or put brackets around the property expression in order to return a column: [${MyProperty}]. See General Syntax for more information about column names.

Note that it is very important to use the correct syntax for the expression at all times. Otherwise you may end up with a different result than you expected. If a property is inserted as a column where the expression expected a string, the first value of the specified column will be retrieved. For string columns, the result may be a valid expression, but for a column of another data type, you may receive an error.

If a property is inserted using the Insert Properties button in the Insert Calculated Column dialog or in the Custom Expression dialog, the property will automatically be added using the text (preprocessor) syntax.