How could this procedure work.

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 2000 Forums

Transact-SQL (2000)

How could this procedure work.

Author

Topic

Valentin-
Starting Member

7 Posts

Posted - 2003-04-29 : 14:19:05

CREATE PROCEDURE adm_showdns
(
@ProductID INT
)
AS
DECLARE @Kacka INT
IF EXISTS
(
SELECT @Kacka = COUNT(*) FROM default_dns WHERE product_id = @ProductID
)
THEN BEGIN

IF(@Kacka = 2)
THEN BEGIN
SELECT * FROM default_dns WHERE product_id = @ProductID
END
ELSE
DELETE FROM default_dns WHERE product_id = @ProductID
END
END
GO

Please help me!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 14:28:02

Well, what is the problem that you are having, what is the error?

Just glancing at it, I see one problem. You can not use THEN in IF/ELSE. Please see BOL for the correct syntax, but this should work for you assuming that your problem is with THEN:

CREATE PROCEDURE adm_showdns
(
@ProductID INT
)
AS
DECLARE @Kacka INT
IF EXISTS (SELECT @Kacka = COUNT(*) FROM default_dns WHERE product_id = @ProductID)
BEGIN
IF(@Kacka = 2)
BEGIN
SELECT * FROM default_dns WHERE product_id = @ProductID
END
ELSE
DELETE FROM default_dns WHERE product_id = @ProductID
END
END

RETURN

Tara

Valentin-
Starting Member

7 Posts

Posted - 2003-04-29 : 14:37:10

Okay like this I thought it should work.
CREATE PROCEDURE adm_showdns
(
@ProductID INT
)
AS
DECLARE @Kacka INT
IF EXISTS (SELECT @Kacka = COUNT(*) FROM default_dns WHERE product_id = @ProductID)
BEGIN
IF(@Kacka = 2)
BEGIN
SELECT * FROM default_dns WHERE product_id = @ProductID
END
ELSE
DELETE FROM default_dns WHERE product_id = @ProductID
END

But he sais that:
Server: Msg 170, Level 15, State 1, Procedure adm_showdns, Line 7
Line 7: Incorrect syntax near '='.

But this should we right or?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 14:41:36

You are not using IF EXISTS correctly. Actually you don't need IF EXISTS at all for this.

BTW, you do not need to use BEGIN/END when there is only one statement in the IF or ELSE.

I think this should work:

CREATE PROCEDURE adm_showdns
(
@ProductID INT
)
AS

IF ((SELECT COUNT(*) FROM default_dns WHERE product_id = @ProductID) = 2)
SELECT * FROM default_dns WHERE product_id = @ProductID
ELSE
DELETE FROM default_dns WHERE product_id = @ProductID

RETURN

Tara

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 14:46:32

Just curious,

Why do you want to delete single instances and show products that have two entries?

Valentin-
Starting Member

7 Posts

Posted - 2003-04-29 : 14:51:50

Because it would not fit with my instances I need. If I got 2
it fits. Otherwise if I have more or less than 2 I use some default
values and insert them manually into the DB.

bye

and thx to all of you

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 14:54:03

You need to rows for a "key" Why not just make 1 row, have a unque, keep the fields nullable and do away with your problem?

Brett

8-)

Valentin-
Starting Member

7 Posts

Posted - 2003-04-30 : 01:41:52

Would have done that but the table structure was made of someone
else and I have to use it. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources