Dynamic SQL - 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)

Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-01 : 15:07:48

Robin writes "I want to write a stored procedure which inserts data in a variant table.
The code I wrote doesn't work! What is the correct code?

version : SQL 2000 SP2

----------------
DECLARE @TableName nvarchar(20)
DECLARE @DateTime datetime
DECLARE @Description nvarchar(50)
DECLARE @ProcessValue sql_variant
DECLARE @MaxID int
DECLARE @sql nvarchar(100)

----------------
Test data
----------------
SET @MaxID = 30
SET @TableName = 'tblLogging001'
SET @DateTime = '2002-12-19 14:25:00'
SET @Description = 'Test'
SET @ProcessValue = 40
----------------

EXEC
('INSERT INTO ' + @TableName + ' VALUES ("' + @MaxID + '","' + @DateTime + '","' + @Description + '","' + @ProcessValue + '")')
-----------------
This gives errors!
-----------------
set @sql = 'INSERT INTO ' + @TableName + ' VALUES (' + convert (varchar(10),@MaxID) + ',' + convert (varchar(20),@DateTime) + ',' + @Description + ',' + convert (varchar(10),@ProcessValue) + ')'
exec (@sql)
-----------------
This statement is OK but as you see the format gives me problems now (DateTime).
-----------------

Thanks."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-01 : 15:36:59

Try

a) printing the string you are going to EXEC to see how it looks

b) looking at BOL for info on the CONVERT function; you can convert a datetime to a varchar() in many different formats.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-01 : 19:42:07

Also use sp_executesql instead of exec.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-01 : 23:24:23

try
select @sql = 'INSERT INTO ' + @TableName + ' select ' + convert (varchar(10),@MaxID) + ',''' + convert (varchar(24),@DateTime, 113) + ''',''' + @Description + ''',' + convert (varchar(10),@ProcessValue)
select @sql
exec (@sql)

Also name the columns you are inserting into.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 01/01/2003 23:25:29

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources