Click here to Skip to main content
15,878,748 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 14-Jul-13 23:31pm
v2
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?

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
 
Share this answer
 
v4
Comments
Raja Sekhar S 17-Jul-13 6:03am    
@Vehbi Neziri:
i think you forgot to set @Return_Code=0 after insert statement....
Vehbi Neziri 17-Jul-13 6:07am    
Yes, you are right.
Raja Sekhar S 17-Jul-13 6:25am    
U can update that....
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
 
Share this answer
 
v6

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900