SQL 查询例子

示例1：

/****** Object:  StoredProcedure [dbo].[ProcIEBillHeadRelationQuery]    Script Date: 04/15/2013 13:46:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author      :   AnDequan
-- Create date :   2013年3月15日
-- Description :   根据查询条件查询
-- =============================================
ALTER PROCEDURE [dbo].[ProcIEBillHeadRelationQuery]
@Business_NO VARCHAR(25),    
@agen_code varchar(10),
@Search_All varchar(1),
@status varchar(2)     
AS
BEGIN


DECLARE @SQL NVARCHAR(2000),@StrWhere nvarchar(1000)
if (@Search_All='0')
   SET @StrWhere = ' 1=1 '


if (@Search_All='1')
   SET @StrWhere = ' DOWNLOAD_FLAG=0 '


IF @Business_NO !='' --业务单编号
   SET @StrWhere = @StrWhere+ ' AND [Business_NO] = '''+@Business_NO+''''
IF @agen_code !='' --导入结束时间
   SET @StrWhere =@StrWhere+ ' AND [agent_code] = '''+@agen_code+''''
IF @status !='-1'     --状态
   SET @StrWhere =@StrWhere+ ' AND [Status] = '''+@status+''''
   
SET @SQL = 'SELECT
top 30 *
FROM IEBillHeadRelation
WHERE '+@StrWhere+'
ORDER BY  Import_Time DESC'

EXEC sp_executesql @SQL,
N'@Business_NO VARCHAR(25), @agen_code varchar(10)',
@Business_NO, @agen_code
PRINT @SQL
 --EXEC ProcIEBillHeadRelationQuery '','','1','3'

End

示例2

/****** Object:  StoredProcedure [dbo].[ProcQueryTieZiAll]    Script Date: 05/08/2013 10:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ProcQueryTieZiAll]
@StartNo INT,@EndNo INT,@StrWhere nvarchar(2000),@OrderBy nvarchar(400), @Title nvarchar(200),@Code nvarchar(20)
AS BEGIN
DECLARE @SQL NVARCHAR(MAX), @SQLRowCount NVARCHAR(MAX)
IF @Title != ''
 SET @StrWhere = @StrWhere + ' AND ps.Title LIKE ''%'+@Title+'%'' '
SET @SQL = 'SELECT * FROM 
(
SELECT ROW_NUMBER() OVER(ORDER BY '+@OrderBy+') rowNumber,  t1.* ,t2.Username,s.ReTime,t3.Username as ReName 
FROM (
SELECT ps.* FROM dbo.forums_Posts ps
INNER JOIN 
(
SELECT HierarchyDataId FROM  dbo.common_HierarchyData WHERE ParentHierarchyDataId=
(SELECT HierarchyDataId FROM  dbo.common_HierarchyData where code='''+@Code+''')
) AS temp
ON ps.CategoryId=temp.HierarchyDataId
WHERE '+@StrWhere+' 
 ) AS t1 
left JOIN dbo.aspnet_Users AS t2 ON t1.CreatedBy=t2.UserId
left join (
select PostId,max(CreatedOn) as ReTime from dbo.forums_PostReplies group by postid
) s on t1.Id=s.PostId
left join dbo.aspnet_Users as t3 on t1.repliedby=t3.userid
) N
WHERE N.rowNumber between '+Convert(nvarchar(10), @StartNo)+' and '+Convert(nvarchar(10),@EndNo)+''

 SET @SQLRowCount ='SELECT COUNT(*) FROM 
(
SELECT ROW_NUMBER() OVER(ORDER BY '+@OrderBy+') rowNumber,  t1.* ,t2.Username,s.ReTime,t3.Username as ReName 
FROM (
SELECT ps.* FROM dbo.forums_Posts ps
INNER JOIN 
(
SELECT HierarchyDataId FROM  dbo.common_HierarchyData WHERE ParentHierarchyDataId=
(SELECT HierarchyDataId FROM  dbo.common_HierarchyData where code='''+@Code+''')
) AS temp
ON ps.CategoryId=temp.HierarchyDataId
WHERE '+@StrWhere+' 
 ) AS t1 
left JOIN dbo.aspnet_Users AS t2 ON t1.CreatedBy=t2.UserId
left join (
select PostId,max(CreatedOn) as ReTime from dbo.forums_PostReplies group by postid
) s on t1.Id=s.PostId
left join dbo.aspnet_Users as t3 on t1.repliedby=t3.userid
) N'


EXEC sp_executesql @SQLRowCount,
N' @StrWhere nvarchar(2000), @OrderBy nvarchar(400), @Title nvarchar(200), @Code nvarchar(20)',
@StrWhere, @OrderBy, @Title, @Code

EXEC sp_executesql @SQL,
N' @StartNo INT, @EndNo INT, @StrWhere nvarchar(2000), @OrderBy nvarchar(400), @Title nvarchar(200), @Code nvarchar(20)',
@StartNo, @EndNo, @StrWhere, @OrderBy, @Title, @Code
PRINT 'TotalCount:'+@SQLRowCount
PRINT '@SQL:'+@SQL
--EXEC ProcQueryTieZiAll 1,20,' 0=0 ' ,'','Hscode'


/*----------------------------------------------------------原始SQL----------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------*/
-- SELECT * FROM 
--(
-- SELECT ROW_NUMBER() OVER(ORDER BY t1.CreatedOn DESC) rowNumber,  t1.* ,t2.Username,s.ReTime,t3.Username as ReName 
-- FROM (
-- SELECT ps.* FROM dbo.forums_Posts ps
-- INNER JOIN 
-- (
-- SELECT HierarchyDataId FROM  dbo.common_HierarchyData WHERE ParentHierarchyDataId=
-- (SELECT HierarchyDataId FROM  dbo.common_HierarchyData where code='Hscode')
-- ) AS temp
-- ON ps.CategoryId=temp.HierarchyDataId
-- --WHERE ps.Title like '%2012%'
--  ) AS t1 
-- left JOIN dbo.aspnet_Users AS t2 ON t1.CreatedBy=t2.UserId
-- left join (
-- select PostId,max(CreatedOn) as ReTime from dbo.forums_PostReplies group by postid
-- ) s on t1.Id=s.PostId
-- left join dbo.aspnet_Users as t3 on t1.repliedby=t3.userid
--) N


/*----------------------------------------------------------原始SQL----------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------*/
END