Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with writing the SQL Procedure

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Loops Section Overview

610 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