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
tblAnalysisScores are WEAQScore0, WEAQScore1,WEAQScore5, 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
tblAnalysisScores
UPDATE tblAnalysisScores SET
-- WEAQ Summmary/Missing
'WEAQScore'+ cast(@Interval as char(1)) = @WEAQScore
,'MissingWEAQ' + cast(@Interval as char(1)) = @MissingWEAQ
WHERE
SubID = @SubID 7 6553
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 Ratchev http://www.SQLStudio.com
On May 14, 5:01*pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
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.
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 tblAnalysisScores
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
> 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.
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.
On May 15, 1:05*pm, Shuurai <Shuura...@hotmail.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!
(bp*******@lifespan.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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: MD |
last post by:
I am trying to create a dynamic SQL statement to create a view.
I have a stored procedure, which based on the parameters passed calls
different stored procedures. Each of this sub stored procedure...
|
by: Not Me |
last post by:
Hi,
I'm sure this is a common problem.. to create a single field from a
whole column, where each row would be separated by a comma.
I can do this for a specified table, and column.. and I've...
|
by: nobody |
last post by:
hello everybody,
is there a way of creating an array with help of a function that would
accept the name of this array as a parameter and then create global
Array type variable of that name?
so...
|
by: Tim.D |
last post by:
People,
I've ventured into the wonderful world of Stored Procedures. My first
experience has been relatively successful however I am stuck on using
host variables to specifiy actualy table or...
|
by: serge |
last post by:
I can not create a stored procedure that calls another not yet created
stored procedure?
In MS SQL I get a warning that the calling procedure does not exist but the
new stored
Procedure gets...
|
by: peteh |
last post by:
Hi All;
I have what I hope is a simple environmental problem. I have some SQL
that creates a VERY simple procedure. When the create is executed from
Quest (from a Windows client) - no problem. As...
|
by: Mukesh |
last post by:
Hi
all
I m Using SQL SERVER 2005
I have a requirement to store some data in xmldatatype using stored
procedure ,
Here is example
|
by: JohnnyDeep |
last post by:
I am trying to create a store proc that contain a create index with
the cluster option and I receive
DB21034E The command was processed as an SQL statement because it was
not a
valid Command...
|
by: SQLJunkie |
last post by:
I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |