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

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

 1 --根据传入不同的@Uname查询不同的表的存储过程
 2 --我现在有4个表:User表 Expert表 Enterprise表 CommentSite表
 3 --
 4 --现在要显示CommentID,CommentPoster,CommentContent,CommentPostedTime,Name
 5 --由于发布者的类型不同不知道是ExpertName还是EnterpriseName
 6 --请问这个Select语句该怎么写
 7 USE [test]
 8 GO
 9 /****** Object:  StoredProcedure [dbo].[GetUserInFo]    Script Date: 08/20/2012 16:08:05 ******/
10 SET ANSI_NULLS ON
11 GO
12 SET QUOTED_IDENTIFIER ON
13 GO
14 CREATE PROC [dbo].[GetUserInFo] @Uname VARCHAR(100)
15 AS
16     DECLARE @Usertype INT
17     SELECT  @Usertype = UserType
18     FROM    [User]
19     WHERE   UserName = @Uname
20     IF @Usertype = 1
21         BEGIN
22             SELECT  a.CommentID ,
23                     a.CommentPoster ,
24                     a.CommentContent ,
25                     a.CommentPostedTime ,
26                     b.ExpertName
27             FROM    Commentsite AS a WITH ( NOLOCK )
28                     INNER JOIN Expert AS b WITH ( NOLOCK ) ON a.CommentPoster = b.UserName
29         END
30     ELSE
31         BEGIN
32             SELECT  a.CommentID ,
33                     a.CommentPoster ,
34                     a.CommentContent ,
35                     a.CommentPostedTime ,
36                     b.EnterpriseName
37             FROM    Commentsite AS a WITH ( NOLOCK )
38                     INNER JOIN Expertprise AS b WITH ( NOLOCK ) ON a.CommentPoster = b.UserName
39         END

 

posted @ 2013-08-03 22:39 桦仔 阅读(...) 评论(...)  编辑 收藏