我不知道为什么这不管用。如果它成功地保存,我会将其设置为返回1的结果,但我始终得到0。这是可行的,但我最近给查询增加了困难。现在它不起作用了:
SQL查询:
DECLARE
@Email AS NVARCHAR(500) = 'uokgames@gmail.com',
@Update AS NVARCHAR(MAX) = 'SET Tile_332 = 1,Tile_216 = 1,Tile_296 = 1,Tile_303 = 1,Tile_313 = 1,Tile_274 = 1,Tile_261 = 1,Tile_311 = 1,Tile_320 = 1,Tile_204 = 1,Tile_304 = 1,Tile_187 = 1,Tile_291 = 1',
@Board AS NVARCHAR(100) = 'Templo_Mayor',
@Difficulty AS INT = 0
--AS
BEGIN
DECLARE @sql AS NVARCHAR(MAX),
@Response AS BIT = 0
BEGIN TRY
SET @sql = 'UPDATE [dbo].[Game_Board] ' + @Update + ' WHERE [Email] =''' + @Email + ''' AND [Board_Name] = ''' + @Board + ''' AND [Difficulty] = ' + @Difficulty
EXECUTE sp_executesql @sql
SET @Response = 1;
END TRY
BEGIN CATCH
SET @Response = 0;
END CATCH
SELECT @Response
END
发布于 2020-01-23 03:19:40
在这里,您只需要将困难转换为varchar(1),它就会工作。
DECLARE
@Email AS NVARCHAR(500) = 'uokgames@gmail.com',
@Update AS NVARCHAR(MAX) = 'SET Tile_332 = 1,Tile_216 = 1,Tile_296 = 1,Tile_303 = 1,Tile_313 = 1,Tile_274 = 1,Tile_261 = 1,Tile_311 = 1,Tile_320 = 1,Tile_204 = 1,Tile_304 = 1,Tile_187 = 1,Tile_291 = 1',
@Board AS NVARCHAR(100) = 'Templo_Mayor',
@Difficulty AS varchar(1) = '0'
--AS
BEGIN
DECLARE @sql AS NVARCHAR(MAX),
@Response AS BIT = 0
BEGIN TRY
SET @sql = 'UPDATE [dbo].[Game_Board] ' + @Update + ' WHERE [Email] =''' + @Email + ''' AND [Board_Name] = ''' + @Board + ''' AND [Difficulty] = ' + @Difficulty
EXECUTE sp_executesql @sql
SET @Response = 1;
END TRY
BEGIN CATCH
SET @Response = 0;
END CATCH
SELECT @Response
END
您需要删除try catch事件,这样才能得到真正的错误:
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the nvarchar value 'UPDATE [dbo].[Game_Board] SET Tile_332 = 1,Tile_216 = 1,Tile_296 = 1,Tile_303 = 1,Tile_313 = 1,Tile_274 = 1,Tile_261 = 1,Tile_311 = 1,Tile_320 = 1,Tile_204 = 1,Tile_304 = 1,Tile_187 = 1,Tile_291 = 1 WHERE [Email] ='uokgames@gmail.com' AND [Board_Name] = 'Templo_Mayor' AND [Difficulty] = ' to data type int.
发布于 2020-01-23 03:18:09
使用参数!
BEGIN TRY
SET @sql = 'UPDATE [dbo].[Game_Board] ' + @Update + ' WHERE [Email] = @Email AND [Board_Name] = @Board AND [Difficulty] = @Difficulty';
EXECUTE sp_executesql @sql,
N'@Email NVARCHAR(500), @Board NVARCHAR(100), @Difficulty INT,
@Email=@Email, @Board=@Board, @Difficulty=@Difficulty
SET @Response = 1;
END TRY
BEGIN CATCH
SET @Response = 0;
END CATCH
https://stackoverflow.com/questions/59871116
复制相似问题