dynamic INSERT - 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

SQL Server Development (2000)

dynamic INSERT

Author

Topic

nabeel
Starting Member

15 Posts

Posted - 2004-05-10 : 11:02:57

hi...

i'm not sure if i can do this or not, i hope some can tell me. i'm trying to pass in the VALUES clause into a stored procedure and have a stored procedure use that to do an INSERT, like so:

(would be passed in) @values = 'fName', 'lName', 'whatever'

INSERT INTO table VALUES @values

is that possible?

thanks...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 12:26:12

You can using dynamic SQL; however, it's much better for performance and security to just do it the normal way.

CREATE PROCEDURE table_insert

@fName VARCHAR(55),
@lName VARCHAR(55),
@whatever VARCHAR(55)

INSERT table(
fName,
lName,
whatever)

SELECT
@fName,
@lName,
@whatever

GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nabeel
Starting Member

15 Posts

Posted - 2004-05-10 : 13:22:25

yeah... i'm not sure what to do because i have an XML document that has values that can be used quite easily if i did it using dynamic SQL:

<customer>'fName', 'lName', 'address1'</customer>

INSERT INTO table VALUES @values

however, i can't even save my stored proc. it says there's incorrect syntax. i even tried listing the columns, but then it complains that the number of columns don't match values...

any ideas?

thanks for your help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 13:26:30

Please post the stored procedure.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 13:32:26

You will have to do everything as dynamic SQL.

Here is an example. Again, this is REALLY NOT RECOMMENDED.

SET QUOTED_IDENTIFIER OFF
DECLARE
@values NVARCHAR(255),
@sql NVARCHAR(2500)

SELECT @values = "'fName', 'lName', 'whatever'"
PRINT @values

SELECT @sql = 'INSERT table(column_list) VALUES(' + @values + ')'
PRINT @sql

EXEC (@sql)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nabeel
Starting Member

15 Posts

Posted - 2004-05-10 : 13:39:00

actually, i haven't written it yet, but i tried that syntax and it wouldn't work. so i decided to ask if it's even possible... i wasn't sure because if i couldn't even save the stored procedure without it complaining about the following line:

INSERT INTO table VALUES @values

i've also tried setting @values to something else (see below) just to see if i can get the procedure to save... didn't work.

SET @values = '''nothing'', ''whatever'''
INSERT INTO tables VALUES @info

nabeel
Starting Member

15 Posts

Posted - 2004-05-10 : 13:41:07

oh, i see... i have to construct the entire statement as dynamic... hmm, not pretty.

may i ask why that is bad compared to passing all the parameters in? i'm sure you're right, i'm just wondering for my own knowledge...

thanks...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 13:43:02

??? Look at what I did. The reason yours wouldn't work is because you have the quotes around the data already. This is trying to split up your string. I had to add the SET QUOTED_IDENTIFIER OFF to allow me to wrap your entire line. You really need to handle all this at the component level. Create a component to parse this out and execute the stored procedure properly so you don't have to do this parsing nonsense.

If you don't want to do that, look at OPENXML in Books Online.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 13:43:54

It's bad because it's susceptible to injection, which is a big security risk. It's also bad on performance because it forces recompiles and doesn't run as efficiently.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 13:44:18

It's bad due to performance and security. Security because you have to grant explicit permissions on the objects rather than just execute on stored procedures. Performance because you don't get the benefit of it being pre-compiled.

Tara

nabeel
Starting Member

15 Posts

Posted - 2004-05-10 : 13:45:24

yes... good points. i'm thinking of changing the format of the XML document and using OPENXML instead.

thanks again for your assistance...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources