在SQL Server 2005中创建存储过程 [英] creating stored procedures in SQL server 2005

问题描述

您好，

我在SQL Server 2005中创建存储过程，需要一些帮助，因为我卡在其中。



i需要将课程加入目录并且操作应该在一个合作中...



tblCourse和tblCatalog是连接发生的表，值应该是保存在表tbleCourseToCatalogLink。



idSite是发生关联的栏目

 创建 程序 [Catalog.JoinCourse] 
（
  @ Return_Code   INT   OUTPUT ，
  @ idSite   INT ，
 
  @ idCatalog   INT ，
  @ id课程  INT  
）
  AS  
  SET   NOCOUNT   ON  
 
 
 
  IF （
  SELECT  COUNT（ 1 ）
  FROM  tblCatalog 
  WHERE  idCatalog =  @ idCatalog  
  AND  idSite =  @idSite  
）<>  1  
 
  BEGIN  
  SELECT   @ Return_Code  =  1    -  -   （1是'未找到详细信息'） 
 返回  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应该是t的解决方案他的问题??请帮忙解决这个问题

解决方案

  CREATE   PROCEDURE  [Catalog.JoinCourse] 
（
  @ Return_Code   INT   OUTPUT ，
  @ idSite   INT ，
  @ idCatalog   INT ，
  @ idCourse   INT  
）
  AS  
  SET   NOCOUNT   ON  
  BEGIN  
  IF   NOT   EXISTS （  -   如果不存在，插入到tblCourseToCatalogLink，否则返回代码= 1  
  SELECT  COUNT（ 1 ）
  FROM  tblCatalog 
 < span class =code-keyword> 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 
 结束 
  ELSE  
  BEGIN  
 < span class =code-keyword> SET   @ Return_Code  =  1  
  END  
  END  

< blockquote>试试这个..

 创建 程序 [Catalog.JoinCourse] 
  @ Return_Code   INT  输出，
  @ idSite   INT ，
  @ idCatalog   INT ，
  @ idCourse   INT  
  AS  
 开始 
  SET   NOCOUNT   ON  
 
  IF （ SELECT  COUNT（ 1 ） FROM  tblCatalog  WHERE  idCatalog =  @ idCatalog   AND  idSite =  @idSite ）<>  1  
 开始 
  SET   @ Return_Code  =  1    -   （1是'未找到详细信息'） 
 结束 
 其他 
 开始 
  INSERT   INTO  tblCourseToCatalogLink（idCourse，idCatalog）
  SELECT  c.idCourse，cl.idCatalog  FROM  tblCourse c 
  INNER   JOIN  tblCatalog cl  ON  c.idSite = cl.idSite 
 设置  @Return_Code  =  0  
 结束 
 返回 
 结束 

或

  CREATE   PROCEDURE  [Catalog.JoinCourse] 
  @idSite   INT ，
  @ idCatalog   INT ，
  @ idCourse   INT  
  AS  
 开始 
  SET   NOCOUNT   ON  
 
  IF （ SELECT  COUNT（ 1 ） FROM < /温泉n> tblCatalog  WHERE  idCatalog =  @ idCatalog   AND  idSite =  @ idSite ）<>  1  
 开始 
 返回  1    -   （1是'详情不是找到'） 
 结束 
 否则 
 < span class =code-keyword>开始 
  INSERT   INTO  tblCourseToCatalogLink（ idCourse，idCatalog）
  SELECT  c.idCourse，cl.idCatalog  FROM  tblCourse c 
  INNER   JOIN  tblCatalog cl  ON  c。 idSite = cl.idSite 
 设置  @ Return_Code  =  0  
 返回  0  
 结束 
 
 结束 

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

   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

解决方案

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

Try this..

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

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

这篇关于在SQL Server 2005中创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！