• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Help with writing the SQL Procedure

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
TECH_NET
Asked:
TECH_NET
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> @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
 
TECH_NETAuthor Commented:
What about the rest of the syntax
0
 
TECH_NETAuthor Commented:
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
 
imitchieCommented:

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
 
TECH_NETAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now