笔记118 根据传入不同的@Uname查询不同的表的存储过程

笔记118 根据传入不同的@Uname查询不同的表的存储过程

--根据传入不同的@Uname查询不同的表的存储过程
--我现在有4个表:User表 Expert表 Enterprise表 CommentSite表
--
--现在要显示CommentID，CommentPoster，CommentContent，CommentPostedTime，Name
--由于发布者的类型不同不知道是ExpertName还是EnterpriseName
--请问这个Select语句该怎么写
USE [test]
GO
/****** Object:  StoredProcedure [dbo].[GetUserInFo]    Script Date: 08/20/2012 16:08:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetUserInFo] @Uname VARCHAR(100)
AS
    DECLARE @Usertype INT
    SELECT  @Usertype = UserType
    FROM    [User]
    WHERE   UserName = @Uname
    IF @Usertype = 1
        BEGIN
            SELECT  a.CommentID ,
                    a.CommentPoster ,
                    a.CommentContent ,
                    a.CommentPostedTime ,
                    b.ExpertName
            FROM    Commentsite AS a WITH ( NOLOCK )
                    INNER JOIN Expert AS b WITH ( NOLOCK ) ON a.CommentPoster = b.UserName
        END
    ELSE
        BEGIN
            SELECT  a.CommentID ,
                    a.CommentPoster ,
                    a.CommentContent ,
                    a.CommentPostedTime ,
                    b.EnterpriseName
            FROM    Commentsite AS a WITH ( NOLOCK )
                    INNER JOIN Expertprise AS b WITH ( NOLOCK ) ON a.CommentPoster = b.UserName
        END