Solved

Help with writing the SQL Procedure

Posted on 2007-11-23
5
200 Views
Last Modified: 2010-03-19
I need to develop a stored procedure using the following pseudocode.

CREATE PROCEDURE KeywordManagement

input parameters :

@KEYWORD_ID int
@TABLE_NAME varchar(100),
@KEYWORD_NAME varchar(100)


if
@KEYWORD_ID=-2 then
   
  'Check to see if the @KEYWORD_NAME exists in the @TABLE_NAME
  @ROW_ID= CheckDoesKeywordExist(@KEYWORD_NAME,@TABLE_NAME)
   if @ROW_ID>0 then ' The KEYWORD_NAME Does exist
     return @ROW_ID
   else
      'Create the entry in the Table
       @ROW_ID=PostKeywordInfo(@KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1)
   end if      

else
@ROW_ID=@KEYWORD_ID

end if
return @ROW_ID

 
 
CheckDoesKeywordExist and PostKeywordInfo are existing procedures that return a value

Any help to start writing or developing the code would be greatly appreciated.
0
Comment
Question by:TECH_NET
  • 3
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20337783
> @ROW_ID= CheckDoesKeywordExist(@KEYWORD_NAME,@TABLE_NAME)
> @ROW_ID=PostKeywordInfo(@KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1)

need to be :
 EXEC @ROW_ID= CheckDoesKeywordExist(@KEYWORD_NAME,@TABLE_NAME)
 EXEC @ROW_ID=PostKeywordInfo(@KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1)
0
 

Author Comment

by:TECH_NET
ID: 20337790
What about the rest of the syntax
0
 

Author Comment

by:TECH_NET
ID: 20337804
Here is my procedure
CREATE PROCEDURE KeywordManagement
(
@KEYWORD_ID int,
@TABLE_NAME varchar(100),
@KEYWORD_NAME varchar(100)

)
AS SET NOCOUNT OFF;  
if @KEYWORD_ID=-2  
   EXEC @ROW_ID= CheckDoesKeywordExist(@KEYWORD_NAME,@TABLE_NAME)
   If @ROW_ID>0  
     return @ROW_ID
   else
     EXEC @ROW_ID=PostKeywordInfo(@KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1)

   end if      

else
@ROW_ID=@KEYWORD_ID

end if
return @ROW_ID

And i get the following error
Must declare the scalar variable "@ROW_ID".
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20337810

CREATE PROCEDURE KeywordManagement

(

@KEYWORD_ID int,

@TABLE_NAME varchar(100),

@KEYWORD_NAME varchar(100)

)

AS

DECLARE @ROW_ID INT

if @KEYWORD_ID=-2

BEGIN
 

--Check to see if the @KEYWORD_NAME exists in the @TABLE_NAME

EXEC @ROW_ID= CheckDoesKeywordExist @KEYWORD_NAME,@TABLE_NAME

if @ROW_ID>0 -- The KEYWORD_NAME Does exist

	return @ROW_ID
 

--Create the entry in the Table

EXEC @ROW_ID=PostKeywordInfo @KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1
 

END ELSE BEGIN

SET @ROW_ID=@KEYWORD_ID
 

END

return @ROW_ID

GO

Open in new window

0
 

Author Comment

by:TECH_NET
ID: 20337811
I fixed the scalar error but now i am getting
Msg 102, Level 15, State 1, Procedure KeywordManagement, Line 13
Incorrect syntax near '@KEYWORD_NAME'.
Msg 102, Level 15, State 1, Procedure KeywordManagement, Line 17
Incorrect syntax near '@KEYWORD_NAME'.

============================================
CREATE PROCEDURE KeywordManagement
(
@KEYWORD_ID int,
@TABLE_NAME varchar(100),
@KEYWORD_NAME varchar(100)

)
AS SET NOCOUNT OFF;  
declare @ROW_ID int
if @KEYWORD_ID=-2  
   EXEC @ROW_ID= CheckDoesKeywordExist(@KEYWORD_NAME,@TABLE_NAME)
   If @ROW_ID>0  
     return @ROW_ID
   else
     EXEC @ROW_ID=PostKeywordInfo(@KEYWORD_NAME,@KEYWORD_NAME,'','',0,-1,1)

   end if      

else
@ROW_ID=@KEYWORD_ID

end if
return @ROW_ID
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now