背景:--权限表
CREATE TABLE TProperty( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fDetails NVARCHAR(MAX) NULL,
CONSTRAINT [PK_TProperty] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TProperty(fName,fDetails) VALUES('权1','权限1');
INSERT INTO TProperty(fName,fDetails) VALUES('权2','权限2');
INSERT INTO TProperty(fName,fDetails) VALUES('权3','权限3');
INSERT INTO TProperty(fName,fDetails) VALUES('权4','权限4');
INSERT INTO TProperty(fName,fDetails) VALUES('权5','权限5');
INSERT INTO TProperty(fName,fDetails) VALUES('权6','权限6');
INSERT INTO TProperty(fName,fDetails) VALUES('权7','权限7');
......
INSERT INTO TProperty(fName,fDetails) VALUES('权n','权限n');--人员表
CREATE TABLE TStaff( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fProperties NVARCHAR(1024) NULL,
CONSTRAINT [PK_TStaff] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TStaff(fName,fProperties) VALUES('人1','1');
INSERT INTO TStaff(fName,fProperties) VALUES('人2','1,2,');
INSERT INTO TStaff(fName,fProperties) VALUES('人3','1,3,4,5');
INSERT INTO TStaff(fName,fProperties) VALUES('人4',null);
......
INSERT INTO TStaff(fName,fProperties) VALUES('人m','1,3,5,7,8');问题:
要求用T-SQL实现查询指定人员所拥有的权限名称。我的写法:
-----------------------------------------------
SELECT fName FROM TProperty
WHERE fID IN
(
SELECT fProperties FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);当@fID=3,运行的时行提示:
在将 nvarchar 值 '1,3,4,5' 转换成数据类型 int 时失败。-----------------------------------------------
于是,我将语句修改为:
SELECT fName FROM TProperty
WHERE CAST(fID AS NVARCHAR(12)) IN
(
SELECT fProperties FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);
当@fID=3,运行的结果为NULL。-----------------------------------------------
于是,我将语句修改为:
SELECT fName FROM TProperty
WHERE CAST(fID AS NVARCHAR(12)) IN
(
SELECT REPLACE(ISNULL(fProperties,'-1'),',',CHAR(39)+','+CHAR(39)) FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);
当@fID=3,运行的结果为NULL。-----------------------------------------------
至此我发现问题所在,却没有办法改正。
现向大家求助:
1、若我坚持要求使用如上的语句结构(即:SELECT FROM WHERE IN ()) ,请问如何调整语句?
2、若是无法使用如上的语句结构(即:SELECT FROM WHERE IN ()),请写出你的高效易读的T-SQL语句。
CREATE TABLE TProperty( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fDetails NVARCHAR(MAX) NULL,
CONSTRAINT [PK_TProperty] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TProperty(fName,fDetails) VALUES('权1','权限1');
INSERT INTO TProperty(fName,fDetails) VALUES('权2','权限2');
INSERT INTO TProperty(fName,fDetails) VALUES('权3','权限3');
INSERT INTO TProperty(fName,fDetails) VALUES('权4','权限4');
INSERT INTO TProperty(fName,fDetails) VALUES('权5','权限5');
INSERT INTO TProperty(fName,fDetails) VALUES('权6','权限6');
INSERT INTO TProperty(fName,fDetails) VALUES('权7','权限7');
......
INSERT INTO TProperty(fName,fDetails) VALUES('权n','权限n');--人员表
CREATE TABLE TStaff( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fProperties NVARCHAR(1024) NULL,
CONSTRAINT [PK_TStaff] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TStaff(fName,fProperties) VALUES('人1','1');
INSERT INTO TStaff(fName,fProperties) VALUES('人2','1,2,');
INSERT INTO TStaff(fName,fProperties) VALUES('人3','1,3,4,5');
INSERT INTO TStaff(fName,fProperties) VALUES('人4',null);
......
INSERT INTO TStaff(fName,fProperties) VALUES('人m','1,3,5,7,8');问题:
要求用T-SQL实现查询指定人员所拥有的权限名称。我的写法:
-----------------------------------------------
SELECT fName FROM TProperty
WHERE fID IN
(
SELECT fProperties FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);当@fID=3,运行的时行提示:
在将 nvarchar 值 '1,3,4,5' 转换成数据类型 int 时失败。-----------------------------------------------
于是,我将语句修改为:
SELECT fName FROM TProperty
WHERE CAST(fID AS NVARCHAR(12)) IN
(
SELECT fProperties FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);
当@fID=3,运行的结果为NULL。-----------------------------------------------
于是,我将语句修改为:
SELECT fName FROM TProperty
WHERE CAST(fID AS NVARCHAR(12)) IN
(
SELECT REPLACE(ISNULL(fProperties,'-1'),',',CHAR(39)+','+CHAR(39)) FROM TStaff
WHERE fID =@fID --@fID为输入参数,对应指定人员编号
);
当@fID=3,运行的结果为NULL。-----------------------------------------------
至此我发现问题所在,却没有办法改正。
现向大家求助:
1、若我坚持要求使用如上的语句结构(即:SELECT FROM WHERE IN ()) ,请问如何调整语句?
2、若是无法使用如上的语句结构(即:SELECT FROM WHERE IN ()),请写出你的高效易读的T-SQL语句。
goexec sp_helprotect @username = 'sa'
go
IF OBJECT_ID('TProperty') IS NOT NULL DROP TABLE TProperty
GO
CREATE TABLE TProperty( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fDetails NVARCHAR(MAX) NULL,
CONSTRAINT [PK_TProperty] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TProperty(fName,fDetails) VALUES('权1','权限1');
INSERT INTO TProperty(fName,fDetails) VALUES('权2','权限2');
INSERT INTO TProperty(fName,fDetails) VALUES('权3','权限3');
INSERT INTO TProperty(fName,fDetails) VALUES('权4','权限4');
INSERT INTO TProperty(fName,fDetails) VALUES('权5','权限5');
INSERT INTO TProperty(fName,fDetails) VALUES('权6','权限6');
INSERT INTO TProperty(fName,fDetails) VALUES('权7','权限7');
INSERT INTO TProperty(fName,fDetails) VALUES('权n','权限n'); IF OBJECT_ID('TStaff') IS NOT NULL DROP TABLE TStaff
GO
--人员表
CREATE TABLE TStaff( fID INT IDENTITY(1,1) NOT NULL,
fName NVARCHAR(50) NOT NULL,
fProperties NVARCHAR(1024) NULL,
CONSTRAINT [PK_TStaff] PRIMARY KEY CLUSTERED
(
[fID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
);
INSERT INTO TStaff(fName,fProperties) VALUES('人1','1');
INSERT INTO TStaff(fName,fProperties) VALUES('人2','1,2,');
INSERT INTO TStaff(fName,fProperties) VALUES('人3','1,3,4,5');
INSERT INTO TStaff(fName,fProperties) VALUES('人4',null);
INSERT INTO TStaff(fName,fProperties) VALUES('人m','1,3,5,7,8'); declare @fid int,@str varchar(400),@n nvarchar(20)
set @fid=3SELECT @n=fProperties FROM TStaff
WHERE fID =ltrim(@fID)set @str=N'SELECT fName FROM TProperty
WHERE ltrim(fID) IN
( '+@n+')' --@fID为输入参数,对应指定人员编号 exec(@str)fName
--------------------------------------------------
权1
权3
权4
权5(4 行受影响)
或者这样:declare @fid int,@n nvarchar(20)
set @fid=3SELECT @n=fProperties FROM TStaff
WHERE fID =ltrim(@fID)EXEC('SELECT fName FROM TProperty
WHERE ltrim(fID) IN
( '+@n+')') --@fID为输入参数,对应指定人员编号
fName
--------------------------------------------------
权1
权3
权4
权5(4 行受影响)
set @fid=3SELECT @n=fProperties FROM TStaff
WHERE fID =ltrim(@fID)--应加上此语句,以防止@n 取值为NULL或String.Empty时造成语法错误!!!
SET @n=CASE WHEN @n IS NULL THEN '-1' WHEN LTRIM(RTRIM(@n))='' THEN '-1' ELSE @n END
EXEC('SELECT fName FROM TProperty
WHERE ltrim(fID) IN
( '+@n+')') --@fID为输入参数,对应指定人员编号
fName
--------------------------------------------------
权1
权3
权4
权5(4 行受影响)