笔记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
