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
 Problem with IF-statement!

Author  Topic 

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-26 : 20:09:01
Hello again!
I have a little problem with this code:
ALTER PROCEDURE lagg_till_ny_enhetsomvandling (@fran_enhet varchar(50), @till_enhet varchar(50), @faktor decimal(10,2), @ravara varchar(50))
AS

DECLARE 
@fran_enhet_id int,
@till_enhet_id int,
@ravara_id int

IF(@ravara = '')
BEGIN	
		SET @ravara = NULL
		print 'NULLY'	
END


IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet)))
	
	BEGIN
		PRINT @fran_enhet + ' finns inte som en inlagd enhet!'
	END

ELSE IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@till_enhet)))
	
	BEGIN
		PRINT @till_enhet + ' finns inte som en inlagd enhet!'
	END

ELSE IF NOT EXISTS (SELECT * FROM dbo.Råvara
				WHERE LTRIM(RTRIM(@ravara)) = LTRIM(RTRIM(namn))
				AND @ravara IS NOT NULL)
	
	BEGIN
		PRINT @ravara + ' finns inte inlagd. Lägg till råvaran först.'
	END				


ELSE IF EXISTS
	(SELECT * FROM dbo.Enhet_till_Enhet ETE
	INNER JOIN Enhet E1 ON ETE.från_enhet_id = E1.id
	INNER JOIN Enhet E2 ON ETE.till_enhet_id = E2.id
	WHERE E1.Namn = @fran_enhet
	AND E2.Namn = @till_enhet)
	
	BEGIN
		PRINT 'Det finns redan en enhetsomvandling mellan dessa två enheter.'
	END


ELSE
BEGIN
SET @ravara_id = (SELECT id FROM dbo.Råvara WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@ravara)))
SET @fran_enhet_id = (SELECT id FROM dbo.enhet WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@fran_enhet)))
SET @till_enhet_id = (SELECT id FROM dbo.enhet WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@till_enhet)))


	INSERT INTO dbo.Enhet_till_Enhet (faktor, råvara_id, från_enhet_id, till_enhet_id)
	VALUES (@faktor, @ravara_id, @fran_enhet_id, @till_enhet_id)
		
		PRINT 'Insatt!'

END



The exact problem is (what I think) this piece of code:
IF(@ravara = '')
BEGIN	
		SET @ravara = NULL
		print 'NULLY'	
END


When I run this procedure:
USE [Matlagning]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[lagg_till_ny_enhetsomvandling]
		@fran_enhet = N'msk',
		@till_enhet = N'tsk',
		@faktor = 25,
		@ravara = N''

SELECT	'Return Value' = @return_value

GO


This is the output (which means that @ravara = '' is fulfilled):
NULLY
 
Return Value
------------
0

(1 row(s) affected)


However, I though that after the IF(@ravara = '') have been fulfilled and all the operations have been processed it should move on to the next IF-statement as they are two different "batches of code", which should be this piece of code:
IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet)))
	
	BEGIN
		PRINT @fran_enhet + ' finns inte som en inlagd enhet!'
	END


Do any of you have any input on this?

Best Regards,
KFluffie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-26 : 23:50:49
why do you think IF is not getting executed? are you sure no records exists which satisfy condition SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet))?
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 06:10:45
quote:
Originally posted by visakh16

why do you think IF is not getting executed? are you sure no records exists which satisfy condition SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet))?


Hello Visakh!
SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(''))

id          namn                                               region
----------- -------------------------------------------------- --------------------------------------------------

(0 row(s) affected)



At the same time, shouldn't there be any more PRINT if any of the other statement are executed? Right now the only PRINT, which is executed, is 'NULLY' but I have printstatement after all IF-statements.

When I set @ravara to NULL statically it doesen't either give me any more output from the other PRINT statements:
SET @ravara = NULL

/*IF(@ravara = '')
BEGIN	
		SET @ravara = NULL
		print 'NULLY'	
END*/

Return Value
------------
0

(1 row(s) affected)


I think the IF-statements are executed but I don't know why there is no information from one of all the PRINT-statements.

Best Regards,
KFluffie
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2009-01-27 : 06:22:28
Please replace all @ravara with this isnull(@ravara,'')
and check once,


Karthik
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2009-01-27 : 06:34:10
quote:
Originally posted by kfluffie

quote:
Originally posted by visakh16

why do you think IF is not getting executed? are you sure no records exists which satisfy condition SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet))?


Hello Visakh!
SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(''))

id          namn                                               region
----------- -------------------------------------------------- --------------------------------------------------

(0 row(s) affected)



