ALTER PROCEDURE [dbo].[spMenuList]
(
@UserID INT,
@ParentID int=null,
@PowerValue NVARCHAR(100)=null
)
AS
Select no=Identity(int,1,1),* Into #temptable From
(
SELECT
ModuleID
,Parent_id
,MenuCode
,ModuleName
,Url
,ParentMenuCode
FROM
(
SELECT DISTINCT
ML.ModuleID,
ML.ParentID AS Parent_id,
ML.ModuleName,
ML.PowerPage AS Url,
ML.MenuCode,
ML.ParentMenuCode
FROM Module M
INNER JOIN Module ML ON M.MenuCode LIKE ML.MenuCode + '%'
where M.IsUsed = 1
) AS A
where ((@ParentID is null and Parent_id is null) or (Parent_id=@ParentID and Parent_id is not null))
AND (@PowerValue is null or ModuleID IN (@PowerValue))
) as TableName
Select * From #temptable ORDER BY MenuCode
Drop Table #temptable
错误如下:消息 245,级别 16,状态 1,过程 spMenuList,第 14 行
在将 nvarchar 值 '9,10,11,12,13,14' 转换成数据类型 int 时失败。
exec spMenuList2 @UserID=2,@ParentID=8,@PowerValue='9,10,11,12,13,14'
@PowerValue的值如上是一个字符串,ModuleID的类型是int型,所以会出错,但是我不知道要怎么改这个存储过程,麻烦改下,
(
@UserID INT,
@ParentID int=null,
@PowerValue NVARCHAR(100)=null
)
AS
Select no=Identity(int,1,1),* Into #temptable From
(
SELECT
ModuleID
,Parent_id
,MenuCode
,ModuleName
,Url
,ParentMenuCode
FROM
(
SELECT DISTINCT
ML.ModuleID,
ML.ParentID AS Parent_id,
ML.ModuleName,
ML.PowerPage AS Url,
ML.MenuCode,
ML.ParentMenuCode
FROM Module M
INNER JOIN Module ML ON M.MenuCode LIKE ML.MenuCode + '%'
where M.IsUsed = 1
) AS A
where ((@ParentID is null and Parent_id is null) or (Parent_id=@ParentID and Parent_id is not null))
AND (@PowerValue is null or ModuleID IN (@PowerValue))
) as TableName
Select * From #temptable ORDER BY MenuCode
Drop Table #temptable
错误如下:消息 245,级别 16,状态 1,过程 spMenuList,第 14 行
在将 nvarchar 值 '9,10,11,12,13,14' 转换成数据类型 int 时失败。
exec spMenuList2 @UserID=2,@ParentID=8,@PowerValue='9,10,11,12,13,14'
@PowerValue的值如上是一个字符串,ModuleID的类型是int型,所以会出错,但是我不知道要怎么改这个存储过程,麻烦改下,
AND (@PowerValue is null or ModuleID IN (''+@PowerValue+''))