Stored Procedure error: Conversion failed

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure error: Conversion failed

Author

Topic

dejanc
Starting Member

8 Posts

Posted - 2012-07-28 : 17:28:49

Hello,
I'm trying to execute stored procedure, but having problems. Code works fine, if I run code only as a query, starting with Create Table. Under stored procedure code is working if have only Create Table method, without Insert Into. But, when I have entire code, I'm having issues. Issues with dates 1/1/2012 & 12/31/2012. I was try to change code in three different way, but getting either wrong results, or error.
Thanks in advance for help!

1.


ALTER PROCEDURE [dbo].[ProcCalendar]
(@TableName varchar(50))
AS
declare @SQL varchar(1000)
set @SQL = 'create table ' +  @TableName + '
 (
 ID numeric (18, 0),
 CalendarDate datetime,
 MaxChair numeric (18, 0),
 MaxInside numeric (18, 0),
 MaxOutside numeric (18, 0)
   )
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = 1/1/2012
set @EndDate = 12/31/2012
while @StartDate <= @EndDate
begin
Insert into test
(
 CalendarDate
)
select
@StartDate
set @StartDate = dateadd (dd, 1, @StartDate)
end
   '
exec (@sql)

Query result:
ID CalendarDate MaxChair MaxInside MaxOutside
NULL 1900-01-01 00:00:00.000 NULL NULL NULL

2.


ALTER PROCEDURE [dbo].[ProcCalendar]
(@TableName varchar(50))
AS
declare @SQL varchar(1000)
set @SQL = 'create table ' +  @TableName + '
 (
 ID numeric (18, 0),
 CalendarDate datetime,
 MaxChair numeric (18, 0),
 MaxInside numeric (18, 0),
 MaxOutside numeric (18, 0)
   )
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = '1/1/2012'
set @EndDate = '12/31/2012'
while @StartDate <= @EndDate
begin
Insert into test
(
 CalendarDate
)
select
@StartDate
set @StartDate = dateadd (dd, 1, @StartDate)
end
   '
exec (@sql)

Query result:
Msg 102, Level 15, State 1, Procedure ProcCalendar, Line 21
Incorrect syntax near '1'.

3.


ALTER PROCEDURE [dbo].[ProcCalendar]
(@TableName varchar(50))
AS
declare @SQL varchar(1000)
set @SQL = 'create table ' +  @TableName + '
 (
 ID numeric (18, 0),
 CalendarDate datetime,
 MaxChair numeric (18, 0),
 MaxInside numeric (18, 0),
 MaxOutside numeric (18, 0)
   )
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = ' + 1/1/2012 + ' 
set @EndDate = ' + 12/31/2012 + '
while @StartDate <= @EndDate
begin
Insert into test
(
 CalendarDate
)
select
@StartDate
set @StartDate = dateadd (dd, 1, @StartDate)
end
   '
exec (@sql)

Query result:
Msg 245, Level 16, State 1, Procedure ProcCalendar, Line 5
Conversion failed when converting the varchar value 'create table test
(
ID numeric (18, 0),
CalendarDate datetime,
MaxChair numeric (18, 0),
MaxInside numeric (18, 0),
MaxOutside numeric (18, 0)
)
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = ' to data type int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 23:06:09

sounds like its date format issue

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dejanc
Starting Member

8 Posts

Posted - 2012-07-29 : 06:16:02

I have checked website, but realy don't know, as I say code actual works under query. But, under procedure not.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-29 : 07:41:08

In your second stored proc, change the two lines wherenyou are assigning the dates to use two single quotes each like this:


 declare @StartDate datetime
declare @EndDate datetime
set @StartDate = ''1/1/2012''
set @EndDate = ''12/31/2012''
while @StartDate <= @EndDate

The way you are assigning the dates in the other two stored procs is incorrect. Those will be interpreted as integer divisions, i.e., 12 divided by 31, divided by 2012, which in iteger division results in a zero. SQL server interprets zero as jan 1, 1900. That is why you see that result in your first stored proc.

if you are trying to create a calendar table there are easier, faster and safer ways. Google for "calendar table in SQL Server". For example here:
http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources