,所以我的问题是我在关键字附近遇到错误的语法'问题与众不同。该代码将在SSRS报告中使用,您将看到我在哪里提出的注释,我将用参数替换这些值。从本质上讲,操作顺序是创建一个分支与所提供的输入匹配的所有用户的表。选择他们的"标志",这只是一个唯一的数字,它允许我们计算每个用户拥有的权限并将其转换为二进制文件,然后交叉检查该用户是否具有我们需要的权限,如果这样创建一个新的用户表权限
Declare @Users table(Names nvarchar(50) not null, Flag int)
Declare @ValidUsers table(Names nvarchar(50) not null)
Declare @Office int
Declare @NumberOfRecords int
Declare @Count int
Declare @IntCount int
DECLARE @Binary AS nvarchar(16);
declare @bit as nvarchar(1);
declare @PermissionSub as nvarchar(1);
declare @Permission as nvarchar(16);
declare @ShouldContinue as bit
set @ShouldContinue = 1;
set @Permission = '0001111111111111'; /* going to pass this value */
set @Count = '1'
set @IntCount = '1'
Set @Office = '3' /* going to pass this value */
Insert into @Users
Select dbUser.usrFullName, udFeeEarnerLicence.purchaseFlag
From udFeeEarnerLicence INNER JOIN
dbUser ON udFeeEarnerLicence.feeUsrId = dbUser.usrID
where dbUser.brId = @Office
select @NumberOfRecords = COUNT(Flag) from @Users
DECLARE @Flag AS int;
select @Flag = Flag from @Users
while(@Count <= @NumberOfRecords)
begin
select @Flag = Flag from @Users where ROW_NUMBER() over (order by Flag) = @Count
WITH A AS (
SELECT 0 AS ORD, @Flag AS NUMBER, CAST('' AS VARCHAR(20)) AS BITS
UNION ALL
SELECT ORD+1, NUMBER/2, CAST(BITS+CAST(NUMBER%2 AS VARCHAR(20)) AS VARCHAR(20))
FROM A
WHERE NUMBER>0)
SELECT @Binary = RIGHT('000000000000000'+ CASE WHEN BITS='' THEN '0' ELSE REVERSE(BITS) END,16)
FROM A
WHERE NUMBER = 0;
while(@IntCount <= 16)
begin
select @bit = SUBSTRING(@Binary, @IntCount, @IntCount + 1)
select @PermissionSub = SUBSTRING(@Permission, @IntCount, @IntCount + 1)
if(@PermissionSub = '1' and @bit != '1') /* if Permission selection is required and user does not have permission*/
begin
SET @ShouldContinue = 0
break;
end
end
Set @IntCount = 0
if(@ShouldContinue = 0)
begin
continue
end
Insert into @ValidUsers
select Names from @Users where ROW_NUMBER() over (order by Flag) = @Count
end
我刚刚尝试使用;
再次运行它,然后提出另一个错误,说"窗口函数只能出现在SELECT或ORDER中","
有关WITH
子句的第一个错误要求您将;
放在WITH
之前,以结束最后一个语句。
第二个错误是因为您正在尝试在WHERE
子句中使用窗口功能,这是不可能的。您可以通过更改代码的此部分来解决它:
Insert into @ValidUsers
select Names from @Users where ROW_NUMBER() over (order by Flag) = @Count
with:
; WITH CTE AS (
SELECT Names, ROW_NUMBER() OVER (ORDER BY Flag) AS RwNr
FROM @Users
)
INSERT INTO @ValidUsers
SELECT Names
FROM CTE
WHERE RwNr = @Count
注意:此代码尚未测试,但我相信即使您将其复制/粘贴到解决方案中,也应该有效。
但是,如 @sean lange 评论,您可能需要在此处重构整个解决方案,因为它根本不可扩展。我的代码段(上文提供)应该清除您的错误,但不会解决您的可扩展性和性能问题。
编辑:当您指出的那样,您的代码中还有另一个窗口函数,然后我已经给了您固定的代码段。基本上是同一错误。更改代码的这一部分:
select @Flag = Flag from @Users where ROW_NUMBER() over (order by Flag) = @Count
with:
; WITH CTE AS (
SELECT Flag, ROW_NUMBER() OVER (ORDER BY Flag) AS RwNr
FROM @Users
)
SELECT TOP(1) @Flag = Flag -- this TOP(1) is just a fail-safe
FROM CTE
WHERE RwNr = @Count
另外,这是有关该主题的一些文档和有用的链接:
- 为什么使用
CTE
时半分号,一个很好的合理解释:https://stackoverflow.com/a/6938089/6492765 -
CTE
官方文件:https://technet.microsoft.com/en-us/library/ms190766(v = sql.105).aspx - 为什么在
WHERE
子句中没有窗口功能,就在stackoverflow上:https://stackoverflow.com/a/13997396/6492765 - 窗口排名函数官方文档:https://msdn.microsoft.com/en-us/library/ms189798.aspx
在with
之后添加;
。
这将删除错误。
SQL语句应用半彩终止。
尽管尚未完全强制性,但许多较新的SQL构造都需要终止先前的陈述。CTE包括
摘自https://msdn.microsoft.com/en-us/library/ms177563.aspx(实际上是较旧的版本https://msdn.microsoft.com/en-us/library/ms1777563(v=sql。100).aspx)
Transact-SQL语句终结器。虽然分号不是 此版本的SQL Server中的大多数语句都需要 在以后的版本中需要。