将varchar值'LAST1，FIRST1'转换为数据类型int时转换失败

I have a TSQL query that is doing 3-4 different things (my 1st attempt at this type of query). I encounter this error on the first record:

我有一个TSQL查询,它做了3-4个不同的事情(我第一次尝试这种类型的查询)。我在第一条记录上遇到此错误:

Error: "Conversion failed when converting the varchar value 'LAST1, FIRST1' to data type int."

错误:“将varchar值'LAST1,FIRST1'转换为数据类型int时转换失败。”

The value referenced in the error is a concatenation of first and last name from a view.
From reading other posts I have deduced that the comma is the issue but how do I get around it? Do I use substring to strip the comma out from the view before I use it or do I re-write the view or is there a CAST or parse function I can use?

错误中引用的值是视图中名字和姓氏的串联。从阅读其他帖子我已经推断出逗号是问题,但我该如何解决它?在使用之前,我是否使用子字符串从视图中删除逗号,或者我是否重新编写视图,或者是否有可以使用的CAST或解析函数?

Here's overview of what the query should do:

以下是查询应该执行的操作的概述:

1. Select and loop through records in a view

选择并循环浏览视图中的记录

2. If column, "daysUntil" (int) = 30, INSERT new record into another database via stored procedure

如果列“daysUntil”(int)= 30,则通过存储过程将新记录INSERT到另一个数据库中

3. Return scope_identity to output parameter @PK_ID to use in email body

将scope_identity返回到输出参数@PK_ID以在电子邮件正文中使用

4. Send email using new @PK_ID in body of email

在电子邮件正文中使用新的@PK_ID发送电子邮件

BTW I already have a C# console app that can handle all of the above but I want to move this functionality to SQL Server.
Here's the SQL:

BTW我已经有一个C#控制台应用程序,可以处理上述所有,但我想将此功能移动到SQL Server。这是SQL:

 DECLARE @MailTo varchar (1024)
 DECLARE @MailBody varchar (1024)
 DECLARE @MailSubject varchar (1024)
 DECLARE @MailFlag int

 DECLARE @Bcc varchar (256)
 SET @Bcc = '[e-mail];[e-mail] 


DECLARE @MailFrom varchar (256)
SET @MailFrom = '[e-mail]'

DECLARE @Notification30Body varchar (512)
DECLARE @Notification30Subject varchar (512)

DECLARE @NotificationAllBody varchar (512)
SET @NotificationAllBody = 'REQUIREMENTS: requirements here'  

--Declare variables to hold the data which we get after looping each record
 DECLARE 
 @iEmpName VARCHAR(102),
 @iEmpID INT, 
 @iEmail1 VARCHAR(24), 
 @iRvwDate smalldatetime, 
 @idaysUntil INT 


DECLARE Empid_cur CURSOR FOR
select  EmpName, EmpID, RvwDate, Email1, daysUntil from ABC.dbo.vwEmpDataTEST  

--Flag the start of loop/curser
SET @MailFlag = 0 

OPEN Empid_cur

FETCH NEXT FROM Empid_cur 
INTO @iEmpID, @iEmpID, @RvwDate, @iEmail1, @idaysUntil

WHILE @@FETCH_STATUS = 0 
BEGIN

    IF (@idaysUntil = 30)   
        BEGIN
        DECLARE @PK_ID INT;
        EXEC XYZ.dbo.InsertEmpDataTEST @iEmpID, @PK_ID OUTPUT
        SELECT @PK_ID

        --send 30 day email here
        SET @MailTo = @iEmail1
        SET @MailBody = @Notification30Body
        SET @MailSubject = @Notification30Subject
        SET @Notification30Body = 'This Record is for ' +  @iEmpName + ' is due in 30 days. Please open the fake URL to the new Record for ' +  @iEmpName + ':' + CHAR(10) + ' http://server/Record/template.aspx?ID=' + @PK_ID  
        SET @Notification30Subject = 'Record for ' +  @iEmpName  
        SET @MailFlag = 1 --email ready
        END

        IF (@MailFlag = 1)
        BEGIN
        --send email
            EXEC msdb..sp_send_dbmail
            @profile_name ='MailUser', 
            @recipients = @MailTo,
            @blind_copy_recipients = @Bcc,
            @subject = @MailSubject,
            @body = @MailBody
        END 

FETCH NEXT FROM Empid_cur 
INTO @iEmpID, @iEmpID, @RvwDate, @iEmail1, @idaysUntil
END

CLOSE Empid_cur 
DEALLOCATE Empid_cur 

1 个解决方案

#1

You have INTO @iEmpID, @iEmpID, ... with the int variable repeated twice, change to INTO @iEmpName, @iEmpID,... so the columns and variables (and their types) are all aligned.

您有INTO @iEmpID,@ iEmpID,...,int变量重复两次,更改为INTO @iEmpName,@ iEmpID,...所以列和变量(及其类型)都是对齐的。