Select @variable not working

Question

I can't seem to wrap my mind around a little situation I have. I am trying to set a variable using an if condition; I have also tried using a case statement as well, but keep receiving error. Below is what I am working with and it's dynamic SQL as well... The variable I am trying to set is @EBP_Allow... Can someone shine some light on this?

What I need it to do for example...

 SELECT 
    @EBP_Allow = IF @Year > 2014 THEN DO a SELECT ELSE DO Something else...

What I have now...

SELECT          
    @OU_Allow = Optional_Unit_Allowed_Flag,
    @BU_Allow = Basic_Unit_Allowed_Flag,
    @EU_Allow = Enterprise_Unit_Allowed_Flag,
    @WU_Allow = Whole_Farm_Unit_Allowed_Flag,
    @EBP_Allow = I NEED TO USE SUB SELECT, IF OR CASE TO SET THIS                              
FROM dbo.@YEAR_Insurance_Offer  
WHERE       
    (dbo.@YEAR_Insurance_Offer.State_Code = @StateCode) AND 
    (dbo.@YEAR_Insurance_Offer.County_Code = @CountyCode) AND 
    (dbo.@YEAR_Insurance_Offer.Crop_Code = ''@CropCode'') AND 
    (dbo.@YEAR_Insurance_Offer.Insurance_Plan_ID = @PlanId) @TypeCondition @PracticeCondition

Here's an update; it seems that when ran it's still jumping over my condition...

                                 CASE 
                                    WHEN @YEAR < 2015
                                        THEN ''N''
                                    WHEN @YEAR > 2014
                                        THEN (
                                                SELECT Enterprise_Unit_By_Practice_Allowed_Flag
                                                FROM dbo.@YEAR_Insurance_Offer  
                                                WHERE       
                                                    (dbo.@YEAR_Insurance_Offer.State_Code = @StateCode) AND 
                                                    (dbo.@YEAR_Insurance_Offer.County_Code = @CountyCode) AND 
                                                    (dbo.@YEAR_Insurance_Offer.Crop_Code = ''@CropCode'') AND 
                                                    (dbo.@YEAR_Insurance_Offer.Insurance_Plan_ID = @PlanId) @TypeCondition @PracticeCondition   
                                             )
                                 END

If I replace the WHEN @Year > 2014 with this...

WHEN @YEAR > 2014
  THEN ''N''

It work's just fine... For some reason or another when I have the select in there it's telling me that Enterprise_Unit_By_Practice_Allowed_Flag is an invalid column, but it's not?

