ALTER PROC [dbo].[spFunctionMenuSave]
(
@FuncID INT,
@DetailString1 VARCHAR(8000)
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFFDECLARE @vErrMsg VARCHAR(100) --错误信息
--重新保存新的信息
CREATE TABLE #Detail
(
-- ModuleID varchar(50),
MenuCode varchar(50),
MenuName varchar(50),
MenuUrl varchar(100)
)INSERT INTO #Detail
EXEC K_spStream2Table
@Stream = @DetailString1,
-- @Stream ='001505|!区域列表|!ssss',
@Seperator = '|!',
--@TableDesc = 'INT|!INT|!MONEY|!MONEY',
@TableDesc = 'varchar(50)|!varchar(50)|!varchar(100)',
@TableSeperator = '|!'IF @@ERROR <> 0
BEGIN
SET @vErrMsg = '分解数据字符串时出错!'
GOTO ErrHandle
ENDDECLARE @MenuCode varchar(50)
SELECT @MenuCode=MenuCode FROM #DetailDECLARE @ParentID varchar(50)
SELECT @ParentID=MenuID FROM PF_Menu where substring(@MenuCode,1,2)=MenuCode
INSERT INTO dbo.[PF_Menu](FuncID, MenuCode,MenuName,MenuUrl,ParentID,isused)
SELECT @FuncID, MenuCode,MenuName,MenuUrl,@ParentID,'True'
FROM #DetailIF @@ERROR <> 0
BEGIN
SET @vErrMsg = '无法保存功能模块!'
GOTO ErrHandle
END--清理现场
DROP TABLE #Detail-- COMMIT TRANRETURN
-- 异常出口
ErrHandle:
-- ROLLBACK TRAN
RAISERROR(@vErrMsg, 11, 1)我想让@ParentID 是null的时候,@FuncID也是null,不是null的时候,@FuncID是原来的值。
(
@FuncID INT,
@DetailString1 VARCHAR(8000)
)
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFFDECLARE @vErrMsg VARCHAR(100) --错误信息
--重新保存新的信息
CREATE TABLE #Detail
(
-- ModuleID varchar(50),
MenuCode varchar(50),
MenuName varchar(50),
MenuUrl varchar(100)
)INSERT INTO #Detail
EXEC K_spStream2Table
@Stream = @DetailString1,
-- @Stream ='001505|!区域列表|!ssss',
@Seperator = '|!',
--@TableDesc = 'INT|!INT|!MONEY|!MONEY',
@TableDesc = 'varchar(50)|!varchar(50)|!varchar(100)',
@TableSeperator = '|!'IF @@ERROR <> 0
BEGIN
SET @vErrMsg = '分解数据字符串时出错!'
GOTO ErrHandle
ENDDECLARE @MenuCode varchar(50)
SELECT @MenuCode=MenuCode FROM #DetailDECLARE @ParentID varchar(50)
SELECT @ParentID=MenuID FROM PF_Menu where substring(@MenuCode,1,2)=MenuCode
INSERT INTO dbo.[PF_Menu](FuncID, MenuCode,MenuName,MenuUrl,ParentID,isused)
SELECT @FuncID, MenuCode,MenuName,MenuUrl,@ParentID,'True'
FROM #DetailIF @@ERROR <> 0
BEGIN
SET @vErrMsg = '无法保存功能模块!'
GOTO ErrHandle
END--清理现场
DROP TABLE #Detail-- COMMIT TRANRETURN
-- 异常出口
ErrHandle:
-- ROLLBACK TRAN
RAISERROR(@vErrMsg, 11, 1)我想让@ParentID 是null的时候,@FuncID也是null,不是null的时候,@FuncID是原来的值。
DECLARE @ParentID varchar(50)
SELECT @ParentID=MenuID FROM PF_Menu where substring(@MenuCode,1,2)=MenuCode
INSERT INTO dbo.[PF_Menu](FuncID, MenuCode,MenuName,MenuUrl,ParentID,isused)
SELECT @FuncID, MenuCode,MenuName,MenuUrl,@ParentID,'True'
FROM #Detail怎么写?
DECLARE @ParentID varchar(50)
declare cur cursor for select MenuID FROM PF_Menu where substring(@MenuCode,1,2)=MenuCode
open cur
fetch cur into @ParentID
while @@fetch_status=0
begin
if @ParentID is null
set @FuncID=null
INSERT INTO dbo.[PF_Menu](FuncID, MenuCode,MenuName,MenuUrl,ParentID,isused)
SELECT @FuncID, MenuCode,MenuName,MenuUrl,@ParentID,'True'
FROM #Detail IF @@ERROR <> 0
BEGIN
close cur
deallocate cur
SET @vErrMsg = '无法保存功能模块!'
GOTO ErrHandle
END
fetch cur into @ParentID
end
close cur
deallocate cur
--清理现场
DROP TABLE #Detail