sql - SQL存储过程在转换参数时出现错误，文字没有错误

Question

嗨，我有一个我无法单独解决的问题，因为该死的调试无法在我的主机上进行。简而言之，当我尝试将表1中的一列中的datetime类型转换为varchar并将其用作存储过程的参数时，我得到了错误，但是当我用N'.. string ...'写出完全相同的东西时，一切都很好，我真的很困惑，这是：

表格1：
ID（标识符int，不为null）
讯息（nvarchar（max）
DisableComments（int）
DateTime（日期时间）
颜色（nvarchar）
用户名（nvarchar）

ID | Message | DisableComments | DateTime                | Color   | Username 
18 | Comment |       0         | 2011-12-18 14:16:27.000 | #000000 | User

这是正常工作的查询：

DECLARE @return_value int

SELECT TOP 1 [ID]
      ,[Message]
      ,[DisableComments]
      ,[DateTime]
      ,[Color]
      ,[Username] 

FROM Thoughts

EXEC    @return_value = InsertThoughtToPartition
        @ThoughtMessage = Message,
        @ThoughtDateTime = N'2012-01-03 01:22:31.000',
        @ThoughtColor = Color,
        @ThoughtUsername = Username

SELECT  'Return Value' = @return_value

这是引发错误的查询：“从字符串转换日期和/或时间时转换失败。”：

DECLARE @return_value int

SELECT TOP 1 [ID]
      ,[Message]
      ,[DisableComments]
      ,[DateTime]
      ,[Color]
      ,[Username]
      ,CONVERT(nvarchar(MAX),DateTime, 121) as Datei   

FROM Thoughts 

EXEC    @return_value = InsertThoughtToPartition
        @ThoughtMessage = Message,
        @ThoughtDateTime = Datei,
        @ThoughtColor = Color,
        @ThoughtUsername = Username

SELECT  'Return Value' = @return_value

这是我正在执行的存储过程：

USE [TagCloudDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE InsertThoughtToPartition
(
@ThoughtMessage as nvarchar(MAX),
@ThoughtDateTime as nvarchar(MAX),
@ThoughtColor as nvarchar(MAX),
@ThoughtUsername as nvarchar(MAX)
)
AS

DECLARE @MonthName nvarchar(MAX);
DECLARE @CurrentYear nvarchar(MAX);
DECLARE @InsertTableName nvarchar(MAX);
Declare @CreateTable nvarchar(MAX);
Declare @JustInsert nvarchar(MAX);

SET @CurrentYear =  CAST((SELECT DATENAME(year, CAST(@ThoughtDateTime as datetime))) as nvarchar(MAX)); 
SET @MonthName = CAST((SELECT DATENAME(month, CAST(@ThoughtDateTime  as datetime))) as nvarchar(MAX));
SET @InsertTableName = 'Thoughts_' + @MonthName + '_' + @CurrentYear;

IF OBJECT_ID(@InsertTableName) IS NOT NULL
BEGIN
SET @JustInsert = 'INSERT INTO '+ @InsertTableName + '(Message,DateTime,Color,Username)
     VALUES('''+ @ThoughtMessage+''',CONVERT(DATETIME,'''+ @ThoughtDateTime +''', 121),'''+@ThoughtColor+''','''+@ThoughtUsername+''')';

EXEC(@JustInsert);
END
ELSE
BEGIN

SET @CreateTable = '
USE [TagCloudDb] 
CREATE TABLE ['+ @InsertTableName+'](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [Color] [nvarchar](max) NOT NULL,
    [Username] [nvarchar](max) NOT NULL,
    UniqueID as CAST(ID as nvarchar) +''-''+ CONVERT(VARCHAR(8), DateTime, 112) 
) ON [PRIMARY]

INSERT INTO '+ @InsertTableName + '(Message,DateTime,Color,Username)
     VALUES('''+ @ThoughtMessage+''',CONVERT(DATETIME,'''+ @ThoughtDateTime + ''', 121),'''+@ThoughtColor+''','''+@ThoughtUsername+''')';

EXEC(@CreateTable);

END
GO

这是输入日期时间的更新版本，但仍然出现两个查询相同的错误：第一个查询工作正常，但是当我尝试从第一个表传递Datei或[DateTime]时，将数据类型nvarchar转换为datetime时出错。

USE [TagCloudDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE InsertThoughtToPartition
(
@ThoughtMessage as nvarchar(MAX),
@ThoughtDateTime as DateTime,
@ThoughtColor as nvarchar(MAX),
@ThoughtUsername as nvarchar(MAX)
)
AS

DECLARE @MonthName nvarchar(MAX);
DECLARE @CurrentYear nvarchar(MAX);
DECLARE @InsertTableName nvarchar(MAX);
Declare @CreateTable nvarchar(MAX);
Declare @JustInsert nvarchar(MAX);
DECLARE @JustInsertParamDef nvarchar(MAX);

SET @CurrentYear =  DATENAME(year, @ThoughtDateTime); 
SET @MonthName = DATENAME(month, @ThoughtDateTime);
SET @InsertTableName = 'Thoughts_' + @MonthName + '_' + @CurrentYear;

SET @JustInsert = N'INSERT INTO '+ @InsertTableName + '(Message, DateTime, Color, Username)
     VALUES(@ThoughtMessage, @ThoughtDateTime ,@ThoughtColor, @ThoughtUsername)';

SET @JustInsertParamDef = N'@InsertTableName nvarchar(MAX), @ThoughtMessage nvarchar(MAX),  @ThoughtDateTime datetime,
                            @ThoughtColor nvarchar(MAX), @ThoughtUsername nvarchar(MAX)';

IF OBJECT_ID(@InsertTableName) IS NOT NULL
BEGIN   

EXECUTE sp_executesql
        @JustInsert,
        @JustInsertParamDef,
        @InsertTableName,
        @ThoughtMessage,
        @ThoughtDateTime,
        @ThoughtColor,
        @ThoughtUsername;

END
ELSE
BEGIN

SET @CreateTable = 'USE [TagCloudDb] 
                    CREATE TABLE ['+@InsertTableName+'](
                        [ID] [int] IDENTITY(1,1) NOT NULL,
                        [Message] [nvarchar](max) NOT NULL,
                        [DateTime] [datetime] NOT NULL,
                        [Color] [nvarchar](max) NOT NULL,
                        [Username] [nvarchar](max) NOT NULL,
                        [UniqueID] as CAST(ID as nvarchar) + ''-'' + CONVERT(VARCHAR(8), DateTime, 112) 
                    ) ON [PRIMARY]'


EXEC(@CreateTable);

EXECUTE sp_executesql
        @JustInsert,
        @JustInsertParamDef,
        @InsertTableName = @InsertTableName,
        @ThoughtMessage = @ThoughtMessage,
        @ThoughtDateTime = @ThoughtDateTime,
        @ThoughtColor = @ThoughtColor,
        @ThoughtUsername = @ThoughtUsername;

END

Answer 1

程序很好，执行不起作用

运行示例后，检查表的内容。

ID |消息|日期时间|颜色|用户名|唯一ID

1 |邮件| 2012-01-03 01：22：31.000 |颜色|用户名| 1-20120103

您没有将所选的值传递给过程，因此在尝试将“ Dateti”解析为DATETIME类型时失败

您绝对应该清理数据类型和字符串大小，这应该使此类事情更容易捕获