存储过程返回将数据类型 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:

['[e-mail]', '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中使用参数而不是字符串插值是一个更好和更安全的做法。请看这里的例子，所以像这样:

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)