我正在尝试编写一个存储过程,它需要接收两个参数:表名和记录ID。它必须从指定名称的表(
@TableName
)中返回带有指定ID(@FormID
)的记录。但是我遇到了这个错误:我无法理解该问题,因为我并没有将任何内容转换为int数据类型。SQL传递的参数为:转换varchar值'SELECT * FROM [Form12_AuditLog] WHERE [FormID] ='时,数据类型 int 转换失败。
@FormID = 88
@TableName = Form12_AuditLog
SQL:
USE [MyDatabase]
GO
/****** Object: StoredProcedure [dbo].[GetAuditLogByFormID] Script Date: 20/12/2016 5:50:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetAuditLogByFormID]
@TableName varchar(50),
@FormID integer
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ActualTableName AS varchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @TableName
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + @FormID + ';'
EXEC(@sql)
END