IF and UNION ALL statements help

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

SQL Server 2005 Forums

Transact-SQL (2005)

IF and UNION ALL statements help

Author

Topic

grasuxxxl
Starting Member

3 Posts

Posted - 2008-12-01 : 03:43:52

Hy i have a problem, i hope you guys can help me out here,
i have a code like this:

INSERT INTO PollOptions (FK_PollId, Answer, Votes)
SELECT 2, @answer1 ,0
UNION ALL
SELECT 2, @answer2 ,0
UNION ALL
SELECT 2, @answer3 ,0
UNION ALL
SELECT 2, @answer4 ,0
UNION ALL
SELECT 2, @answer5 ,0

I'd like to write a IF statement like so:

IF EXISTS(@answer3)
BEGIN
UNION ALL
SELECT 2, @answer3 ,0
END

and can't seem to get it right, i get errors
PS: i kinda new to this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:48:30

[code]INSERT INTO PollOptions (FK_PollId, Answer, Votes)
SELECT 2, @answer1 ,0
UNION ALL
SELECT 2, @answer2 ,0
UNION ALL
SELECT 2, @answer3 ,0
WHERE EXISTS(@answer3)
UNION ALL
SELECT 2, @answer4 ,0
WHERE EXISTS(@answer4)
UNION ALL
SELECT 2, @answer5 ,0
WHERE EXISTS(@answer5)
....[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-01 : 04:41:08

EXISTS(@answer3)

should be

WHERE EXISTS(select * from PollOptions where Answer=@answer3)

Madhivanan

Failing to plan is Planning to fail

grasuxxxl
Starting Member

3 Posts

Posted - 2008-12-01 : 09:11:10

10x for the help
but i thougt this trough and came up with a solution on my own

IF NOT @answer3 IS NULL
BEGIN;
INSERT INTO table1 (col0, col1, col3)
VALUES (@parameter0, @parameter1 ,0);
END;

and at the begging of the SP i gave default value to @parameter1 = NULL;

hope this helps someone else

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:17:50

quote:
Originally posted by grasuxxxl

10x for the help
but i thougt this trough and came up with a solution on my own

IF NOT @answer3 IS NULL
BEGIN;
INSERT INTO table1 (col0, col1, col3)
VALUES (@parameter0, @parameter1 ,0);
END;

and at the begging of the SP i gave default value to @parameter1 = NULL;

hope this helps someone else

the above is same as @answer3 IS NOT NULL

grasuxxxl
Starting Member

3 Posts

Posted - 2008-12-02 : 08:22:46

yes, but now i gave the parameter the default value NULL and didn't use UNION ALL cause it gave some kind of error, anyway... this way it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources