On May 14, 5:01*pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> You can avoid dynamic SQL with something like this:
>
> UPDATE tblAnalysisScores
> SET WEAQScore0 = CASE WHEN @Interval = 0 THEN @WEAQScore ELSE WEAQScore0
> END,
> * * * MissingWEAQ0 = CASE WHEN @Interval = 0 THEN @MissingWEAQ ELSE
> MissingWEAQ0 END,
> * * * WEAQScore1 = CASE WHEN @Interval = 1 THEN @WEAQScore ELSE WEAQScore1
> END,
> * * * MissingWEAQ1 = CASE WHEN @Interval = 1 THEN @MissingWEAQ ELSE
> MissingWEAQ1 END,
> * * * WEAQScore5 = CASE WHEN @Interval = 5 THEN @WEAQScore ELSE WEAQScore5
> END,
> * * * MissingWEAQ5 = CASE WHEN @Interval = 5 THEN @MissingWEAQ ELSE
> MissingWEAQ5 END
> WHERE SubID = @SubID;
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks for your quick response. I appreciate that. I thought of
setting the values with CASE, but I have 12 measures. The problem is
the 12 measures have up to 12 timepoints with 5 sessions within each
timepoint and some have 3 sessions within the 5 sessions. That would
be a lot of code. That is why I was hoping to do it with dynamic
variables (Hope that is the right term. I am relatively new to SQL)
Seemed like a great idea since I have the timepoint and session and
just need to concatenate those to create the variable name that is
already in the table.
I don't know what the syntax is to create a dynamic variable. Do I
have to have a recordset open for tblAnalysisScores? I'm just
grasping here.