StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What you used was Simple Case function. Searched Case function should work (see below). I've just tried it in a very simple query:</p> <pre><code>DECLARE @type VARCHAR(100) DECLARE @input VARCHAR(100) DECLARE @value SQL_VARIANT SET @type = 'varchar' SET @input = 'test' SET @value = CASE WHEN @type = 'varchar' THEN @input WHEN @type = 'int' THEN CAST(@input AS VARCHAR) END </code></pre> <p>On SQL Server 2005 I am getting the same error, but the following is from SQL Server 2008 doc:</p> <p>Simple CASE function: </p> <ul> <li>Evaluates input_expression, and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.</li> <li>Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.</li> <li>If no input_expression = when_expression evaluates to TRUE, the SQL Server 2005 Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.</li> </ul> <p>Searched CASE function: </p> <ul> <li>Evaluates, in the order specified, Boolean_expression for each WHEN clause.</li> <li>Returns result_expression of the first Boolean_expression that evaluates to TRUE.</li> <li>If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.</li> </ul> <p>If this is true, then Simple Case will do all the CASTS and then decide qhich one to use, while Searched Case will evaluate the boolean expression and then decide which CAST to execute. This should be the solution.</p> <p>Otherwise, you can probably use IF ... THEN ... ELSE, as proposed in another answer.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload