34,837
社区成员




update table set TypeID=(case when len(ParentID)=1 then (select ParentID+'-'+(case when len(max(right(TypeID,2)+1))=1 then '0'+cast(max(right(TypeID,2)+1) as varchar(1))
else cast(max(right(TypeID,2)+1) as varchar(2)) end)
from table where len(ParentID)>1 ) else (select CHAR(UNICODE(MAX(TypeID)) + 1) FROM TABLE where ParentID is null) end)
update table set TypeID=(select ParentID+'-'+(case when len(max(right(TypeID,2)+1))=1 then '0'+cast(max(right(TypeID,2)+1) as varchar(1))
else cast(max(right(TypeID,2)+1) as varchar(2)) end)
from table where len(ParentID)=1
--CREATE table TABLE1
--(TypeID varchar(4),TypeName varchar(6),ParentID varchar(1))
--GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'UP_InsertFruit')
BEGIN
DROP PROC UP_InsertFruit
END
GO
CREATE PROC UP_InsertFruit
(
@Name VARCHAR(100),
@Type VARCHAR(100),
@ReturnValues_Int INT = 0 OUTPUT
)
AS
DECLARE @MainTYpe VARCHAR(10)
DECLARE @ERROR INT
IF @Type IS NULL
BEGIN
IF EXISTS (SELECT 1 FROM Table1)
BEGIN
SELECT @MainTYpe = CHAR(UNICODE(MAX(TypeID)) + 1) FROM TABLE1
END
ELSE
BEGIN
SET @MainTYpe = 'A'
END
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM Table1 WHERE ParentID = @Type)
BEGIN
SELECT @MainTYpe = CAST((MAX(CAST(RIGHT(TypeID,2) AS INT)) + 1) AS VARCHAR(10)) FROM TABLE1 WHERE ParentID = @Type
END
ELSE
BEGIN
SET @MainTYpe = '01'
END
IF LEN(@MainTYpe) = 1
BEGIN
SET @MainTYpe = '0' + @MainTYpe
END
SET @MainTYpe = @Type + '-' + @MainTYpe
END
BEGIN TRAN
INSERT INTO Table1 VALUES (@MainTYpe,@Name,@Type)
SET @ERROR = @@ERROR
IF @ERROR > 0
BEGIN
GOTO ERRORHANDLE
END
COMMIT TRAN
RETURN 100
ERRORHANDLE:
ROLLBACK TRAN
RETURN -100
GO
EXEC UP_InsertFruit '苹果',NULL
EXEC UP_InsertFruit '红富士','A'
SELECT * FROM TABLE1
TypeID TypeName ParentID
A 苹果 NULL
A-01 红富士 A
CREATE table TABLE1
(TypeID varchar(4),TypeName varchar(6),ParentID varchar(1))
GO
CREATE PROC UP_InsertFruit
(
@Name VARCHAR(100) = '',
@Type VARCHAR(100) = '',
@ReturnValues_Int INT = 0 OUTPUT
)
AS
DECLARE @MainTYpe VARCHAR(10)
DECLARE @ERROR INT
IF (CHARINDEX('-',@Type) > 0)
BEGIN
SET @MainTYpe = LEFT(@Type,1)
END
ELSE
BEGIN
SET @MainTYpe = ''
END
BEGIN TRAN
INSERT INTO Table1 VALUES (@Type,@Name,@MainTYpe)
SET @ERROR = @@ERROR
IF @ERROR > 0
BEGIN
GOTO ERRORHANDLE
END
COMMIT TRAN
RETURN 100
ERRORHANDLE:
ROLLBACK TRAN
RETURN -100
GO
EXEC UP_InsertFruit '苹果','A'
EXEC UP_InsertFruit '红富士','A-01'
SELECT * FROM TABLE1
TypeID TypeName ParentID
A 苹果
A-01 红富士 A
USE [Factory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXX
-- Create date: 2012,4,20
-- Description: 创建物料类别
-- =============================================
CREATE PROCEDURE [dbo].[CreateStoreTypID]
@TypeID NVARCHAR(4),
@TypeName NVARCHAR(50)
AS
BEGIN
DECLARE @MaxTyp NVARCHAR(2) , @ParentID NVARCHAR(4)
SELECT TOP 1 @MaxTyp = TypeID from Mat_TypeInfo where LEFT(TypeID, 1)= @TypeID ORDER BY TypeID DESC
if @MaxTyp = null
--BEGIN
--SELECT ('A')
--END
--ELSE
--BEGIN
--SELECT LEFT(@TypeID, 1) + REPLICATE('0', 2-LEN(CONVERT(INT, RIGHT(@MaxTyp , 2))+1))+CONVERT(NVARCHAR(2), CONVERT(INT, RIGHT(@MaxTyp , 2))+1)
--END
BEGIN
SET @ParentID=null
END
ELSE
BEGIN
SET @ParentID=@TypeID
if @MaxTyp=@TypeID
BEGIN
SET @TypeID=@TypeID+'-01'
END
ELSE
BEGIN
SET @TypeID=@TypeID+'-'+REPLICATE('0', 2-LEN(CONVERT(INT, RIGHT(@MaxTyp , 2))+1))+CONVERT(NVARCHAR(2), CONVERT(INT, RIGHT(@MaxTyp , 2))+1)
END
END
INSERT INTO Mat_TypeInfo VALUES(@TypeID,@TypeName,@PrarentID)
END
USE [Factory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XXX
-- Create date: 2012,4,20
-- Description: 创建物料类别
-- =============================================
CREATE PROCEDURE [dbo].[CreateStoreTypID]
@TypeID NVARCHAR(4),
@TypeName NVARCHAR(50)
AS
BEGIN
DECLARE @MaxTyp NVARCHAR(2) , @ParentID NVARCHAR(4)
SELECT TOP 1 @MaxTyp = TypeID
from Mat_TypeInfo
where LEFT(TypeID, 1)= @TypeID
ORDER BY TypeID DESC
if @MaxTyp = null
--BEGIN
--SELECT ('A')
--END
--ELSE
--BEGIN
--SELECT LEFT(@TypeID, 1) + REPLICATE('0', 2-LEN(CONVERT(INT, RIGHT(@MaxTyp , 2))+1))+CONVERT(NVARCHAR(2), CONVERT(INT, RIGHT(@MaxTyp , 2))+1)
--END
BEGIN
SET @ParentID=null
END
ELSE
BEGIN
SET @ParentID=@TypeID
SET @TypeID=@TypeID+'-'+REPLICATE('0', 2-LEN(CONVERT(INT, RIGHT(@MaxTyp , 2))+1))+CONVERT(NVARCHAR(2), CONVERT(INT, RIGHT(@MaxTyp , 2))+1)
END
INSERT INTO Mat_TypeInfo VALUES(@TypeID,@TypeName,@PrarentID)
END