Auto increment column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto increment column

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2009-12-29 : 02:56:16

Hi,

I have the following table:

CREATE TABLE [dbo].[kundeservice_lopenummer](
	[Lopenummer] [bigint] IDENTITY(1,1) NOT NULL,
	[Operator] [bigint] NOT NULL,
	[Kundenummer] [int] NULL,
	[utfall] [nvarchar](50) NULL,
	[Tidspunkt] [datetime] NOT NULL CONSTRAINT [DF_kundeservice_lopenummer_Tidspunkt]  DEFAULT (getdate()),
	[case_id] [bigint] NOT NULL
) ON [PRIMARY]

I want to be able to get a new case_id, but not every time. Basically, I'm trying to create a stored procedure that will increment the previous max case_id and return it to me. However, SQL must be wrong, because it doesn't work! (hehe...I know - I'm broken, not SQL) :)

This is where I'm at

CREATE PROCEDURE getNewCaseID 
	-- Add the parameters for the stored procedure here
	@operator bigint,
	@kundenummer int,
	@utfall nvarchar(50),
	@tidspunkt datetime,
	@case_id bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

select MAX(case_id) as prev_max_case_id
    -- Insert statements for procedure here
	INSERT INTO [viasatWebserv].[dbo].[kundeservice_lopenummer]
           ([operator]
           ,[kundenummer]
           ,[utfall]
           ,[Tidspunkt]
		   ,[case_id]
)
     VALUES
           (@operator
           ,@kundenummer
           ,@utfall
           ,getdate()
		   ,@case_id) 

	SELECT	SCOPE_IDENTITY() as case_id
END
GO

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-29 : 04:20:43

hi,

i think this is ur requirement.

scopeidentity only returns the laster incremented identity column value.

CREATE PROCEDURE getNewCaseID
-- Add the parameters for the stored procedure here
@operator bigint,
@kundenummer int,
@utfall nvarchar(50),
@tidspunkt datetime,
@case_id bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @var = MAX(case_id) as prev_max_case_id
-- Insert statements for procedure here
INSERT INTO [viasatWebserv].[dbo].[kundeservice_lopenummer]
([operator]
,[kundenummer]
,[utfall]
,[Tidspunkt]
,[case_id]
)
VALUES
(@operator
,@kundenummer
,@utfall
,getdate()
,@case_id)

SELECT @var as case_id
END

Thanks,
vikky.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 01:51:37

quote:
Originally posted by vikky

hi,

i think this is ur requirement.

scopeidentity only returns the laster incremented identity column value.

CREATE PROCEDURE getNewCaseID
-- Add the parameters for the stored procedure here
@operator bigint,
@kundenummer int,
@utfall nvarchar(50),
@tidspunkt datetime,
@case_id bigint
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @var = MAX(case_id) as prev_max_case_id FROM [viasatWebserv].[dbo].[kundeservice_lopenummer]
-- Insert statements for procedure here
INSERT INTO [viasatWebserv].[dbo].[kundeservice_lopenummer]
([operator]
,[kundenummer]
,[utfall]
,[Tidspunkt]
,[case_id]
)
VALUES
(@operator
,@kundenummer
,@utfall
,getdate()
,@case_id)

SELECT @var as case_id
END

Thanks,
vikky.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources