Dynamic Stored Procedure

Hi all,
I've created a stored procedure with parameters to create 40 tables with the
same structure.From VB6 i pass the name of the table to be created from a cm
d
command.
========================================
=============
CREATE PROC dbo.CreaTabelleFondi
@.tablename VARCHAR(6)
AS
DECLARE @.query NVARCHAR(1000)
SET @.query = 'CREATE TABLE ' + @.tablename + ' ( CodFondo varchar(6),
Currency varchar(3), Descrizione varchar(255), Importo_01 float,Importo_02
float,Importo_03 float,Importo_04 float,Importo_05 float,Importo_06
float,Importo_07 float,Importo_08 float,Importo_09 float,Importo_10
float,Importo_11 float,Importo_12 float,Importo_13 float,Importo_14
float,Importo_15 float)'
EXEC(@.query)
GO
========================================
=============
This work properly.Then I've created another stored procedure to populate
the tables with data from 40 csv files.From VB i pass the name of the tables
.
========================================
=============
use CashFlow
go
CREATE PROC dbo.InsertTabelleFondi
@.tablename varchar(6),
@.CodFondo varchar(6),
@.Currency varchar(3),
@.Description varchar(255),
@.Importo_01 decimal,
@.Importo_02 decimal,
@.Importo_03 decimal,
@.Importo_04 decimal,
@.Importo_05 decimal,
@.Importo_06 decimal,
@.Importo_07 decimal,
@.Importo_08 decimal,
@.Importo_09 decimal,
@.Importo_10 decimal,
@.Importo_11 decimal,
@.Importo_12 decimal,
@.Importo_13 decimal,
@.Importo_14 decimal,
@.Importo_15 decimal
AS
DECLARE @.query NVARCHAR(1000)
SET @.query = 'INSERT INTO ' + @.tablename + '
(CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,I
mporto_05,Importo_06,Importo_07,Importo_
08,Importo_09,Importo_10,Importo_11,
Importo_12,Importo_13,Importo_14,Importo
_15)
VALUES (' + @.CodFondo + ',' + @.Currency + ',' + @.Description + ',' +
@.Importo_01 + ',' + @.Importo_02 + ',' + @.Importo_03 + ',' + @.Importo_04 + ',
'
+ @.Importo_05 + ',' + @.Importo_06 + ',' + @.Importo_07 + ',' + @.Importo_08 +
',' + @.Importo_09 + ',' + @.Importo_10 + ',' + @.Importo_11 + ',' + @.Importo_1
2
+ ',' + @.Importo_13 + ',' + @.Importo_14 + ',' + @.Importo_15 + ')'
EXEC(@.query)
========================================
=============
This stored procedure doesn't work because i can't use the INSERT INTO
statement with dynamic stored procedures.
Could please someone help me?
Thanks a lot in advance.
LeoIn general it works, but id you try to add a numeric value to a text,
this throws up an error, so you have to either declare the variables as
some kind of character type or use an explicit cast within very
variable.
1. @.Importo_01 varchar(100)
2. CAST(@.Importo_05 as varchar(100)
HTH, jens Suessmeyer.|||Hi Jens,
I'm new in SQL Server but I think that the problem is that i try to
parametrize the @.tablename.
I've change the two stored proc in this way
=============
CREATE PROC dbo.CreaTabelleFondi
@.tablename VARCHAR(6)
AS
DECLARE @.query NVARCHAR(1000)
SET @.query = 'CREATE TABLE ' + @.tablename + ' ( CodFondo varchar(6),
Currency varchar(3), Descrizione varchar(255), Importo_01
varchar(10),Importo_02 varchar(10),Importo_03 varchar(10),Importo_04
varchar(10),Importo_05 varchar(10),Importo_06 varchar(10),Importo_07
varchar(10),Importo_08 varchar(10),Importo_09 varchar(10),Importo_10
varchar(10),Importo_11 varchar(10),Importo_12 varchar(10),Importo_13
varchar(10),Importo_14 varchar(10),Importo_15 varchar(10))'
EXEC(@.query)
GO
===============
CREATE PROC dbo.InsertTabelleFondi
@.tablename varchar(6),
@.CodFondo varchar(6),
@.Currency varchar(3),
@.Descrizione varchar(255),
@.Importo_01 varchar(10),
@.Importo_02 varchar(10),
@.Importo_03 varchar(10),
@.Importo_04 varchar(10),
@.Importo_05 varchar(10),
@.Importo_06 varchar(10),
@.Importo_07 varchar(10),
@.Importo_08 varchar(10),
@.Importo_09 varchar(10),
@.Importo_10 varchar(10),
@.Importo_11 varchar(10),
@.Importo_12 varchar(10),
@.Importo_13 varchar(10),
@.Importo_14 varchar(10),
@.Importo_15 varchar(10)
AS
DECLARE @.query NVARCHAR(1000)
SET @.query = 'INSERT INTO ' + @.tablename + '
(CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,I
mporto_05,Importo_06,Importo_07,Importo_
08,Importo_09,Importo_10,Importo_11,
Importo_12,Importo_13,Importo_14,Importo
_15)
VALUES
(@.CodFondo,@.Currency,@.Description,@.Impor
to_01,@.Importo_02,@.Importo_03,@.Impor
to_04,@.Importo_05,@.Importo_06,@.Importo_0
7,@.Importo_08,@.Importo_09,@.Importo_1
0,@.Importo_11,@.Importo_12,@.Importo_13,@.I
mporto_14,@.Importo_15)'
EXEC(@.query)
GO
========================================
=====
And VB6 gives to me the following error Must Declace @.CodFondo.
Please help me I think I'll became crazy.
Thanks.
jcvd
"Jens" wrote:

> In general it works, but id you try to add a numeric value to a text,
> this throws up an error, so you have to either declare the variables as
> some kind of character type or use an explicit cast within very
> variable.
> 1. @.Importo_01 varchar(100)
> 2. CAST(@.Importo_05 as varchar(100)
> HTH, jens Suessmeyer.
>|||Hi,
This here can work , you have to put the variables outside the string:
(@.CodFondo,@.Currency,@.Description,@.Impor
to_01,@.Importo_02,@.Importo_03,@.Impo=
=ADrto_04,@.Importo_05,@.Importo_06,@.Impor
to_07,@.Importo_08,@.Importo_09,@.Impo=
rto=AD_10,@.Importo_11,@.Importo_12,@.Impor
to_13,@.Importo_14,@.Importo_15)'
-->
SET @.query =3D 'INSERT INTO ' + @.tablename
'
(CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,=
=ADImporto_05,Importo_06,Importo_07,Impo
rto_08,Importo_09,Importo_10,Import=
o_11,Importo_12,Importo_13,Importo_14,Im
porto_15)
VALUES' +
' VALUES ( ' + @.CodFondo + ',' + .. and so on
HTH, jens Suessmeyer.|||Hi Jens,
may be you are right.
Now I go home.
Tomorrow I'll try in this way and I let you know.
Thanks a lot for your help.
jcvd
"Jens" wrote:

> Hi,
>
> This here can work , you have to put the variables outside the string:
> (@.CodFondo,@.Currency,@.Description,@.Impor
to_01,@.Importo_02,@.Importo_03,@.Imp
o_rto_04,@.Importo_05,@.Importo_06,@.Impor
to_07,@.Importo_08,@.Importo_09,@.Impor
to__10,@.Importo_11,@.Importo_12,@.Importo
_13,@.Importo_14,@.Importo_15)'
>
> -->
> SET @.query = 'INSERT INTO ' + @.tablename
> '
> (CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04
,_Importo_05,Importo_06,Importo_07,Impo
rto_08,Importo_09,Importo_10,Importo
_11,Importo_12,Importo_13,Importo_14,Imp
orto_15)
> VALUES' +
> ' VALUES ( ' + @.CodFondo + ',' + .. and so on
>
> HTH, jens Suessmeyer.
>|||On Thu, 24 Nov 2005 08:14:06 -0800, jcvd wrote:
(snip)
>SET @.query = 'INSERT INTO ' + @.tablename + '
> (CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,
Importo_05,Importo_06,Importo_07,Importo
_08,Importo_09,Importo_10,Importo_11
,Importo_12,Importo_13,Importo_14,Import
o_15)
>VALUES
> (@.CodFondo,@.Currency,@.Description,@.Impor
to_01,@.Importo_02,@.Importo_03,@.Impo
rto_04,@.Importo_05,@.Importo_06,@.Importo_
07,@.Importo_08,@.Importo_09,@.Importo_
10,@.Importo_11,@.Importo_12,@.Importo_13,@.
Importo_14,@.Importo_15)'
>EXEC(@.query)
>GO
> ========================================
=====
>And VB6 gives to me the following error Must Declace @.CodFondo.
>Please help me I think I'll became crazy.
Hi jcvd,
You'll have to use sp_executesql for this.
(Or Jens' suggestion - but then you'll have to double all single quotes,
and be very careful how to handle datetype conversions).
This site by Erland Sommarskog is a must read if you want or need to use
dynamic sql: http://www.sommarskog.se/dynamic_sql.html.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||jcvd (jcvd@.discussions.microsoft.com) writes:
> ========================================
=============
> CREATE PROC dbo.CreaTabelleFondi
> @.tablename VARCHAR(6)
> AS
> DECLARE @.query NVARCHAR(1000)
> SET @.query = 'CREATE TABLE ' + @.tablename + ' ( CodFondo varchar(6),
> Currency varchar(3), Descrizione varchar(255), Importo_01 float,Importo_02
> float,Importo_03 float,Importo_04 float,Importo_05 float,Importo_06
> float,Importo_07 float,Importo_08 float,Importo_09 float,Importo_10
> float,Importo_11 float,Importo_12 float,Importo_13 float,Importo_14
> float,Importo_15 float)'
> EXEC(@.query)
> GO
> ========================================
=============
The normal definition for a table like this would be to have two tables.
One for CodFondo, Currency and Description, and one with CodFondo, Number
and Importo. This latter table would have 15 rows.
By the way, your table lacks a primary key. I guess this is CodFondo,
or possible CodFondo + Currency.
And of course, even more normal, the primary key would also include
whatever information that is in the tablename. Dynamically created tables
is not really how DB engines are intended to be used.

> @.Importo_01 decimal,
> @.Importo_02 decimal,
> @.Importo_03 decimal,
> @.Importo_04 decimal,
But above you Importo are float?

> DECLARE @.query NVARCHAR(1000)
> SET @.query = 'INSERT INTO ' + @.tablename + '
>
(CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,I
mporto_05,Importo_06,Importo_07,Importo_
08,Importo_09,Importo_10,Importo_11,
Importo_12,Importo_13,Importo_14,Importo
_15)
> VALUES (' + @.CodFondo + ',' + @.Currency + ',' + @.Description + ',' +
> @.Importo_01 + ',' + @.Importo_02 + ',' + @.Importo_03 + ',' + @.Importo_04 +
','
> + @.Importo_05 + ',' + @.Importo_06 + ',' + @.Importo_07 + ',' + @.Importo_08
+
> ',' + @.Importo_09 + ',' + @.Importo_10 + ',' + @.Importo_11 + ',' +
@.Importo_12
> + ',' + @.Importo_13 + ',' + @.Importo_14 + ',' + @.Importo_15 + ')'
> EXEC(@.query)
As Jens said, you run into to conversion problems, so you need to stringfy
all values. Passing the parameters as varchar is a quite good idea, as
they come from a CSV file.

> SET @.query = 'INSERT INTO ' + @.tablename + '
> (CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04,
> Importo_05,Importo_06,Importo_07,Importo
_08,Importo_09,Importo_10,
> Importo_11,Importo_12,Importo_13,Importo
_14,Importo_15)
> VALUES
> (@.CodFondo,@.Currency,@.Description,@.Impor
to_01,@.Importo_02,@.Importo_03,
> @.Importo_04,@.Importo_05,@.Importo_06,@.Imp
orto_07,@.Importo_08,@.Importo_09,
> @.Importo_10,@.Importo_11,@.Importo_12,@.Imp
orto_13,@.Importo_14,@.Importo_15)'
This does not work, because you try to refer to the variables inside
the dynamic SQL, which constitutes its own scope, and do not see the
local variables of the surrounding procedure. With varchar for your
variables, your original syntax should work fine.
Of course, using sp_executesql as Hugo suggested is also an option. In
this case you would pass the parameters as float, and the use the later
syntax:
sp_executesql @.query, '@.Importo_01 float, ...',
@.Importo_01, @.Importo_02, ...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi all,
converting all values to varchar it works fine!!!
Thanks a lot to all you.
jcvd
"Erland Sommarskog" wrote:

> jcvd (jcvd@.discussions.microsoft.com) writes:
> The normal definition for a table like this would be to have two tables.
> One for CodFondo, Currency and Description, and one with CodFondo, Number
> and Importo. This latter table would have 15 rows.
> By the way, your table lacks a primary key. I guess this is CodFondo,
> or possible CodFondo + Currency.
> And of course, even more normal, the primary key would also include
> whatever information that is in the tablename. Dynamically created tables
> is not really how DB engines are intended to be used.
>
> But above you Importo are float?
>
> (CodFondo,Currency,Description,Importo_0
1,Importo_02,Importo_03,Importo_04
,I
> mporto_05,Importo_06,Importo_07,Importo_
08,Importo_09,Importo_10,Importo_1
1,
> Importo_12,Importo_13,Importo_14,Importo
_15)
> ','
> +
> @.Importo_12
> As Jens said, you run into to conversion problems, so you need to stringfy
> all values. Passing the parameters as varchar is a quite good idea, as
> they come from a CSV file.
>
> This does not work, because you try to refer to the variables inside
> the dynamic SQL, which constitutes its own scope, and do not see the
> local variables of the surrounding procedure. With varchar for your
> variables, your original syntax should work fine.
> Of course, using sp_executesql as Hugo suggested is also an option. In
> this case you would pass the parameters as float, and the use the later
> syntax:
> sp_executesql @.query, '@.Importo_01 float, ...',
> @.Importo_01, @.Importo_02, ...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>