Solved

Help with writing the SQL Procedure

Posted on 2007-11-23
5
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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