insert statement failing on conversion

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 2008 Forums

Transact-SQL (2008)

insert statement failing on conversion

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-12-02 : 09:46:48

Morning,

I have a function I wrote to update a table. I looked at the table structure and made sure the values of the declares were as same as the table I'm trying to update.


Declare @CreateDBName       varchar(100),
	@Date		    varchar(100),
	@startOrder	    int,
	@startDate	    datetime,
	@endDate	    datetime,
	@desc		    varchar(50), 
	@Sql                varchar(MAX);

SELECT @Date= CAST(YEAR(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))) AS VARCHAR(4)) + left(CONVERT(char(20),DATEADD(MONTH,+1,GETDATE()),101),2)
select @startDate = DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) 
select @endDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
select @desc = datename(month,CONVERT(char(20),DATEADD(MONTH,+1,GETDATE()),101))+' '+CAST(YEAR(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))) AS VARCHAR(4))+' Month End'
select @startOrder = convert(int,MAX(startorder)+1)
			from JB_Warehouse.dbo.DBOrder
			where StartOrder < 200

SET @CreateDBName='P'+@Date

print @CreateDBName
print @startDate
print @endDate
print @startOrder
print @desc 

SET @Sql='INSERT INTO DBOrder  
		  VALUES (' + @CreateDBName + ',
		          ' + @startOrder+ ',
		          ' + @desc+ ',
			  ' + @startDate+ ',
			  ' + @endDate+ ',
		  ''Y'')'
		  
execute (@sql)

When I run this script this is what I get:


P201101
Jan  1 2011 12:00AM
Jan 31 2011 11:59PM
45
January 2011 Month End
Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the varchar value 'INSERT INTO DBOrder  VALUES (P201101,
		' to data type int.

Even though I converted the @startOrder variable to INT it still doesn't work.

Any thoughts would be great. Thanks so much.

Laura

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-02 : 09:52:42

change this

SET @Sql='INSERT INTO DBOrder  
		  VALUES (' + @CreateDBName + ',
		          ' + @startOrder+ ',
		          ' + @desc+ ',
			  ' + @startDate+ ',
			  ' + @endDate+ ',
		  ''Y'')'
		  
execute (@sql)

to this

INSERT INTO DBOrder  
VALUES (@CreateDBName,
	@startOrder,
	@desc,
	@startDate,
	@endDate,
	'Y'
)

what happens now?

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-12-02 : 10:00:23

Magic! Thanks Russell

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 10:00:52

I'm guessing you want o do something otherthan just this as the dynamic sql isn't necessary (as russell points out).
The datatypes of the variables are not varchar so everything will be converted
try
SET @Sql='INSERT INTO DBOrder
VALUES (''' + @CreateDBName + ''','
+ convert(varchar(20),@startOrder)+ ','
+ '''' + @desc+ ''','
+ '''' + convert(varchar(8),@startDate,112) + ''','
+ '''' + convert(varchar(8),@endDate,112)+ ''','
+ '''Y'')'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources