SQL Stored Procedure Call

Use [changhong_612]
GO
/****** object:storedprocedure [dbo]. [St_mes_getcoderule] Script date:09/10/2015 17:44:29 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]. [St_mes_getcoderule] (
@in_CodeRuleNamevarchar (50) = "--rule name
, @in_BillNOvarchar (50) = "--Incoming document encoding
, @in_Uservarchar (20) = "
, @OutCodevarchar () = ' Output
, @ShowRsbit = 1
)
/*************
exec [St_mes_getcoderule] ' mouldno ', '
SELECT * FROM Sys_coderulechild
Creator:wuchun
Create date:2012.11.06
Remark: Generate code according to coding rules
**************/
As
Begin
DECLARE @code varchar ($), @DeptCode varchar, @SNO varchar (20)
declare @RuleID int, @CodeRuleNo varchar (@InitValue varchar), @length int, @Level int
Select @RuleID = RuleID from sys_coderule where coderuleename = @in_CodeRuleName and state = 1
Select @Code = ', @DeptCode = ', @SNO = '
DECLARE cur cursor FOR
Select Coderuleno, InitValue, length, level
From Sys_coderulechild
where RuleID = @RuleID
ORDER BY level
Open cur
FETCH NEXT from cur to @CodeRuleNo, @InitValue, @length, @Level
while (@ @fetch_Status = 0)
Begin
if (@CodeRuleNO = ' Date ')
Select @Code = @Code +convert (varchar (8), GETDATE (), 112)
else if (@CodeRuleNO = ' Billno ')
Select @Code = @Code + RTrim (@in_BillNO)
else if (@CodeRuleNO = ' SN ')--serial number
Begin
EXEC st_mes_getcodeinitvalue @SNO output, @RuleID, @Level, @CodeRuleNO, @Length, @InitValue
Select @Code = @Code + RTrim (@SNO)
End
else if (@CodeRuleNO = ' Define ')
Select @Code = @Code + RTrim (@InitValue)
else if (@CodeRuleNO = ' EmpID ')
Select @Code = @Code + RTrim (@in_user)
else if (@CodeRuleNO = ' Deptcode ')
Begin
Select @DeptCode = Deptcode from sys_user where usercode = @in_User
Select @Code = @Code + @DeptCode
End
FETCH NEXT from cur to @CodeRuleNo, @InitValue, @length, @Level
End
Close cur
Deallocate cur

--output
Select @OutCode = @Code
If @ShowRs = 1
Select Code = @Code
End

----------------------------------------------------------------

Use [changhong_612]
GO
/****** object:storedprocedure [dbo]. [St_mes_getcodeinitvalue] Script date:09/10/2015 17:46:05 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]. [St_mes_getcodeinitvalue] (
@retvarchar () output
, @in_RuleIDint
, @in_Levelint
, @in_CodeRuleNOvarchar (20) = "
, @in_Lengthint
, @in_InitValuevarchar (20) = "
)
/*************
Creator:wuchun
Create date:2012.11.06
Remark: By encoding rule serial number value
**************/
As
Begin
BEGIN Tran
if (Not EXISTS (select 1 from Sys_coderulevalue
where RuleId = @in_RuleID and level = @in_Level and Coderuleno = @in_CodeRuleNO))
Begin
If @in_InitValue = ' '
Select @in_InitValue = ' 1 '
Else
Begin
If IsNumeric (@in_InitValue) = 1
Select @in_InitValue = convert (int, @in_InitValue) +1
Else
Select @in_InitValue = ' 1 '
End

Insert into Sys_coderulevalue (Ruleid,coderuleno, Level,value,updatetime)
Select @in_RuleID, @in_CodeRuleNO, @in_Level, @in_InitValue, GETDATE ()
End
Else
Begin
Select @in_InitValue = value
From Sys_coderulevalue with (Rowlock,xlock)
where RuleId = @in_RuleID and level = @in_Level
and Coderuleno = @in_CodeRuleNO
Select @in_InitValue = convert (int, @in_InitValue) +1
Update Sys_coderulevalue with (rowlock) Set value = @in_InitValue, UpdateTime = getdate ()
where RuleId = @in_RuleID and level = @in_Level
and Coderuleno = @in_CodeRuleNO
End
Commit Tran
Select @ret = replace (str (right (@in_InitValue, @in_Length), @in_Length), Space (1), ' 0 ')

End

SQL Stored Procedure Call