如果在sqlserver存储过程中为else [英] IF else in sqlserver storeprocedure

问题描述

 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！