Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with writing the SQL Procedure

Posted on 2007-11-23
5
Medium Priority
?
254 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 143

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 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