At the same time, shouldn't there be any more PRINT if any of the other statement are executed? Right now the only PRINT, which is executed, is 'NULLY' but I have printstatement after all IF-statements.

When I set @ravara to NULL statically it doesen't either give me any more output from the other PRINT statements:
SET @ravara = NULL

/*IF(@ravara = '')
BEGIN	
		SET @ravara = NULL
		print 'NULLY'	
END*/

Return Value
------------
0

(1 row(s) affected)


I think the IF-statements are executed but I don't know why there is no information from one of all the PRINT-statements.

Best Regards,
KFluffie


SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM('msk'))

are you sure there is no record exist for this condition?


Karthik
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 06:38:15
quote:
Originally posted by karthik_padbanaban

Please replace all @ravara with this isnull(@ravara,'')
and check once,


Karthik


Hello Karthik!
That seems to work:
ELSE IF NOT EXISTS (SELECT * FROM dbo.Råvara
				WHERE LTRIM(RTRIM(isnull(@ravara,''))) = LTRIM(RTRIM(namn))
				AND @ravara IS NOT NULL)
	
	BEGIN
		PRINT isnull(@ravara,'') + ' finns inte inlagd. Lägg till råvaran först. (ravara)'
	END


I am not sure but it seems that this is enough:
ELSE IF NOT EXISTS (SELECT * FROM dbo.Råvara
				WHERE LTRIM(RTRIM(@ravara)) = LTRIM(RTRIM(namn))
				AND @ravara IS NOT NULL)
	
	BEGIN
		PRINT isnull(@ravara,'') + ' finns inte inlagd. Lägg till råvaran först. (ravara)'
	END


Which is that only @ravara have isNull() during the PRINT-statement.

However, this IF-statement shouldn't be executed as as "@ravara IS NOT NULL"?

The complete code once again:
http://pastebin.com/m66faecc6

Best Regards,
KFluffie
Go to Top of Page

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts
Posted - 2009-01-27 : 07:40:09
SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM('msk'))

are you sure there is no record exist for this condition?


Karthik
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 07:59:38
quote:
Originally posted by karthik_padbanaban

SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM('msk'))

are you sure there is no record exist for this condition?


Karthik


Hello!
That record does exist:
id          namn                                               region
----------- -------------------------------------------------- --------------------------------------------------
11          msk                                                EU                                                

(1 row(s) affected)


What I mean is that, that is the next execution after the IF(@ravara = '') and it should pass on to the next ELSE IF statement.
However, I though that after the IF(@ravara = '') have been fulfilled and all the operations have been processed it should move on to the next IF-statement as they are two different "batches of code", which should be this piece of code:

IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet)))
	
	BEGIN
		PRINT @fran_enhet + ' finns inte som en inlagd enhet!'
	END



Best Regards,
Tomas
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-27 : 08:49:56
once IF condition is true it wont go to ELSE block and so all IF inside ELSE wont be executed.
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 10:22:57
quote:
Originally posted by visakh16

once IF condition is true it wont go to ELSE block and so all IF inside ELSE wont be executed.


Hello!
Thanks for all your help.

Visakh, the first IF(@Ravara = '') is one "batch" and does not consider the ELSE-IF statements, correct? (Because the second IF (IF NOT EXISTS (SELECT * FROM dbo.enhet
WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet)))
) belongs to the ELSE-IF).


Please, see this picture:
http://www.ladda-upp.com/bilder-a/sql_proc-36.JPG


Why is the output as it is when it sais "AND @ravara IS NOT NULL". It shouldnäe be fulfilled there, right?

Best Regards,
KFluffie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-27 : 12:10:33
whats the output that you expect? you are not returning anything from procedure. e you trying to return value of @ravara? then you need to make it OUTPUT parameter.
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 12:20:06
quote:
Originally posted by visakh16

whats the output that you expect? you are not returning anything from procedure. e you trying to return value of @ravara? then you need to make it OUTPUT parameter.


The question is that I don't know why this is being outputted as @ravara IS NULL and that "ELSE IF" shouldn't be fulfilled.

The output should be "Insatt!" and also a new record in the table enhet_till_enhet.

Best Regards,
KF
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-27 : 12:22:04
what does this return?

SELECT * FROM dbo.enhet	WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet))
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 12:41:10
quote:
Originally posted by visakh16

what does this return?

SELECT * FROM dbo.enhet	WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet))



That gives an output:
SELECT * FROM dbo.enhet	WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM('msk'))
id          namn                                               region
----------- -------------------------------------------------- --------------------------------------------------
11          msk                                                EU


But shouldn't @ravara make the statement false?

