sql proc Demo

基础知识：

sql code：

/*********************************************************/
/*                       sql proc                        */
/*                       10/06/26                        */
/*                       Hyey.wl                         */
/*********************************************************/
USE hyey_power
IF EXISTS(SELECT name FROM sysobjects
          where name='GetClassTypeName_wl' AND type='p')
DROP PROCEDURE GetClassTypeName_wl
GO
/*
  根据类别代码获取类别名称
*/
CREATE PROCEDURE GetClassTypeName_wl
(
   @CategoryCode NVARCHAR(20),
   @CategoryName NVARCHAR(40) OUTPUT
)
AS
 SELECT @CategoryName=[name] FROM typetab WHERE Code =@CategoryCode
GO

--DECLARE @Name NVARCHAR(40)
--EXECUTE GetClassTypeName_wl '04',@Name OUTPUT
--SELECT '类别名'=@Name


IF EXISTS(SELECT name FROM sysobjects
          where name='GetProductData_wl' AND type='p')
DROP PROCEDURE GetProductData_wl
GO
/*
  根据类别代码获取所属类别的产品数据集
*/
CREATE PROCEDURE GetProductData_wl
(
   @CategoryCode NVARCHAR(20)
)
AS
 SELECT drugtype,code,COUNT(*) num,ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS indexer 
 FROM ReleaseDrugs r,typetab t 
 WHERE code like '@CategoryCode%' and productsh=1 and r.drugtype=t.name 
 GROUP BY drugtype,code ORDER BY COUNT(*) DESC
GO

--EXECUTE  GetProductData_wl  '05'

IF EXISTS(SELECT name FROM sysobjects
          where name='GetCsalesData_wl' AND type='p')
DROP PROCEDURE GetCsalesData_wl
GO
/*
 获取促销产品信息数据
*/
CREATE PROCEDURE GetCsalesData_wl
AS
SELECT TOP 5 r.id,r.img,left(c.title,10) title 
FROM releaseDrugs r ,csales c 
WHERE r.ypmc=c.ypmc and r.cdmc=c.cdmc and r.gg=c.gg and r.productSh=1 
ORDER BY r.viewflags DESC,c.innerTime DESC
GO


--EXECUTE GetCsalesData_wl

--system 
sp_help 'GetCsalesData_wl'
sp_helptext @objname='GetCsalesData_wl'
sp_depends @objname='GetCsalesData_wl'
sp_stored_procedures 'GetCsalesData_wl'

---

       快速评论通道--您对本文的宝贵意见：
       
感谢您的鼓励和批评，它将是我进步的动力

---

http://wenku.baidu.com/view/40b457fafab069dc502201df.html