数据库and语句

1 USE [ ibcs_wuhu_true ]
2   GO
3   /* ***** Object: StoredProcedure [dbo].[P_RP_JBXX_XHQK] Script Date: 04/20/2011 09:55:43 ***** */
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 /*
9 用户销户情况一览表
10 P_RP_JBXX_XHQK '00','','2010-12-06','2011-01-28'
11 P_RP_JBXX_XHQK '91','','',''
12 */
13 ALTER PROCEDURE [ dbo ] . [ P_RP_JBXX_XHQK ]
14 @S_ST VARCHAR ( 10 ),
15 @S_CH VARCHAR ( 10 ),
16 @StartCB VARCHAR ( 10 ), -- 拆表开始时间
17 @EndCB VARCHAR ( 10 ) -- 拆表结束时间
18 -- D_ChaiBiaoRQ
19 AS
20 BEGIN
21 DECLARE @STNAME VARCHAR ( 50 ), @Condition varchar ( 500 ), @SQL VARCHAR ( 1000 )
22 SELECT @STNAME = ' 天之饺子制造 '
23 SELECT @S_ST = CASE WHEN @S_ST = '' THEN ' 00 ' ELSE @S_ST END
24
25 IF @S_ST <> ' 00 ' AND @S_ST <> ''
26 BEGIN
27 SELECT @STNAME = RTRIM (S_ZhanDianMC) FROM SX_ZhanDianXX WHERE S_ST = @S_ST
28 END
29
30 CREATE TABLE #REPORT_DATA(STNAME VARCHAR ( 50 ),TJ_CH VARCHAR ( 10 ), ID INT IDENTITY ( 1 , 1 ) NOT NULL ,S_CH VARCHAR ( 10 ),S_CID VARCHAR ( 16 ),S_HM VARCHAR ( 100 ),S_DZ VARCHAR ( 100 ),
31 LXDH VARCHAR ( 50 ),CBRQ VARCHAR ( 10 ),DJRQ VARCHAR ( 10 ),QFJE NUMERIC( 18 , 2 ),YE NUMERIC( 18 , 2 ),JBR VARCHAR ( 50 )
32 )
33 SET @SQL = ' INSERT INTO #REPORT_DATA
34 SELECT ''' + @STNAME + ''' ,
35 ''' + @S_CH + ''' ,
36 B.S_CH,
37 A.S_CID,
38 B.S_HM,
39 B.S_DZ,
40 '''' ,
41 CONVERT(VARCHAR(10),D.D_ChaiBiaoRQ,112),
42 CONVERT(VARCHAR(10),A.D_CaoZuoSJ,112),
43 A.N_QianFeiJE,
44 ISNULL(C.N_YuFuFJE,0),
45 A.S_CaoZuoRMC
46 FROM RZ_ZhuXiaoBTBG A
47 INNER JOIN KG_BiaoKaXX B ON B.S_CID = A.S_CID
48 LEFT JOIN BW_ChaiBiaoXX D ON D.S_CID = A.S_CID
49 LEFT JOIN YF_YuFuFXX C ON C.S_CID = A.S_CID
50 WHERE A.I_CaoZuoLX = '' 1 ''
51 AND ( '' 00 '' = ''' + @S_ST + ''' OR B.S_ST = ''' + @S_ST + ''' )

　　　　--AND ('00' = '91' OR B.S_ST ='91')

　　　当前面的条件不成立就执行后面成立的条件

52     AND ('''' ='''+@S_CH+''' OR B.S_CH ='''+@S_CH+''')'
53     --AND ('' = '' OR B.S_CH ='')

　　　　 54 IF @StartCB = ''
55 BEGIN
56 EXEC ( @SQL )
57 END
58 IF @StartCB <> ''
59 BEGIN
60 set @Condition = ' AND D.D_ChaiBiaoRQ between ''' + @StartCB + ''' and ''' + @EndCB + ''''
61 SET @SQL = @SQL + @Condition
62 EXEC ( @SQL )
63 PRINT ( @SQL )
64 END
65 SELECT * FROM #REPORT_DATA ORDER BY ID
66 DROP TABLE #REPORT_DATA
67 END
68
69 /*
70 select * from RZ_ZhuXiaoBTBG 用户注销/报停恢复表
71
72 select top 100 * from KG_BiaoKaXX 表卡信息表
73
74 select top 1000 * from BW_ChaiBiaoXX 拆表信息表
75
76 select top 100 * from YF_YuFuFXX 预付费信息表
77
78 */

转载于:https://www.cnblogs.com/zq281660880/archive/2011/04/20/2022418.html