SP-problem - 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

SP-problem

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-08-07 : 15:53:56

Hello,
could anyone find the problem to my Procedure?

CREATE PROCEDURE lagg_till_ny_ravara
		
		@Namn VARCHAR(25),
		@Kategori VARCHAR(25)
		

AS
	
		IF(	SELECT COUNT(ID)
			FROM Råvara
			WHERE Namn = @Namn
			AND Kategori = @Kategori
			GROUP BY ID) >= 1
		
				BEGIN
					-- This one get's Outputed correctly (but togetether with the ELSE):	

				RAISERROR ('Råvaran finns redan!',1,1)
				END
		
		

		IF(	SELECT COUNT(ID)
			FROM Råvara
			WHERE Namn = @Namn
			AND Kategori = @Kategori
			GROUP BY ID) < 1 --OR NULL
				
				BEGIN	
					INSERT INTO Råvara (Namn, Kategori)
					VALUES (@Namn, @Kategori)
					
					-- This one never get't output. This is because the COUNT is NOT 0, it is NOT NULL. What is it? How should I compare it?
					RAISERROR ('Tillägg lyckades!',1,1)
				END
		

		ELSE
			
				BEGIN
					-- Why is this always being outputted?	
				RAISERROR ('Något galet hände!!',1,1)
				END

pootle_flump

1064 Posts

Posted - 2008-08-07 : 16:22:16

1) Your first IF is totally distinct from the other conditions. You need to precede the second IF with an ELSE.
2) Don't use a count - inefficient when it is >1 and also prone to the sort of bug you've found.

	IF EXISTS (	SELECT *
			FROM Råvara
			WHERE Namn = @Namn
			AND Kategori = @Kategori)

kfluffie
Posting Yak Master

103 Posts

Posted - 2008-08-11 : 08:10:48

Thanks for the assistance!

Here is the complete code for you interested:

CREATE PROCEDURE lagg_till_ny_ravara
		
		@Namn VARCHAR(25),
		@Kategori VARCHAR(25)
		

AS
	
		IF EXISTS (	SELECT *
					FROM Råvara
					WHERE RTRIM(LTRIM(Namn)) = RTRIM(LTRIM(@Namn))
					AND RTRIM(LTRIM(Kategori)) = RTRIM(LTRIM(@Kategori))
					GROUP BY ID)
		
				BEGIN
					
					RAISERROR ('Råvaran finns redan!',1,1)
				END
		
		

		ELSE IF NOT EXISTS (	
					SELECT *
					FROM Råvara
					WHERE RTRIM(LTRIM(Namn)) = RTRIM(LTRIM(@Namn))
					AND RTRIM(LTRIM(Kategori)) = RTRIM(LTRIM(@Kategori))
					GROUP BY ID)

				BEGIN	
					INSERT INTO Råvara (Namn, Kategori)
					VALUES (@Namn, @Kategori)
					
				
					RAISERROR ('Tillägg lyckades!',1,1)
				END
		

		ELSE
			
				BEGIN
					RAISERROR ('Något galet hände!!',1,1)
				END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 08:19:00

Didnt understand the purpose of last ELSE block??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 08:20:59

Instead of nested IF's, just do a RETURN along with error message.

CREATE PROCEDURE dbo.usp_Lagg_till_ny_ravara
(
	@Namn VARCHAR(25),
	@Kategori VARCHAR(25)
)	
AS

SET NOCOUNT ON

IF EXISTS	(
			SELECT	*
			FROM	Råvara
			WHERE	LTRIM(Namn) = LTRIM(@Namn)
				AND LTRIM(Kategori) = LTRIM(@Kategori)
		)
BEGIN
	RAISERROR('Råvaran finns redan!', 16, 1)
	RETURN -100
END
		
INSERT	Råvara
	(
		Namn,
		Kategori
	)
VALUES	(
		@Namn,
		@Kategori
	)

IF @@ERROR = 0
	BEGIN
		RAISERROR ('Tillägg lyckades!', 10, 1)
		RETURN 0
	END

RAISERROR ('Något galet hände!!', 18, 1)
RETURN -200

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources