Query No1

Hi

I have a table Topics which contains Topics Id and Topic Name.

Now i have two strings 'strTopicId' & 'strTopicName'. I need to check whether strTopicId and strTopicName exist in the table or not.

Since TopicId is the Primary Key, so i think a better way to validate would be first to check for the TopicID and then check its corresponding TopicName and see whether it also matches.
You can show me the SP or Query the way it can be done. I think we can return a '1' if its present and a '0' if its not present. I would be very grateful for any help. Thanks a lot.

Regards,

Nab

Hmm, you are asking help to write a stored procedure. Dont you think its not a right place to ask about stored procedures.

Anyway,

CREATEORREPLACEPROCEDURE Sp_Chk_Topic

(

topicID IN"Table_Name"."TopicIDFieldName"%TYPE,

topicName IN"Table_Name"."TopicNameFieldName"%TYPE,

retValueOUTVARCHAR2

)

AS

c_counterNUMBER;

BEGIN

SELECT NVL(COUNT(*),0)INTO c_counterFROMTABLE_NAMEWHERE "topicIDFieldName" = UPPER(topicID) andtopicNameFieldName" = UPPER(topicName)

IF( c_counter<>0)THEN

retValue:='01';--Name matched';

RETURN;

ELSE

retValue:='02';--Name did NOT matched';

RETURN;

ENDIF;

EXCEPTION

WHENOTHERSTHEN

retValue:='03';--'Error Occured getting the data';

RETURN;

END Sp_Chk_Topic;

/

|||

Hi

Thanks a lot, Mr Kumar. I mistakenly posted it here. This should have been in the Sql Sectioin. If it can be moved, it should be.

However i modified ur SP and am still recieving some errors. Could u plz explain the reason of errors.

CREATE PROCEDURE CheckThread
(
@.TopicID IN Topics.TopicId int(4),

@.TopicName IN Topics.TopicName varchar(50),

@.retValue OUT VARCHAR

)

AS

c_counter NUMBER;

BEGIN

SELECT NVL(COUNT(*), 0) INTO c_counter FROM Topics WHERE TopicId = UPPER(TopicID) and TopicName = UPPER(TopicName)

IF( c_counter <> 0 ) THEN

retValue = '01'; --Name matched';

RETURN;

ELSE

retValue = '02'; --Name did NOT matched';

RETURN;

END IF;

EXCEPTION

WHEN OTHERS THEN

retValue = '03'; --'Error Occured getting the data';

RETURN;

END CheckThread;

1)Incorrect Syntax near IN "in line 3"

2)NVL is not a recognized f() name.

3)Incorrect syntax near the keyword 'Then'

4)Line 31: Incorrect syntax near ';'

The errors were much more in the original one. I had to add'@.' and also removed ':' as well. Could u plz inform me of the corrections required. Thanks a lot.

Regards,

NAB

|||You were provided Oracle PL/SQL coding. For SQL Server there are some differences.

1)Incorrect Syntax near IN "in line 3"

In SQL Server stored procedures there is no "IN" keyword. Parameters are assumed to be input parameters unless OUTPUT isspecified. So remove the IN keyword.

2)NVL is not a recognized f() name.

Use ISNULL instead of NVL. although it's not needed here because theCOUNT(*) will return a 0 if no records are found to match the criteria.

3)Incorrect syntax near the keyword 'Then'

In SQL Server stored procedures the THEN keyword is not used, nor isthe END IF keyword, so remove them. When conditional statementsextend for more than one line then enclose them in BEGIN...END blocks.

4)Line 31: Incorrect syntax near ';'

There are no line termination characters in SQL Server stored procedures, so remove them.

Additionally, all variables should be DECLARE'd and all variablesmuch start with an @. character. And SQL Server databases arenormally case-insensitive so the UPPER keyword is likely not needed.

You should be left with something that looks like this:

CREATE PROCEDURE CheckThread
(
@.TopicID int,

@.TopicName varchar(50),

@.retValue varchar(2) OUTPUT

)

AS

DECLARE @.c_counter int

SELECT @.c_counter = COUNT(*) FROM Topics WHERE TopicId = @.TopicID and TopicName = @.TopicName

IF @.@.ERROR <> 0

BEGIN
SELECT @.retValue = '03' --'Error Occured getting the data'
RETURN
END

IF(@.c_counter <> 0 )

SELECT @.retValue = '01' --Name matched'

ELSE

SELECT @.retValue = '02' --Name did NOT matched'

RETURN

That could probably be simplified even further using an EXISTS keyword:

CREATE PROCEDURE CheckThread
(
@.TopicID int,

@.TopicName varchar(50),

@.retValue varchar(2) OUTPUT

)

AS
SELECT @.retValue = '03'

IF EXISTS(SELECT TopicID FROM Topics WHERE TopicId = @.TopicID and TopicName = @.TopicName)

SELECT @.retValue = '01' --Name matched'

ELSE

SELECT @.retValue = '02' --Name did NOT matched'

RETURN

|||

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting . I have found a very easier way of doin this and its working perfectly well for my purpose

CREATE PROCEDURE VerifyTopic(@.strTopicId int, @.strTopicName varchar(50))
AS
DECLARE @.Verified int

SELECT @.Verified = 1
FROM Topics
Where TopicId = @.strTopicId AND TopicName = @.strTopicName

If @.Verified = 1
Return 1
else
Return 0
GO

Regards,

NAB

|||

TheNAB wrote:

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting . I have found a very easier way of doin this and its working perfectly well for my purpose

lol. You try to help people and this is what you get. Way to go Mr.NAB

By the way, you never mentioned in your original post that you are using SQL server and you posted in Getting Started forum. You simply asked for a damn SP and that too in a wrong forum

Oh yeah, by the way, what you posted is simply a sql server version of my proc, if you know what you are doing

|||

TheNAB wrote:

Thanks a lot Tmorton. I think Mr. Kumar never knew what he was posting .

Kumar knew what he was posting -- he was posting an Oracle storedprocedure. It's not good form to insult those who are trying tohelp you.
I am glad what you have now serves your purpose. I stillrecommend the coding I suggested at the end of my first post, but ifwhat you now have works for you then I guess you have what youneed. Good luck!