将nvarchar值'update photo ****转换为[imagededicated]）时，转换失败，其中imageid ='到数据类型int。 [英] Conversion failed when converting the nvarchar value 'update photo **** as [imagededicated]) where imageid = ' to data type int.

问题描述

大家好，

我有一个存储过程SaveImage如下;

Hello all,
I have a stored procedure SaveImage as follows;

USE [WCSC]
GO
/****** Object:  StoredProcedure [dbo].[SaveImage]    Script Date: 6/6/2016 12:59:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SaveImage] @nimageid int, @cimagedata nvarchar(MAX) 
AS 
BEGIN
DECLARE @strImage nvarchar(888);
SET @strImage = @cimagedata;
DECLARE @strExec [nvarchar](999);
SET @strExec = N'Update photo set imagedata =(SELECT * FROM OPENROWSET(BULK N''' + @strImage + ''', SINGLE_BLOB) as [imageDedicated]) where imageid = '+ @nimageid;
EXEC(@strExec)
END

执行以下操作时，

While executing this as below,

GO
DECLARE @nimageid int;
DECLARE @cimagedata nvarchar(MAX);
SET @nimageid = 1234;
SET @cimagedata =  'i:\wcsc_server\1001001.jpg';
EXEC SaveImage @nimageid , @cimagedata 

我收到此错误：

I get this error:

Conversion failed when converting the nvarchar value 'Update photo set imagedata =(SELECT * FROM OPENROWSET(BULK N'i:\wcsc_server\1001001.jpg', SINGLE_BLOB) as [imageDedicated]) where imageid = ' to data type int.

我尝试了什么：



错误在哪里？在存储过程或调用代码中？

What I have tried:

Where is the error? In the stored procedure or calling code?

推荐答案

您正在尝试向字符串添加整数：

You are trying to add an integer to a string:

SET @strExec = N'Update ... where imageid = '+ @nimageid;

但SQL in它的无限智慧试图将字符串转换为整数，而不是将整数转换为字符串。

你必须手动转换它：

But SQL in it's infinite wisdom tries to convert the string to an integer, rather than the integer to a string.
You have to manually convert it:

SET @strExec = N'Update ... where imageid = '+ CONVERT(NVARCHAR(10), @nimageid);

这篇关于将nvarchar值'update photo ****转换为[imagededicated]）时，转换失败，其中imageid ='到数据类型int。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！