dynamic SQL variable with Output ?

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_Rponses_tudiants WHERE' + @.colonne + '=4 AND YY = @.year'
EXEC sp_executesql @.SQLStatement, N'@.TotalNum4 int OUTPUT', @.TotalNum4
OUTPUT

INSERT INTO Comptabilisation(Total4) VALUES (@.TotalNum4)
GOPatrik (patrik.maheux@.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_Rponses_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_Rponses_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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../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 (patrik.maheux@.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_Rponses_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_Rponses_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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../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 (patrik.maheux@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Patrik (patrik.maheux@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../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_Rponses_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 (patrik.maheux@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp