USE [Laboratory] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[pr_Patient_Insert] @iErrorCode int OUTPUT, @LASID int OUTPUT, @PatientName varchar(50), @Age int, @AgeUnit char(1), @Sex char(1), @DOB datetime, @Priorty char(1), @EnterBy int, @Shift char(1), @PatientType char(1), @Relation varchar(4), @RelativeName varchar(30), @PH# varchar(15), @Email varchar(50), @ReferBy varchar(30), @PUID int=null, @PenalID int, @CashReceiptNo int, @CashReceivedBY varchar(30), @TotalAmount float, @DiscountPtage int, @DiscountBy int AS SET NOCOUNT ON ----------- declare @seed int, @newSeede int; set @seed = IDENT_SEED(''Patient''); set @newSeede = YEar(Getdate())%100*1000000; if (@seed <> @newSeede) BEGIN DBCC CHECKIDENT ( Patient, RESEED,@newSeede ) End if( @PUID is null) BEGIN select @PUID= Max(LASID)+1 from Patient END ------------ INSERT INTO [dbo].[Patient] ( [PatientName], [Age], [AgeUnit], [Sex], [DOB], [Priorty], [EnterBy], [Shift], [PatientType], [Relation], [RelativeName], [PH#], [Email], [ReferBy], [PUID], [PenalID], [CashReceiptNo], [CashReceivedBY], [TotalAmount], [DiscountPtage], [DiscountBy]) VALUES ( @PatientName, @Age, @AgeUnit, @Sex, @DOB, @Priorty, @EnterBy, @Shift, @PatientType, @Relation, @RelativeName, @PH#, @Email, @ReferBy, @PUID, @PenalID, @CashReceiptNo, @CashReceivedBY, @TotalAmount, @DiscountPtage, @DiscountBy ) SELECT @iErrorCode=@@ERROR -- Get the IDENTITY value for the row just inserted. SELECT @LASID=SCOPE_IDENTITY() --endregion
这是我执行此过程时的存储过程
this is my store procedure when i excute this procedure the
DBCC CHECKIDENT ( Patient, RESEED,@newSeede )代码的一部分
part of code
if (@seed <> @newSeede) BEGIN DBCC CHECKIDENT ( Patient, RESEED,@newSeede ) End每次执行并重新排列列,如果陈述为真或为假 当我评论"DBCC CHECKIDENT(Patient,RESEED,@ newSeede)"行时 那么它可以正常工作 请帮助我我做错了什么 谢谢 我在表中的标识字段是LASID,我想自动生成值,但想从每年0开始,年份为后缀,表示2011年从11000000开始,2012年从frme 12000000开始,2013年开始将为13000000 ect我该怎么办
excute every time and reseed the column either the condition of if statment is true or false when i comments the line "DBCC CHECKIDENT ( Patient, RESEED,@newSeede)" then it works properly plz help me what i am doing wrong thanks My identity field in table is LASID and i want to autogenerate the value but want to start from 0 every year with year postfix means in 2011 it start from 11000000 and in 2012 it start frme 12000000 for 2013 start will be 13000000 ect how can i do this.
推荐答案@seed是表中的最高值 @newseede是一个基于GetDate()值在其上方创建的值.它们不相等的变化必须接近100%. 顺便说一下: YEar(Getdate())%100 * 1000000. Year(GetDate())=> 年(GetDate())= 2011 2011%100 = 11 11 * 1000000 = 11000000. 您可能想打印@ seed,@ newseede.如果运行SP,则可以看到该值. 更好的.表格中的身份字段是什么?是否在步骤1中将其设置为身份"字段.然后它会自动为您的字段生成值.容易得多. @seed is the highest value in the table @newseede ia a value created just above it, based on a GetDate() value. The change that they are not equal must be pretty near 100%. By the way you do: YEar(Getdate())%100*1000000. Year(GetDate()) => Year(GetDate()) = 2011 2011 %100 = 11 11 * 1000000 = 11000000. You might wanna do a print @seed, @newseede. If you run the SP you can see the value. Better. What is the Identity Field in the table? Is it set as a Identity field with step 1. It then autogenerates the value for your field. Much easier.
更多推荐
如果在sqlserver存储过程中为else
发布评论