I know this has been dealt with a lot, but I would still really
appreciate help. Thanks.
I am trying to transform this table
YY--ID-Code-R1-R2-R3-R4...R40
2004-1-101--1--2-3-4
2004-2-101--2--3-4-2
....
2005-99-103-4-3-2-1
Into a table where the new columns are the count for 4-3-2-1 for every
distinct code in the first table based on year. I will get the year
from the user-end(Access). I will then create my report based on the
info in the new table. Here's what I've tried so far (only for 1st
column):
CREATE PROCEDURE comptabilisationDYN
@colonne varchar(3) '*receives R1, then R2, loop is in vba access*
AS
DECLARE @SQLStatement varchar(8000)
DECLARE @TotalNum4 int
DECLARE @TotalNum3 int
DECLARE @TotalNum2 int
DECLARE @TotalNum1 int
SELECT SQLStatement = 'SELECT COUNT(*) FROM
dbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'
EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4
OUTPUT
INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)
GO 6 2866
Patrik (pa***********@umontreal.ca) writes: CREATE PROCEDURE comptabilisationDYN @colonne varchar(3) '*receives R1, then R2, loop is in vba access* AS
DECLARE @SQLStatement varchar(8000) DECLARE @TotalNum4 int DECLARE @TotalNum3 int DECLARE @TotalNum2 int DECLARE @TotalNum1 int
SELECT SQLStatement = 'SELECT COUNT(*) FROM dbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year' EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4 OUTPUT
You need:
SELECT SQLStatement = 'SELECT @TotalNum4 = COUNT(*) FROM
dbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'
EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4
OUTPUT
You also need to add @year to the parameter list.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thank You, but i have tried your code before and again and it still
doesn't work. I call the procedure and it seems to work but it doesn't
write anything in the table. It doesn't recognize the ouput variable in
the INSERT line.
Erland Sommarskog wrote: Patrik (pa***********@umontreal.ca) writes: CREATE PROCEDURE comptabilisationDYN @colonne varchar(3) '*receives R1, then R2, loop is in vba access* AS
DECLARE @SQLStatement varchar(8000) DECLARE @TotalNum4 int DECLARE @TotalNum3 int DECLARE @TotalNum2 int DECLARE @TotalNum1 int
SELECT SQLStatement = 'SELECT COUNT(*) FROM dbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY =
@year' EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT',
@TotalNum4 OUTPUT You need:
SELECT SQLStatement = 'SELECT @TotalNum4 = COUNT(*) FROM dbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY =
@year' EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT',
@TotalNum4 OUTPUT
You also need to add @year to the parameter list.
-- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Your personal narratives and pseudo-code are useless.
Patrik (pa***********@umontreal.ca) writes: Thank You, but i have tried your code before and again and it still doesn't work. I call the procedure and it seems to work but it doesn't write anything in the table. It doesn't recognize the ouput variable in the INSERT line.
Could you post the exact code you have now. Looking back on your post,
I see now that there will be a syntax error from the dynamic SQL.
Judging from the code you posted, you should always get a row insered,
even if only a NULL value.
I assume that "seems to work" does not mean that you don't get any
error messages. But maybe you should try running the procedure from
Query Analyzer, in case you have poor error handling in your Access
code.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland Sommarskog wrote: Patrik (pa***********@umontreal.ca) writes: Thank You, but i have tried your code before and again and it still doesn't work. I call the procedure and it seems to work but it
doesn't write anything in the table. It doesn't recognize the ouput
variable in the INSERT line. Could you post the exact code you have now. Looking back on your
post, I see now that there will be a syntax error from the dynamic SQL.
Judging from the code you posted, you should always get a row
insered, even if only a NULL value.
I assume that "seems to work" does not mean that you don't get any error messages. But maybe you should try running the procedure from Query Analyzer, in case you have poor error handling in your Access code.
-- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thank You, after reading Mr.Sommarskog article more profoundly again
over the week-end, I pick-up my mistake. I wasn't using the parameter
list, now it works fine. Here's my final code.
CREATE PROCEDURE ComptabilisationDYN
@numsaisie int,
@code int,
@colonne varchar(3)
AS
DECLARE @TotalNum4 int
DECLARE @SQL1 nvarchar(1000)
DECLARE @paramlist nvarchar(1000)
SELECT @SQL1 ='SELECT @TotalNum4 = COUNT(*) FROM
dbo.Tbl_Réponses_Étudiants WHERE ' +@colonne + '=''4'''
SELECT @paramlist = '@TotalNum4 int OUTPUT'
EXEC sp_executesql @SQL1, @paramlist, @TotalNum4 OUTPUT
INSERT INTO comptabilisation(Num_Saisie, code, Total4) VALUES
(@numsaisie,@code, @TotalNum4)
GO
Erland Sommarskog wrote: Patrik (pa***********@umontreal.ca) writes: Thank You, but i have tried your code before and again and it still doesn't work. I call the procedure and it seems to work but it
doesn't write anything in the table. It doesn't recognize the ouput
variable in the INSERT line. Could you post the exact code you have now. Looking back on your
post, I see now that there will be a syntax error from the dynamic SQL.
Judging from the code you posted, you should always get a row
insered, even if only a NULL value.
I assume that "seems to work" does not mean that you don't get any error messages. But maybe you should try running the procedure from Query Analyzer, in case you have poor error handling in your Access code.
-- Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Martin Feuersteiner |
last post by:
Hi
I'm grateful for any light you can shed on this!!
I've to admit, it's an unusual design but I've multiple contact tables named
e.g. i2b_ash_contact or i2b_ted_contact.
'i2b_' and...
|
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: Tommy Lang |
last post by:
I am trying to learn to use dynamic variables.
I have pasted the code below.
Is this the proper way of using dynamic variables?
Thanks,
Tommy
...
|
by: Rodusa |
last post by:
I am trying to assign @sql variable to @total, where @sql is a sql
statement stored on the database, however what I am getting is its
string value and not its calcuation. Could anybody help?
...
|
by: starace |
last post by:
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list...
|
by: Angelos |
last post by:
Hello,
I need to dynamically specify the name of a variable. I just read that
$varA = "Cat";
echo $$varA;
OUTPUT: Cat
What I try to establish is somehow add a bit of text on my dynamic...
|
by: Camet |
last post by:
I have been trying to create a dynamic id for a number of images in a
table so that I can identify which image was clicked on later. ie I
need to set a variable to the id of each image, that is...
|
by: Rolf Mander |
last post by:
Hi,
I need to use dynamic variable names but for objects.
As you know something like that works fine:
$variable="content";
$part="able";
echo ${"vari".$part};
// gives out content
but i...
|
by: mix01 |
last post by:
Hi,
I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help.
Many thanks,
Mix01
Version of the program
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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: 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: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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,...
| |