Can you add to your question which exactly is the error that you receive? — Mauricio Arias Olave, Jan 23 '15 at 14:37
you can use `case` there, with the syntax `case when condition then valueA else valueB end` — paul, Jan 23 '15 at 14:37
You might be able to use `CASE` and won't be able to use `IF`. But if you expect help with this, you need to describe *what* the logic is meant to be. — Damien_The_Unbeliever, Jan 23 '15 at 14:38
@paul no I can because it depends on the '@Year' variable sent in.. — Trevor, Jan 23 '15 at 14:38
And what's the error? It might be in regards to this line: `(dbo.@YEAR_Insurance` ?. In regards of `I NEED TO USE SUB SELECT, IF OR CASE TO SET THIS` - then it's a case or subselect - not IF. — Allan S. Hansen, Jan 23 '15 at 14:38
Try using the [**CASE**](https://msdn.microsoft.com/en-us/library/ms181765.aspx) — Radu Gheorghiu, Jan 23 '15 at 14:38
Well I did add that @Damien_The_Unbeliever, but Radu edited the post and removed it — Trevor, Jan 23 '15 at 14:39
Just so you know, IF is not allowed in select statements. It is a control flow command and selects/inserts/udates/deletes are excuted within an IF not the other way around. — HLGEM, Jan 23 '15 at 18:21

Radu Gheorghiu · Accepted Answer · 2015-01-23T14:47:13.030

3

In order to set @EPB_Allow you can use a CASE statement and you can add multiple WHEN clauses to treat different situations:

SELECT          
    @OU_Allow = Optional_Unit_Allowed_Flag,
    @BU_Allow = Basic_Unit_Allowed_Flag,
    @EU_Allow = Enterprise_Unit_Allowed_Flag,
    @WU_Allow = Whole_Farm_Unit_Allowed_Flag,
    @EBP_Allow = CASE 
                     WHEN @Year > 2014 
                         THEN (SELECT column FROM Table where conditions1...) 
                     WHEN @Year > 2013
                         THEN (SELECT column FROM Table where conditions2...)
                     ELSE (SELECT column FROM Table where conditions3...)
                 END                              
FROM dbo.@YEAR_Insurance_Offer  
WHERE       
    (dbo.@YEAR_Insurance_Offer.State_Code = @StateCode) AND 
    (dbo.@YEAR_Insurance_Offer.County_Code = @CountyCode) AND 
    (dbo.@YEAR_Insurance_Offer.Crop_Code = ''@CropCode'') AND 
    (dbo.@YEAR_Insurance_Offer.Insurance_Plan_ID = @PlanId) 
    @TypeCondition @PracticeCondition

Also, make sure that if you have defined @EBP_Allow as NVARCHAR then you have to make sure that the datatype returned by the query which selects what value you want to assign the variable is of the same datatype (otherwise use conversion functions).

And you have to make sure that the query that you specify in the THEN part will return only one result.

edited Jan 23 '15 at 14:47

answered Jan 23 '15 at 14:41

Radu Gheorghiu

20,049
16
72
107

I have tried that, but if the year > 2014 I need to do a select statement to actually pull that value.... – Trevor Jan 23 '15 at 14:43
@436f6465786572 Well, you can replace the string I've put in with the `SELECT` statement. Just make sure it returns only 1 result. – Radu Gheorghiu Jan 23 '15 at 14:44
@436f6465786572 It's likely the subquery for your >2014 branch is returning more than 1 row. Take a look at the conditions, or use `TOP 1` to return only 1 row. – Code Different Jan 23 '15 at 14:49
@ZoffDino I wouldn't necessarily recommend `TOP 1`, but since we have no more details about this, we can assume that `TOP 1` is a valid solution. – Radu Gheorghiu Jan 23 '15 at 14:52
I have tried all of these, the issue is it's telling me that a column doesn't exist when it does. I can take that chunk out and run it in a new query and it work's just fine? Please see edit's, thanks! – Trevor Jan 23 '15 at 17:46
@436f6465786572 Can you post the exact error you are getting? – Radu Gheorghiu Jan 23 '15 at 17:47
I've got it, the replace I was doing was messing the case statement up... Thanks Again! – Trevor Jan 23 '15 at 19:26

score 1 · Answer 2 · answered Jan 23 '15 at 18:18

OK first it seems you have fallen into the horrible practice of creating new tables with each year. This is a huge database antipattern and if this is the first year you have done this, I suggest an immediate redesign as this just gets harder and harder to deal with as you add more years.

If you can't, well then at least learn from this and never allow anyone to design a table like this in the future. In enterprise level datbase you can partition by date and in smaller ones, you dont need to have differnt tables just a where clause to filter by year and good indexing.

But you seem to be stuck using dynamic SQL to do everything (another reason why this is a horrible design choice). So you might as well learn how to use dynamic SQl correctly.

First read and thorughly understand this link before you try to write code against this database design:

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

Here is an example below of how you build and view the generated SQl for a dynamic SQl statement. This will help you corretly build the statement before you try to execute it.

declare @SQl nvarchar (max), @year nchar (4) = '2014'
set @sql = 'SELECT Enterprise_Unit_By_Practice_Allowed_Flag
    FROM dbo.' +@YEAR+ '_Insurance_Offer  
    WHERE  dbo.'+ @YEAR + '_Insurance_Offer.State_Code = @StateCode'
Print @sql

Since you are doing this in Stored procs, all of them should be designed with a debug variable that is used to display any SQL built. It doesn't need to run in the debug mode in prodcution, but when you have a problem (and you can't possibly forsee every variable that will ever be part of creating a dynamic sql statement and there is close to a 100% probability that you will encounter wierd bugs that you have to troubleshoot later. So you need to build in troubleshooting ability into every proc you write with dynamic SQl.

Of course to execute you need to learn to use sp_executesql.

But really read the link, share the link with your managers and coworkers and think about better ways to do this.

score -1 · Answer 3 · edited May 23 '17 at 10:34

-1

dbo.@YEAR_Insurance_Offer.State_Code [databaseowner].[table].[fieldname]

Table name as variable

According to this table names need to be static.

Honestly, I have never seen a variable table name. Further, I can't think of a good reason to do this. So this is either a logic error or a syntax error?

edited May 23 '17 at 10:34

Community

1
1

answered Jan 23 '15 at 14:40

terary

940
13
30

It's dynamic, we do replace on these so we don't have to change the stored procedures every year.... – Trevor Jan 23 '15 at 14:41

Select @variable not working

3 Answers3