Question

请问这个存储过程怎么改啊，现在它总是报错啊，

错误如下：消息 245，级别 16，状态 1，过程 spMenuList，第 14 行
在将 nvarchar 值 '9,10,11,12,13,14' 转换成数据类型 int 时失败。
[spMenuList] 1,8,'9,10,11,12,13,14'
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 --Where no>=6 And no < 10
Drop Table #temptable
楼下的，我就只有三个参数，第三个参数必须那样传。

Answer 1

alter PROCEDURE [dbo].[spMenuList]
(
@UserID INT,
@ParentID int=null,
@PowerValue NVARCHAR(100)=null
)
AS
declare @sql varchar(2000)
begin
--drop 临时表##TableName 以便过程可以多次执行
if object_ID('tempdb..##TableName') is not null
drop table tempdb..##tableName

--生成临时表#A
SELECT DISTINCT ML.ModuleID, ML.ParentID AS Parent_id,ML.ModuleName,ML.PowerPage AS Url, ML.MenuCode, ML.ParentMenuCode
Into #A
FROM Module M
INNER JOIN Module ML ON M.MenuCode LIKE ML.MenuCode + '%'
where M.IsUsed = 1

--生成临时表#TableName
set @sql=' SELECT ModuleID ,Parent_id ,MenuCode ,ModuleName ,Url ,ParentMenuCode'
+' into ##TableName'
+' FROM #A'
+' where (('+Cast(@ParentID as varchar(10))+' is null and Parent_id is null) or (Parent_id='+Cast(@ParentID as varchar(10))+' and Parent_id is not null)) '
+' AND ('+''''+@PowerValue+''''+' is null or ModuleID IN ('+@PowerValue+'))'
exec (@sql)

--生成临时表#temptable
Select no=Identity(int,1,1),*
Into #temptable
From ##TableName
--获取数据
Select * From #temptable ORDER BY MenuCode --Where no>=6 And no < 10

end
go

不行再告诉我，

Answer 2

[spMenuList] 1,8,'''9'',''10'',''11'',''12'',''13'',''14'''
参数这样传递就可以了

Answer 3

exec spMenuList 1,8,‘9,10,11,12,13,14’