存储过程返回将数据类型varchar转换为int.（8114）（SQLExecDirectW）'时出错

Question

我试图使用存储过程执行CRUD操作，但在更新时，我得到了错误Error converting data type varchar to int. (8114) (SQLExecDirectW)')。我编写了一个函数，将空字符串返回到一个整数并返回它，它似乎工作正常（我打印了结果），但我得到了一个错误

以下是存储过程：

USE [testenr]
GO
CREATE PROCEDURE [dbo].[updateJobPost]
    @id int = Null,
    @TopicName nvarchar(300) = NULL,
    @UpdatedDate datetime2(7) = NULL,
    @IsActive bit = 0,
    @IsClose bit = 1,
    @ForceCloseReason nvarchar(3999) = NULL,
    @IsNotification bit = 0,
    @SMSText nvarchar(150) = NULL,
    @WhatsAppText nvarchar(1000) = NULL,
    @Category_id int = NULL,
    @CloseBy_id int = NULL,
    @ForceCloseCategory_id int = NULL,
    @SubCategory_id int = NULL,
    @User_id int = NULL
     
AS
UPDATE [dbo].[accounts_topiclist]
   SET     [TopicName]=@TopicName,
           [UpdatedDate]=@UpdatedDate,
           [IsActive]=@IsActive,
           [IsClose]=@IsClose,
           [ForceCloseReason]=@ForceCloseReason,
           [IsNotification]=@IsNotification,
           [SMSText]=@SMSText,
           [WhatsAppText]=@WhatsAppText,
           [Category_id]=Category_id,
           [CloseBy_id]=@CloseBy_id,
           [ForceCloseCategory_id]=@ForceCloseCategory_id,
           [SubCategory_id]=@SubCategory_id,
           [User_id]=@User_id
WHERE id = @id
GO

以下是视图：

def post(self, request, pk):
        pk = self.kwargs.get('pk')
        if request.method == 'POST':
            topicname = request.POST['TopicName']
            category_id = AllProcedures.empty(request.POST['Category'])
            sub_Category = AllProcedures.empty(request.POST['SubCategory'])
            isActive = request.POST.get('IsActive')
            active = AllProcedures.boolcheck(isActive)
            isClose = request.POST.get('IsClose')
            close = AllProcedures.boolcheck(isClose)
            closed_by = AllProcedures.empty(request.POST['CloseBy'])
            closereason = request.POST['ForceCloseReason']
            CLosedCategory = AllProcedures.empty(request.POST['ForceCloseCategory'])
            isNotify = request.POST.get('IsNotification')
            notify = AllProcedures.boolcheck(isNotify)
            sms = request.POST['SMSText']
            wap = request.POST['WhatsAppText']
            li = [request.user.email, request.user.City, pk, topicname, category_id, sub_Category, active, close, closed_by, closereason, CLosedCategory, notify, sms, wap]
            print(li)
            cursor = connection.cursor()
            cursor.execute(f"EXEC dbo.updateJobPost @id='{pk}', @TopicName='{topicname}', @UpdatedDate='{datetime.datetime.now()}', @IsActive='{active}', @IsClose='{close}', @ForceCloseReason='{closereason}', @IsNotification='{notify}', @SMSText='{sms}', @Category_id='{category_id}', @CloseBy_id='{closed_by}', @ForceCloseCategory_id='{CLosedCategory}', @SubCategory_id='{sub_Category}', @User_id='{request.user.email}'")
            return redirect('/alljobs')

这是我试图打印的li：

['dummy@gmail.com', 'India', 5, '325453354', 1, 0, 1, 0, 0, '', 0, 1, '', '']

表单返回空字符串时有几个字段，我编写了这个函数来转换类型

@staticmethod
    def empty(var):
        if var == '':
            return 0
        else: 
            return int(var)

Answer 1

可以是传递给int参数的任何字符串，但此字符串

 @User_id='{request.user.email}'")

这几乎肯定是错误的

 @User_id int = NULL

在SQL中使用参数而不是字符串插值是一种更好、更安全的做法。请参见示例here，如下所示：

cursor.execute(f"EXEC dbo.updateJobPost @id=?, @TopicName=?, @UpdatedDate=?, @IsActive=?, @IsClose=?, @ForceCloseReason=?, @IsNotification=?, @SMSText=?, @Category_id=?, @CloseBy_id=?, @ForceCloseCategory_id=?, @SubCategory_id=?, @User_id=?",     pk,topicname,datetime.datetime.now(),active,close,closereason,notify,sms,category_id,closed_by,CLosedCategory,sub_Category,request.user.email)