Dynamic Stored Procedure

microsoft.public.sqlserver.programming

24 Nov 2005 3:32 PM

jcvd

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 cmd
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_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)
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)
=====================================================
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.
Leo

24 Nov 2005 3:45 PM

Jens

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.

24 Nov 2005 4:14 PM

jcvd

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_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)
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)
GO
=============================================
And VB6 gives to me the following error Must Declace @CodFondo.
Please help me I think I'll became crazy.
Thanks.
jcvd

Show quote

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

24 Nov 2005 4:40 PM

Jens

Hi,

This here can work , you have to put the variables outside the string:

(@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)'

-->
SET @query = 'INSERT INTO ' + @tablename
'
(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)
VALUES' +
' VALUES ( ' + @CodFondo + ',' + .. and so on

HTH, jens Suessmeyer.

24 Nov 2005 5:15 PM

jcvd

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

Show quote

"Jens" wrote:

> Hi,
>
>
> This here can work , you have to put the variables outside the string:
>
> (@CodFondo,@Currency,@Description,@Importo_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)'
>
>
>
> -->
> SET @query = 'INSERT INTO ' + @tablename
> '
> (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)
> VALUES' +
> ' VALUES ( ' + @CodFondo + ',' + .. and so on
>
>
> HTH, jens Suessmeyer.
>
>

24 Nov 2005 10:12 PM

Hugo Kornelis

On Thu, 24 Nov 2005 08:14:06 -0800, jcvd wrote:

(snip)

>SET @query = 'INSERT INTO ' + @tablename + '
>(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)
>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)
>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)

24 Nov 2005 10:55 PM

Erland Sommarskog

jcvd (j***@discussions.microsoft.com) writes:
Show quote

>=====================================================
> 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_01,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_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)
> 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)'

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, esq***@sommarskog.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

25 Nov 2005 2:43 PM

jcvd

Hi all,
converting all values to varchar it works fine!!!
Thanks a lot to all you.
jcvd

Show quote

"Erland Sommarskog" wrote:

> jcvd (j***@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_01,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_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)
> > 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)'
>
> 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, esq***@sommarskog.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
>