creating stored procedures in SQL server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello,
Im creating stored procedures in SQL Server 2005, a little help needed as im stuck in it.

i need to joins Course(s) to a Catalog and the action should on a joincourse...

tblCourse and tblCatalog are the tables which the join occur and the values should be a save in the table tbleCourseToCatalogLink.

idSite is the column on which the realtionship occur

SQL

   CREATE PROCEDURE [Catalog.JoinCourse]
	(
	@Return_Code	INT	OUTPUT,
	@idSite		INT, 
	
	@idCatalog     INT,
	@idCourse      INT
)
AS
SET NOCOUNT ON
	
   
	     
IF (
	SELECT COUNT(1)
	FROM tblCatalog
	WHERE idCatalog = @idCatalog
	AND idSite = @idSite
	) <> 1
	
	BEGIN
	SELECT @Return_Code = 1 --(1 is 'details not found')
	RETURN 0
	END	     
	
	INSERT INTO tblCourseToCatalogLink (
	       idCourse,
	       idCatalog	
	)
	     SELECT c.idCourse,cl.idCatalog
	     FROM tblCourse c 
	     INNER JOIN tblCatalog cl
	     ON c.idSite = cl.idSite

Wot should be solution of this problem?? Kindly help in this matter

Posted 14-Jul-13 22:57pm

Almas Sayem

Updated 14-Jul-13 23:31pm

Maciej Los

v2

Add a Solution

Comments

ArunRajendra 15-Jul-13 5:21am

Can you give the table structure for tblCourse and tblCatalog

Maciej Los 15-Jul-13 5:39am

Not clear. What are you trying to achieve? Please, be more specific and provide more details. Use "Improve question" widget to update question.
Why do you add some extra values into tblCourseToCatalogLink if you can join it on idSite field?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vehbi Neziri · Answer 1 · 2013-07-16T22:49:00

SQL

CREATE PROCEDURE [Catalog.JoinCourse]
(
    @Return_Code  INT OUTPUT,
    @idSite       INT,
    @idCatalog    INT,
    @idCourse     INT
)
AS
	SET NOCOUNT ON
	BEGIN
		IF NOT EXISTS ( --if not exists, insert to tblCourseToCatalogLink, else returin code=1
		       SELECT COUNT(1)
		       FROM   tblCatalog
		       WHERE  idCatalog = @idCatalog
		              AND idSite = @idSite
		   )
		BEGIN
		    INSERT INTO tblCourseToCatalogLink
		      (
		        idCourse,
		        idCatalog
		      )
		    SELECT c.idCourse,
		           cl.idCatalog
		    FROM   tblCourse c
		           INNER JOIN tblCatalog cl
		                ON  c.idSite = cl.idSite
		END
		ELSE
		BEGIN
		    SET @Return_Code = 1
		END
	END

Raja Sekhar S · Answer 2 · 2013-07-16T22:25:00

Try this..

SQL

CREATE PROCEDURE [Catalog.JoinCourse]
	@Return_Code   INT  OUTPUT,
	@idSite	       INT, 
	@idCatalog     INT,
	@idCourse      INT
AS
Begin
    SET NOCOUNT ON
  	     
    IF (SELECT COUNT(1) FROM tblCatalog WHERE idCatalog = @idCatalog AND idSite = @idSite ) <> 1
    Begin
       SET @Return_Code = 1 --(1 is 'details not found')
    End
    Else
    Begin
       INSERT INTO tblCourseToCatalogLink (idCourse, idCatalog)
       SELECT c.idCourse,cl.idCatalog FROM tblCourse c 
       INNER JOIN tblCatalog cl ON c.idSite = cl.idSite
       Set @Return_Code = 0
    End
  Return
End

Or

SQL

CREATE PROCEDURE [Catalog.JoinCourse]
	@idSite	       INT, 
	@idCatalog     INT,
	@idCourse      INT
AS
Begin
    SET NOCOUNT ON
  	     
    IF (SELECT COUNT(1) FROM tblCatalog WHERE idCatalog = @idCatalog AND idSite = @idSite ) <> 1
    Begin
	Return 1 --(1 is 'details not found')
    End
    Else
    Begin
       INSERT INTO tblCourseToCatalogLink (idCourse, idCatalog)
       SELECT c.idCourse,cl.idCatalog FROM tblCourse c 
       INNER JOIN tblCatalog cl ON c.idSite = cl.idSite
       Set @Return_Code = 0
       Return 0
    End
  
End

creating stored procedures in SQL server 2005

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0