EXISTS

[arpan]

I have framed the following procedure to find out whether the parameter that is being passed to the procedure exists in the ID column of a table or not:

CREATE OR REPLACE PROCEDURE ExistsOrNot(
iID INT)
IS
intID INT;
BEGIN
SELECT ID INTO intID FROM tblName WHERE ID=iID;
IF EXISTS(intID) THEN
DBMS_OUTPUT.PUT_LINE('ID EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('ID DOES NOT EXIST');
END IF;
END;

but the above throws a compilation error saying Encountered the symbol "INTID" when expecting one of the following :
( select

Where am I going wrong? I know that NO_DATA_FOUND can be used in this case but I would like to know how do I make use of EXISTS to do the same.

Thanks,

Arpan

 

[Abhivyakti]

hi arpan,
EXISTS works out in the query along with Where clause. So try using it in the where clause. It will not work with exists i guess. As your query will bring only true or false when u use exists in the where clause.... u can then check, if true give a msg otherwise give another msg.

hope this works
Abhivyakti

 

[sem]

No, you can not. EXISTS may be applied to subqueries or pl/sql table records. Where did you find this syntax? Regards, Dima

 

[DaPi]

SELECT count(*) INTO something FROM tblName WHERE ID=iID;
and test on that. DaPi - no silver bullet

 

[arpan]

Hi Dima,

Actually what I have shown in my post (the IF condition) is allowed in Microsoft SQL Server......something like this:

IF EXISTS (SELECT ID FROM tblName WHERE ID=@id)
PRINT ('ID EXISTS')
ELSE
PRINT ('ID DOES NOT EXIST&quot
............

where @id is the parameter passed to the procedure.So that is the reason why I tried something similar in Oracle. I am more into SQL Server than Oracle. Anyway, thanks for your response. Thanks to Abhivyakti as well.

Regards,

Arpan