Best Regards,
KF
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-27 : 12:47:04
what about other selects inside other ELSE IF's? do they all return records?
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-27 : 13:42:55
quote:
Originally posted by visakh16

what about other selects inside other ELSE IF's? do they all return records?


DECLARE	@return_value int

EXEC	@return_value = [dbo].[lagg_till_ny_enhetsomvandling]
		@fran_enhet = N'dont exist',
		@till_enhet = N'tsk',
		@faktor = 2,
		@ravara= ''

SELECT	'Return Value' = @return_value


Correct output:
IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@fran_enhet)))
	
	BEGIN
		PRINT @fran_enhet + ' finns inte som en inlagd enhet! (fran enhet)'
	END
(1 row(s) affected)
dont exist finns inte som en inlagd enhet! (fran enhet)
Return Value
------------





---------------------------------
DECLARE	@return_value int

EXEC	@return_value = [dbo].[lagg_till_ny_enhetsomvandling]
		@fran_enhet = N'msk',
		@till_enhet = N'dont exist',
		@faktor = 2,
		@ravara= ''


Correct output:
ELSE IF NOT EXISTS (SELECT * FROM dbo.enhet
				WHERE LTRIM(RTRIM(Namn)) = LTRIM(RTRIM(@till_enhet)))
	
	BEGIN
		PRINT @till_enhet + ' finns inte som en inlagd enhet! (till enhet)'
	END

(1 row(s) affected)


(1 row(s) affected)
dont exist finns inte som en inlagd enhet! (till enhet)




----------------------------
DECLARE	@return_value int

EXEC	@return_value = [dbo].[lagg_till_ny_enhetsomvandling]
		@fran_enhet = N'msk',
		@till_enhet = N'tsk',
		@faktor = 2,
		@ravara= 'Gul Lök'

SELECT	'Return Value' = @return_value



Correct output:
ELSE IF EXISTS
	(SELECT * FROM dbo.Enhet_till_Enhet ETE
	INNER JOIN Enhet E1 ON ETE.från_enhet_id = E1.id
	INNER JOIN Enhet E2 ON ETE.till_enhet_id = E2.id
	WHERE E1.Namn = @fran_enhet
	AND E2.Namn = @till_enhet)
	
	BEGIN
		PRINT 'Det finns redan en enhetsomvandling mellan dessa två enheter.'
	END


(1 row(s) affected)
Det finns redan en enhetsomvandling mellan dessa två enheter.
Return Value
------------
0


---------------------------

DECLARE	@return_value int

EXEC	@return_value = [dbo].[lagg_till_ny_enhetsomvandling]
		@fran_enhet = N'msk',
		@till_enhet = N'dl',
		@faktor = 3,
		@ravara= 'paprika'

SELECT	'Return Value' = @return_value



Correct output:
ELSE
BEGIN
SET @ravara_id = (SELECT id FROM dbo.Råvara WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@ravara)))
SET @fran_enhet_id = (SELECT id FROM dbo.enhet WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@fran_enhet)))
SET @till_enhet_id = (SELECT id FROM dbo.enhet WHERE LTRIM(RTRIM(namn)) = LTRIM(RTRIM(@till_enhet)))


	INSERT INTO dbo.Enhet_till_Enhet (faktor, råvara_id, från_enhet_id, till_enhet_id)
	VALUES (@faktor, @ravara_id, @fran_enhet_id, @till_enhet_id)
		
		PRINT 'Insatt!'

END

Insatt!
Return Value
------------
0




The rest seems to be working correct.

Best Regards,
KFluffie
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-28 : 02:23:46
pass parameter values such that records exist for all the selects inside not exists() conditions. then i think it will work
Go to Top of Page

kfluffie
Posting Yak Master

103 Posts
Posted - 2009-01-28 : 05:48:31
quote:
Originally posted by visakh16

pass parameter values such that records exist for all the selects inside not exists() conditions. then i think it will work


I have confused my self a lot with thise procedure.

I changed the code to this and it works:
ELSE IF (@ravara IS NOT NULL)
	BEGIN
	IF NOT EXISTS (SELECT * FROM dbo.Råvara
				WHERE LTRIM(RTRIM(@ravara)) = LTRIM(RTRIM(namn))				)
	
	BEGIN
		PRINT @ravara + ' finns inte inlagd. Lägg till råvaran först.'
	END				
	END


I though that the criteria wouldn't even be treated because the "@ravara IS NOT NULL" wasn't included, but of course it does and the state (NOT EXISTS) is fulfilled.

Thanks for all your help!

Best Regards,
KFluffie
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site