SQL Server Performance Forum – Threads Archive
Sql 2005 Quote issue
<br />Hi All,<br />I am facing a problem on the following store procedure whenever i executed, will give me error as below :<br /> "Conversion failed when converting datetime from character string"<br /><br />Please help me on the quote issues!<br /><br />Thanks<br /><br />–=====================Store Proc=============================================<br />set ANSI_NULLS ON<br />set QUOTED_IDENTIFIER ON<br />go<br /><br />ALTER PROCEDURE [dbo].[CreateTablePrismStatus]<br />@DbMonthlyname nvarchar(40),<br />@StartDatedatetime,<br />@Enddatedatetime<br />AS<br />BEGIN<br />SET NOCOUNT ON;<br />Declare @SqlString nvarchar(MAX)<br />set @SqlString = N’ USE [‘ + @DbMonthlyname + ‘]'<br />+ N’ DROP TABLE [PrismStatus]'<br />+ N’ CREATE TABLE [PrismStatus]'<br />+ N’ ([LotRefno] [char](<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NOT NULL, ‘<br />+ N’ [MachineID] [TINYINT] NOT NULL, ‘<br />+ N’ [ModelID] [TINYINT] NOT NULL, ‘<br />+ N’ [CellID] [TINYINT] NOT NULL, ‘<br />+ N’ [MachineNo] [TINYINT] NOT NULL, ‘<br />+ N’ [RetryCount] [TINYINT] NOT NULL, ‘<br />+ N’ [Judgement] [BIT] NOT NULL, ‘<br />+ N’ [InputDate] [datetime] NOT NULL) ON [PRIMARY]'<br />+ N’ CREATE CLUSTERED INDEX [IX_PrismStatus] ON'<br />+ N’ [PrismStatus]([InputDate],[LotRefno]) ON [PRIMARY]'<br />+ N’ CREATE INDEX [IX_PrismStatus1] ON'<br />+ N’ [PrismStatus]([LotRefno]) ON [PRIMARY]'<br />+ N’ ALTER TABLE [dbo].[PrismStatus] ADD'<br />+ N’ CONSTRAINT [PK_PrismStatus] PRIMARY KEY NONCLUSTERED'<br />+ N’ ([LotRefno],[MachineID],[ModelID],[MachineNo]) ON [PRIMARY]'<br />+ N’ , CONSTRAINT [CK_PrismStatus]'<br />+ N’ CHECK ([InputDate] >=’+ @StartDate <br />+ N’AND [InputDate] <=’+ @Enddate + N’)'<br />print @SqlString<br />exec sp_executesql @SqlString<br /><br />ENDexec sp_executesql @SqlString, N’@DbMonthlyname nvarchar(40), @StartDate DATETIME, @Enddate DATETIME’, @DbMonthlyname, @StartDate, @Enddate M A Srinivas
Hi Srinivas,<br /><br />Thanks for the help…works fine.. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
Adding a parameter list won’t help, as the values are already (meant to be) concatenated into the query statement. You’ll have to check @StartDate and @EndDate … IF NOT (ISDATE(@StartDate) AND ISDATE(@EndDate))
BEGIN
………. step out, or create table without the constraint ………………..
RETURN
… and for the constraint,
(1) put three single quotes around the concatenations to get one single quote in the string,
(2) format the dates with a universal format: + N’ CHECK ([InputDate] >= ”’ + CONVERT(VARCHAR(20), @StartDate, 120)
+ N”’ AND [InputDate] <= ”’ + CONVERT(VARCHAR(20), @EndDate, 120) + N”’)’
The parameter list lets you create the SP without error, but when you execute the SP you will get the same error all over again. Check my post.
Hi Adriaan, I understand your concerns… i’ve tried your method, it’s working also.. Thanks for the great help
(1) put three single quotes around the concatenations to get one single quote in the string,
(2) format the dates with a universal format: + N’ CHECK ([InputDate] >= ”’ + CONVERT(VARCHAR(20), @StartDate, 120)
+ N”’ AND [InputDate] <= ”’ + CONVERT(VARCHAR(20), @EndDate, 120) + N”’)’
]]>