Inster into @table in procs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inster into @table in procs

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 09:29:15

I am trying to creat a proc that will inster the same type of data into differant tables, which table the data is inserted into will be passed to the proc from a perl script. What I have now ise this code for the proc:

create proc insert_images (@table char(25), @image varchar(25), @subset varchar(13), @height numeric(3,0), @width numeric(3,0), @alt varchar(50)) as
insert into @table (image, subset, height, width, alt) values (@image, @$subset, @height, @width, @alt)

But if I use query analizer with that code to check the syntac I get the error:

Server: Msg 137, Level 15, State 2, Procedure insert_images, Line 2
Must declare the variable '@table'.

Am I not allowed to have a varible as what table to insert data into? If so, does anyone have a suggestion on what to do?

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 09:31:54

You need to create the table variable

Try this


create proc insert_images (@table char(25), @image varchar(25), @subset varchar(13), @height numeric(3,0), @width numeric(3,0), @alt varchar(50)) as

Declare @table table(col1 type,...colN type)

insert into @table (image, subset, height, width, alt) values (@image, @$subset, @height, @width, @alt)

Madhivanan

Failing to plan is Planning to fail

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 10:35:50

I added the declare @table line with the approreate info and now get the error that @table has already been declared. My code now looks like this:

create proc insert_images (@table char(25), @image varchar(25), @subset varchar(13), @height numeric(3,0), @width numeric(3,0), @alt varchar(50)) as
declare @table table(image varchar(25), subset varchar(13), height numeric(3,0), width numeric(3,0), alt varchar(50))
insert into @table (image, subset, height, width, alt) values (@image, @$subset, @height, @width, @alt)

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 10:42:58

emmm... heloo...
you now have 2 variables with same name and different datatypes. that's not allowed.

what do you want to do??
insert the data into the table you specify??
if so, you need to use dynamic sql:
exec('insert into ' + @table + ' (image, subset, height, width, alt) values (' + @image + ',' + @subset + ',' + @height + ',' + @width + ',' + @alt + ')')

although i don't know why would you want to do that....
i could be wrong of course

EDIT: just added a )

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-05 : 10:44:10

Use different name than @table, say
declare @myTable table(image varchar(25), subset varchar(13), height numeric(3,0), width numeric(3,0), alt varchar(50))

Madhivanan

Failing to plan is Planning to fail

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 11:25:11

quote:
Originally posted by spirit1

emmm... heloo...
you now have 2 variables with same name and different datatypes. that's not allowed.

what do you want to do??
insert the data into the table you specify??
if so, you need to use dynamic sql:
exec('insert into ' + @table + '(image, subset, height, width, alt) values (' + @image + ',' + @subset + ',' + @height + ',' + @width + ',' + @alt + ')'

although i don't know why would you want to do that....
i could be wrong of course

Go with the flow & have fun! Else fight the flow

If you read my first post you would have read that I am trying to creat a proc the insterts data into differant tables, and then you would have noticed that the code I originaly used is the same one you used (well not the same but if you strip out the extra stuff you added it is)

quote:
Originally posted by madhivanan

Use different name than @table, say
declare @myTable table(image varchar(25), subset varchar(13), height numeric(3,0), width numeric(3,0), alt varchar(50))

Madhivanan

Failing to plan is Planning to fail

That too does not work, it returns me to the original problem that @table must be declared.

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 11:29:32

and if you tried what i typed and understood it you'd see that it is what you want.

Go with the flow & have fun! Else fight the flow

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 11:32:22

Hurm, what a shock. SAME ERROR.

You know what, fuck it. I make the program do the dyanmic work and not SQL.

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 11:40:29

come on... that's not the way


create proc insert_images (@table char(25), @image varchar(25), @subset varchar(13), @height numeric(3,1), @width numeric(3,1), @alt varchar(50)) 
as
select 'insert into ' + @table + ' (image, subset, height, width, alt) values (''' + @image + ''',''' + @subset + ''',' + convert(varchar(20), @height) + ',' + convert(varchar(20), @width) + ',' + @alt + ')'
--exec 'insert into ' + @table + ' (image, subset, height, width, alt) values (''' + @image + ''',''' + @subset + ''',' + convert(varchar(20), @height) + ',' + convert(varchar(20), @width) + ',' + @alt + ')'
go
exec insert_images 'table1', 'image1', 'subset1', 12.3, 15.4, '3'
go 
drop proc insert_images

this is what is returned/executed in a sproc
insert into table1 (image, subset, height, width, alt) values ('image1','subset1',12.3,15.4,3)

i do have to say that you approching this the wrong way.
this will only work if all your tables have the same structure.
it's usually done 1 insert sproc per table.

Go with the flow & have fun! Else fight the flow

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 12:12:04

How am I approcing this the wrong way? Making a dynamic insert link or have the perl program do the dynamic work instead of waisting time getting a proc to do it?

Either way of doing this it is not working, the curser will not move allowing more then one entry at a time, I am saying fuck this automation and do it by hand.

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 12:28:29

it's your app...

i think both ways are wrong.
as i said one insert sproc per table should do the trick:

create proc insert_images_to_MyTableName (@image varchar(25), @subset varchar(13), @height numeric(3,1), @width numeric(3,1), @alt varchar(50))
as
insert into MyTableName(image, subset, height, width, alt) values (@image, @$subset, @height, @width, @alt)

Go with the flow & have fun! Else fight the flow

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 12:49:23

Why is having the dynamic proc the wrong thing to do? The tables will always have the same colums

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-05 : 12:55:22

Well, if the tables will have the same columns, why not have one single table to insert the data into? I don't see any particular advantage in having multiple tables store the same (in this case, EXACT) type of data. And once you do that, you find you don't need table variables, dynamic SQL, or anything more than the most basic and essential code.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 12:56:22

i said that if your tables have same structure then dynamic way will work, otherwise no.

Go with the flow & have fun! Else fight the flow

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2005-09-05 : 15:12:04

quote:
Originally posted by robvolk

Well, if the tables will have the same columns, why not have one single table to insert the data into? I don't see any particular advantage in having multiple tables store the same (in this case, EXACT) type of data. And once you do that, you find you don't need table variables, dynamic SQL, or anything more than the most basic and essential code.

What the tables will be doing is storing the names (and other data) of all images for differant games. Since each game will at least have 2 sub groups in common (Misc Images, and Group Image) and possibly more, plus some games will have thousands of images I figured a seperate table for each game would be best, performance wise.

--
If I get used to enving others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources