nvarchar(50) ==》 nvarchar(200),SQL的string被截断了。另外,也不能直接将@ID_Char写在刚才的@SQL中,可以考虑通过临时表来处理。DECLARE @TableName varchar(50);
SET @TableName = 'Role'DECLARE @SQL nvarchar(400);
DECLARE @ID_Char char(8);--读取ID
SET @SQL = N'SELECT TOP 1 [ID] INTO #TEMP FROM dbo.' + @TableName + ' ORDER BY [ID] DESC'
SELECT @ID_Char = [ID] FROM #TEMP ......
SET @TableName = 'Role'DECLARE @SQL nvarchar(400);
DECLARE @ID_Char char(8);--读取ID
SET @SQL = N'SELECT TOP 1 [ID] INTO #TEMP FROM dbo.' + @TableName + ' ORDER BY [ID] DESC'
SELECT @ID_Char = [ID] FROM #TEMP ......
DECLARE @TableName varchar(50);
SET @TableName = 'Role'DECLARE @SQL nvarchar(1000);
DECLARE @ID_Char char(8);--读取ID
SET @SQL = N'SELECT TOP 1 @ID_Char = [ID] FROM dbo.' + @TableName + ' ORDER BY [ID] DESC'
EXEC sp_executesql @SQL, N'@ID_Char char(8) output',@ID_Char OUTPUT;
--04表名称: Role
--数据含义: 定义角色,分配一组权限
--字段: ID,Name,Purview,Phase
--关系: Primary(ID),Unique(Name)CREATE TABLE Role([ID] char(8),[Name] varchar(50),[Purview] bigint,
[Phase] tinyint,CONSTRAINT PK_Role_ID PRIMARY KEY ([ID]),
CONSTRAINT UK_Role_Name UNIQUE ([Name]))GO--添加管理员角色
INSERT INTO dbo.[Role]([ID],[Name],[Purview],[Phase])
VALUES('00000001','Administrator',Power(CAST(2 AS bigint),61)-1,0)GO
--<--Error Line--定义变量保存最大的ID数值和字符串值
DECLARE @ID_Num int;
DECLARE @ID_Char char(8);
DECLARE @SQL nvarchar(50);--读取ID
SET @SQL = N'SELECT TOP 1 @ID_Char = [ID] FROM dbo.role ORDER BY [ID] DESC'
EXEC sp_executesql @SQL, N'@ID_Char char(8)',@ID_Char OUTPUT;
/*Result:(所影响的行数为 1 行)服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'B' 附近有语法错误。*/