create dynamic variable in procedure

[bprocopio@lifespan.org]

Please help. I'm stumped.

I need to create a dynamic variable in a procedure that will be used
to update a variable of the same name in a table. i.e. the name in
tblAnalysisScor es are WEAQScore0, WEAQScore1,WEAQ Score5, MissingWEAQ0,
MissingWEAQ1, MissingWEAQ5
The 0, 1, 5 are the @Interval. I am using a cursor to loop through the
table. Each row in the table is the data for one person at a given
timepoint (0,1,5).

This is what I have so far, but it doesn't recognize the concatenated
name as the name in the table.
What is the correct syntax to reference the variable?

In the code before this, I have already calculated @WEAQScore for the
current record of data. Now I want to update the correct variable in
tblAnalysisScor es

UPDATE tblAnalysisScor es SET
-- WEAQ Summmary/Missing
'WEAQScore'+ cast(@Interval as char(1)) = @WEAQScore
,'MissingWEAQ' + cast(@Interval as char(1)) = @MissingWEAQ
WHERE
SubID = @SubID

[Plamen Ratchev]

Re: create dynamic variable in procedure

You can avoid dynamic SQL with something like this:

UPDATE tblAnalysisScor es
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 Ratchev

http://www.SQLStudio.com

[bprocopio@lifespan.org]

Re: create dynamic variable in procedure

On May 14, 5:01 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

You can avoid dynamic SQL with something like this:
>
UPDATE tblAnalysisScor es
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.c om

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 tblAnalysisScor es? I'm just
grasping here.

[Plamen Ratchev]

Re: create dynamic variable in procedure

You cannot use variables for column names in dynamic SQL. You would have to
concatenate the SQL as a string to add the columns, and then pass the other
parameters and execute.

DECLARE @sql NVARCHAR(2000);

SET @sql = N'
UPDATE tblAnalysisScor es
SET WEAQScore' + CAST(@Interval AS CHAR(1)) + ' = @WEAQScore, ' +
' MissingWEAQ' + CAST(@Interval AS CHAR(1)) + ' = @MissingWEAQ
WHERE SubID = @SubID';

DECLARE @params NVARCHAR(100);

SET @params = N'@WEAQScore INT, @MissingWEAQ INT, @SubID INT';

EXEC sp_executesql @sql, @params, @WEAQScore, @MissingWEAQ, @SubID

Read Erland Sommarskog's article on dynamic SQL:

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

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

Re: create dynamic variable in procedure

> 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. <<

LOL! Why is it that traditional proceudral programmers think nothing
of writing hundreds of lines of procedural code then panic over using
a text editor to generate a long SQL query with a repetitive
structure?

What Ratchev gave you will touch the table once, avoid using a cursor
(figure ~10 to ~100 times faster) and be easy to maintain because of
the repetitive structure of the CASE expressions.

[Shuurai]

Re: create dynamic variable in procedure

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.  

It would be a lot more code, but that code will run orders of
magnitude faster than what you are doing now because it would only
have to to read from the table once, would not have the overhead
associated with cursor. The code also would be far easier to read an
maintain for anyone (including you) who had to deal with problems in
the future.

[bprocopio@lifespan.org]

Re: create dynamic variable in procedure

On May 15, 1:05 pm, Shuurai <Shuura...@hotm ail.comwrote:

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.  

>
It would be a lot more code, but that code will run orders of
magnitude faster than what you are doing now because it would only
have to to read from the table once, would not have the overhead
associated with cursor.  The code also would be far easier to read an
maintain for anyone (including you) who had to deal with problems in
the future.

You have all been very helpful. This is great. Thanks!

[Erland Sommarskog]

Re: create dynamic variable in procedure

(bprocopio@life span.org) writes:

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.

You are right, that would be a lot of code. That may reflect that this
is not the best table design for the task. Maybe those columns should
be rows instead. But without knowledge of the business domain, I don't
want to make a definitive statement on that point.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx