sql - 将数据类型 varchar 转换为浮点数时出错，但在 Management Studio 中没有

Question

我是编程新手。我的任务是查找并修复标题中所述的错误。我有以下存储过程，其中表单提供表格。SP 在管理工作室中通过解析和执行，但是当我在表单中运行测试输入时，将数据类型 varchar 转换为日志中的浮点数时出错，并且表单上输入的值不会最终出现在 db 表中。我找不到 varchar 试图转换为 float 的位置。我错过了什么吗？

Code:
/* GET ARGUMENTS PASSED FROM FORM */
@MYID AS NVARCHAR(4) = NULL,
@ZIP AS NVARCHAR(10) = NULL,
@NAME AS NVARCHAR(300) = NULL,
@EMAIL AS NVARCHAR(300) = NULL,
@NUMBER1 AS NVARCHAR(15) = NULL,
@NUMBER2 AS NVARCHAR(15) = NULL,
@OUT_MESSAGE AS NVARCHAR(MAX) = NULL,
@SOURCE AS NVARCHAR(300) = NULL,
@REFR AS NVARCHAR(300) = NULL,
@SERVE AS NVARCHAR(300) = NULL,
@QT AS NVARCHAR(4) = NULL

AS
SET NOCOUNT ON;

SET @MYID = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @ZIP = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @NAME = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @EMAIL = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @NUMBER1 = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @NUMBER2 = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @OUT_MESSAGE = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @SOURCE = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @REFR = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @SERVE = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')
SET @QT = REPLACE(REPLACE(REPLACE(@ZIP,'|',','),'_',' '),';','-')


DECLARE @TODAY AS DATETIME = GETDATE()
DECLARE @WORK_ZIP AS NVARCHAR(10) = NULL
DECLARE @MESSAGE AS NVARCHAR(MAX)
DECLARE @INPOINT AS GEOGRAPHY = NULL

SET @MYID = (SELECT TOP 1 ISNULL(myid,'NONE') FROM VIEW_ZIPS WHERE EmailAddress IS NOT NULL AND myid LIKE(@MYID,4))
IF (@MYID IS NULL)
BEGIN
SET @MYID = 'NONE'
END

IF @MYID NOT LIKE 'NONE'
BEGIN
SET @SERVE = 'BRANCH1'
END

SET @OUT_MESSAGE = 'Something went wrong'
SET @OUT_MESSAGE = 'If problem continues, contact support'

DECLARE @CURR_REC AS INT

SET @CURR_REC = (SELECT TOP 1 ISNULL(REC_ID,0) FROM TBL_CONTACT WHERE (zip LIKE @ZIP AND name LIKE @NAME AND email LIKE @EMAIL AND number1 LIKE @NUMBER1 AND message LIKE @OUT_MESSAGE AND source LIKE @SOURCE AND refr LIKE @REFR AND serve LIKE @SERVE AND date LIKE @TODAY))
SET @CURR_REC = ISNULL(@CURR_REC,0)

IF (ISNULL(@NAME,'TEST') LIKE 'TEST' OR ISNULL(@NAME,'NONE') LIKE 'NONE'
BEGIN
SET @CURR_REC = 1
END

IF (ISNULL(@QT,'UNKNOWN') NOT LIKE 'UNKNOWN' AND @SOURCE NOT LIKE '%REFR')
BEGIN
SET @OUT_MESSAGE = (@OUT_MESSAGE + 'EST QT: ' +ISNULL(@QT,'UNKNOWN'))
END

IF (@CURR_REC < 1)
BEGIN
INSERT INTO TBL_CONTACT (zip, name, email, number1, number2, message, source, refr, serve, date, myid)
VALUES (@ZIP, @NAME, @EMAIL, @NUMBER1, @NUMBER2, @OUT_MESSAGE, @SOURCE, @REFR, @TODAY, @MYID)
END

SET @CURR_REC = (SELECT TOP 1 ISNULL(REC_ID,0) FROM TBL_CONTACT WHERE (zip LIKE @ZIP AND name LIKE @NAME AND @email LIKE @EMAIL AND number1 LIKE @NUMBER1 AD message LIKE @OUT_MESSAGE AND source LIKE @SOURCE AND refr LIKE @REFR AND serve LIKE @SERVE AND date LIKE @TODAY))
SET @CURR_REC = ISNULL(@CURR_REC,0)

IF @@NAME LIKE 'TEST' OR @NAME LIKE 'NONE')
BEGIN
SET @CURR_REC = 0
END

IF (@CURR_REC > 0)
BEGIN
SET @OUT_MESSAGE = 'Message body'
SET @OUT_MESSAGE = @OUT_MESSAGE + 'more body'

IF (@MYID NOT LIKE 'NONE') AND (@SERVE IN ('BRANCH1','BRANCH2'))
BEGIN
SET @OUT_MESSAGE = 'more code...'
END

这是日志错误：

2018 年 8 月 7 日 13:11:45.98 w3wp.exe (0x2778) 0x12CC SharePoint Foundation Web 部件 89a1 执行 Web 部件时出错：System.Data.SqlClient.SqlException：将数据类型 varchar 转换为浮点数时出错。在 System.Data.SqlClient.SqlConnection.OnError（SqlException 异常，布尔 breakConnection） 在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj） 在 System.Data.SqlClient.TdsParser.Run（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream , BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 在 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 在 System.Data.SqlClient.SqlDataReader.get_MetaData() 在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,

2018 年 8 月 7 日 13:11:45.98* w3wp.exe (0x2778) 0x12CC SharePoint Foundation Web 部件 89a1 高 ...erTds（CommandBehavior cmdBehavior，RunBehavior runBehavior，布尔返回流，布尔异步）在 System.Data.SqlClient.SqlCommand。 System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，布尔 returnStream，String 方法）在 System.Data.SqlClient.SqlCommand.ExecuteReader 的 RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，布尔 returnStream，String 方法，DbAsyncResult 结果） （CommandBehavior 行为，String 方法）在 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader（CommandBehavior 行为）在 System.Data.Common.DbCommand.System.Data.IDbCommand。在 System.Data.Common.DbDataAdapter.FillInternal（DataSet 数据集，DataTable[] 数据表，Int32 开始... 4fe25e3e-0d93-45e1-80ab-4d934b5af716 处执行Reader（CommandBehavior 行为）

2018 年 8 月 7 日 13:11:45.98* w3wp.exe (0x2778) 0x12CC SharePoint Foundation Web 部件 89a1 高 ...Record、Int32 maxRecords、字符串 srcTable、IDbCommand 命令、CommandBehavior 行为）在 System.Data.Common.DbDataAdapter。在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) 在 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect( DataSourceSelectArguments 参数）在 System.Web.UI.DataSourceView.Select（DataSourceSelectArguments 参数，DataSourceViewSelectCallback 回调）在 Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal() 在 Microsoft.SharePoint.WebControls.SingleDataSource。Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator 上的 GetXPathNavigator()（字符串 viewP... 4fe25e3e-0d93-45e1-80ab-4d934b5af716

标签： sqlsql-serversharepointvarchar