Question

当我执行 SP 时, 我收到错误信息 :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near . .

我测试了SQL,没有案例说明, 它的工作,它返回每个人是管理者。

ALTER PROCEDURE [dbo].[GetRoles]
AS
DECLARE @num varchar(25),
@reportid Decimal,
@dom varchar(10)
set @reportid = 1
set @dom =  use5 

SELECT @num = 
Case @reportid

 WHEN 1 THEN
   select distinct u.id as userId, u.domain, u.isAdmin, u.email, u.canReport, a.[site],
  a.bldgNum, a.dataCenterNum, l.shortName, l.[description], a.canApprove, a.canComplete
  from locAdmin a inner join location l on (a.site=l.site and a.bldgNum = l.bldgNum 
  and a.dataCenterNum = l.dataCenterNum) right outer join [user] u on u.id=a.userId 
  and u.domain=a.domain where u.isAdmin = 1 

 End

 EXEC (@num)

同样明显的是,由于某种原因,无法过滤重复的用词。有些用词根据它们拥有的存取权限(也就是我进来之前的设置方式)被输入了 3x (Userids) 。

Answer 1

You have limited @num to 25 characters - but the string you re setting it to is much longer than that - it ll be truncated
(also: @num is a very odd and potentially misleading name for this - it suggest something numeric - and this is really more of a SQL statement or something - use the principle of least surprise when naming your variables!)

尝试一下这样的东西:

DECLARE @stmt NVARCHAR(2000)
.....

SELECT @stmt = 
   CASE @reportid
      WHEN 1 THEN
          N select distinct u.id as userId, u.domain, u.isAdmin, u.email, u.canReport, a.[site],
  a.bldgNum, a.dataCenterNum, l.shortName, l.[description], a.canApprove, a.canComplete
  from locAdmin a inner join location l on (a.site=l.site and a.bldgNum = l.bldgNum 
  and a.dataCenterNum = l.dataCenterNum) right outer join [user] u on u.id=a.userId 
  and u.domain=a.domain where u.isAdmin = 1 

 EXEC (@stmt)

友情链接