SQL中声明表变量示例

USE [ShanghaiWeibo]
GO
/****** Object:  StoredProcedure [dbo].[PROC_GetMyRealAttentionByParams]    Script Date: 08/09/2012 10:30:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
-- =============================================
-- Author:        zhaowei
-- Create date: 20120312
-- Description:    检索我的互相关注
-- =============================================
--exec PROC_GetMyRealAttentionByParams 66,0,0,100,'',0
 
ALTER PROCEDURE [dbo].[PROC_GetMyRealAttentionByParams]
(
    @memberId int,
    @memberType int,
    @pageIndex int,
    @pageSize int,
    @keyword nvarchar(50),
    @groupId int
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @startRowIndex INT
    DECLARE @EndRows INT
    SET @startRowIndex=@PageIndex*@pageSize+1
    SET @EndRows=@startRowIndex+@pageSize
    --    DECLARE @SQL NVARCHAR(1000)    
 
    DECLARE @TABLE_MEMBER TABLE (toMemberid INT,toMemberType INT)
 
    INSERT INTO @TABLE_MEMBER
    SELECT    
        AB.toMemberid,
        AB.toMemberType
    FROM AttentionBusiness AB
    INNER JOIN (SELECT id,fromMemberId,fromMemberType,toMemberid FROM AttentionBusiness WHERE toMemberid   = @memberId and toMemberType=@memberType) CD
    ON AB.toMemberid = CD.fromMemberId AND AB.toMemberType = CD.fromMemberType
    WHERE
        AB.fromMemberId = @memberId and AB.fromMemberType=@memberType
    ORDER BY AB.toMemberid
     
    DECLARE @T_TABLE TABLE
    (
        ID INT,
        fromMemberId INT,
        fromMemberType INT,
        toMemberid INT,
        toMemberType INT,
        addDatetime datetime,
        fromGroupId INT,
        toMemberFirstChar VARCHAR(100),
        toMemberName NVARCHAR(100)
    )
    INSERT INTO @T_TABLE
    SELECT
    AB.*
        FROM
    AttentionBusiness  AB
    INNER JOIN
    ( SELECT ID,CompanyPetName FROM    Member_Info
      WHERE  ID IN (SELECT toMemberid FROM @TABLE_MEMBER WHERE toMemberType =1) AND CompanyPetName LIKE '%'+@keyword+'%' 
    ) MI ON MI.ID = AB.toMemberid
    WHERE
        AB.fromMemberId = @memberId and AB.fromMemberType=@memberType
    AND
        toMemberid IN (SELECT toMemberid FROM @TABLE_MEMBER WHERE toMemberType =1)
    AND
        toMemberType = 1
 
    UNION
 
    SELECT
    AB.*
        FROM
    AttentionBusiness  AB
    INNER JOIN
    ( SELECT ID,MemberPetName FROM    MemberBizcenter_Info
      WHERE  ID IN (SELECT toMemberid FROM @TABLE_MEMBER WHERE toMemberType =0) AND MemberPetName LIKE '%'+@keyword+'%' 
    ) MI ON MI.ID = AB.toMemberid
    WHERE
        AB.fromMemberId = @memberId AND AB.fromMemberType=@memberType
    AND
        toMemberid IN (SELECT toMemberid FROM @TABLE_MEMBER WHERE toMemberType =0)
    AND
        toMemberType = 0
 
    SELECT TA.*
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY T.ID) AS SNO,
    T.*
    FROM @T_TABLE AS T
    ) AS TA
    WHERE TA.SNO>=@startRowIndex AND TA.SNO<@EndRows
 
    SELECT COUNT(T.ID) AS RecordCount
    FROM @T_TABLE AS T
END
-------------------------------------------------------------------------------------------------
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        zw
-- Create date: 20120809
-- Description:    检索人气排名
-- =============================================
--exec PROC_FansCountTop 10
CREATE PROCEDURE PROC_FansCountTop
    (
        @top int
    )
AS
BEGIN
    SET NOCOUNT ON;
 
 
    declare @t table
    (
        ID int,
        MemberName nvarchar(100),
        MemberType varchar(10),
        TrueName nvarchar(100),
        fansCount int
    )
 
    insert into @t
 
    select ID,MemberName,MemberType,TrueName,
    (BeAttentionedCount0+BeAttentionedCount1) as fansCount
     from MemberBizcenter_Info
 
 
    union
 
    select ID,MemberName,'1' as MemberType,TrueName,
    (BeAttentionedCount0+BeAttentionedCount1) as fansCount
     from Member_Info
 
 
     select top(@top) * from @t order by fansCount desc
 
END
GO