Menu
Is it possible to set a variable in a case statement? My code doesn't work. Thanks all.
CREATE PROCEDURE spReport @q1 INT, @q2 INT AS BEGIN -- Dates for the 2 different quarters to be viewed DECLARE @StartDateQ1 DATETIME DECLARE @EndDateQ1 DATETIME DECLARE @StartDateQ2 DATETIME DECLARE @EndDateQ2 DATETIME SELECT CASE @q1 WHEN 1 THEN SET @StartDateQ1 = '20130401' END 
597
to set the value of a single variable according to a CASE expression. If your real logic is more complicated (e.g. need to set multiple variables inside a condition) look at IF ... ELSE instead. CASE is an expression not a flow of control construct. Show activity on this post.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
You can use
SET @StartDateQ1 = CASE @q1 WHEN 1 THEN '20130401' END to set the value of a single variable according to a CASE expression.
If your real logic is more complicated (e.g. need to set multiple variables inside a condition) look at IF ... ELSE instead.
CASE is an expression not a flow of control construct.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
