---
title: "Stored procedure Insert med CASE"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/88612-stored-procedure-insert-med-case"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "SteveP"
published: "2003-10-16T11:03:01.000Z"
updated: "2003-10-16T14:53:37.000Z"
replies: 3
views: 189
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/88612-stored-procedure-insert-med-case"
---

# Stored procedure Insert med CASE

## #1 — SteveP, 2003-10-16T11:03Z

Jag verkar inte få köra ett case i inserten, problemet är i BOLD.

```
CREATE PROCEDURE spLCPFiles_Insert1
	
	@ContactID 	INT,
	@OpenDate	DATETIME,
	@Client		INT,
	@SubCode	Char(2),
	@FileType	INT

AS

Declare
@Sequence	INT

BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION

SELECT @Sequence = coalesce(max(Sequence),0) + 1

From tblLCPFiles

with (HoldLock)
Where
OrgID = @Client And FileTypeID = @FileType And SubCode = @SubCode

INSERT INTO TblLCPFiles
	(
	ContactID,
	OpenDate,
	OrgID,
	SubCode,
	FileTypeID,
	Sequence
	)

VALUES
	(
	@ContactID,
	@OpenDate,
	@Client,
	@SubCode,
	@FileType,
	[b]Case
	        when Sequence is Null
	        then @Sequence
	        Else Sequence
	end[/b]
	)

SELECT @@IDENTITY AS NewID

COMMIT
END
GO
```

Permalänk: https://www.webforum.nu/p/88612

## #2 — SteveP, 2003-10-16T11:08Z

Det verkade iofs räcka att enbart använda @sequence.
Jag som tänkte fel

```
CREATE PROCEDURE spLCPFiles_Insert1
	
	@ContactID 	INT,
	@OpenDate	DATETIME,
	@Client		INT,
	@SubCode	Char(2),
	@FileType	INT

AS

Declare
@Sequence	INT

BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION

SELECT @Sequence = coalesce(max(Sequence),0) + 1

From tblLCPFiles

with (HoldLock)
Where
OrgID = @Client And FileTypeID = @FileType And SubCode = @SubCode

INSERT INTO TblLCPFiles
	(
	ContactID,
	OpenDate,
	OrgID,
	SubCode,
	FileTypeID,
	Sequence
	)

VALUES
	(
	@ContactID,
	@OpenDate,
	@Client,
	@SubCode,
	@FileType,
	@Sequence
	)

SELECT @@IDENTITY AS NewID

COMMIT
END
GO
```

Permalänk: https://www.webforum.nu/p/1188027

## #3 — SteveP, 2003-10-16T11:12Z

Nej förresten.
Om Sequence är "Null" så ska den plussa på ett, alltså använda @sequence annars ska den använda samma värde som sequence returnerade. Alltså om den inte är null så ska den använda max(sequence) annars ska den använda @sequence

```
CREATE PROCEDURE spLCPFiles_Insert1
	
	@ContactID 	INT,
	@OpenDate	DATETIME,
	@Client		INT,
	@SubCode	Char(2),
	@FileType	INT

AS

Declare
@Sequence	INT

BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION

SELECT @Sequence = coalesce(max(Sequence),0) + 1

From tblLCPFiles

with (HoldLock)
Where
OrgID = @Client And FileTypeID = @FileType And SubCode = @SubCode

INSERT INTO TblLCPFiles
	(
	ContactID,
	OpenDate,
	OrgID,
	SubCode,
	FileTypeID,
	Sequence
	)

VALUES
	(
	@ContactID,
	@OpenDate,
	@Client,
	@SubCode,
	@FileType,
	[b]Case
	        when Sequence is Null
	        then @Sequence
	        Else Sequence
	end[/b]
	)

SELECT @@IDENTITY AS NewID

COMMIT
END
GO
```

Permalänk: https://www.webforum.nu/p/1188033

## #4 — LarsG, 2003-10-16T14:53Z

```
SELECT @Sequence = max(Sequence) + 1

From tblLCPFiles

with (HoldLock)
Where OrgID = @Client 
And FileTypeID = @FileType 
And SubCode = @SubCode

INSERT INTO TblLCPFiles
	(
	ContactID,
	OpenDate,
	OrgID,
	SubCode,
	FileTypeID,
	Sequence
	)
VALUES
	(
	@ContactID,
	@OpenDate,
	@Client,
	@SubCode,
	@FileType,
	Coalesce(@Sequence,1)
	)
```

Permalänk: https://www.webforum.nu/p/1188212

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/88612-stored-procedure-insert-med-case