存储过程 为 SQL Server 2000版本,请打开SQL server 2000 的查询分析器执行下面的SQL 语句。
程序用到的存储过程(仅支持主键排序)
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[sys_QuickSortPaging]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
drop procedure [ dbo ] . [ sys_QuickSortPaging ]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sys_QuickSortPaging
(
@Table nvarchar ( 4000 ), -- 表名(必须)
@PrimaryKeyField nvarchar ( 50 ), -- 表的主键字段
@Field nvarchar ( 4000 ) = ' * ' , -- 需要返回字段名(必须)
@Where nvarchar ( 1000 ) = NULL , -- Where 条件(可选)
@GroupBy nvarchar ( 1000 ) = NULL , -- 分组
@OrderBy nvarchar ( 1000 ) = NULL , -- 排序用到的字段()
@PageNumber int = 1 , -- 要返回的页(第X页) (默认为第一页)
@PageSize int = 10 , -- 每页大小(默认为5)
@RecordCount int output -- 返回记录总数
)
AS
SET NOCOUNT ON
DECLARE @SortTable nvarchar ( 100 )
DECLARE @SortName nvarchar ( 100 )
DECLARE @strSortColumn nvarchar ( 200 )
DECLARE @Operator nvarchar ( 50 )
DECLARE @Type varchar ( 100 )
DECLARE @Prec int
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy = @PrimaryKeyField
/**/ /* 获取用于定位的字段*/
IF CHARINDEX ( ' DESC ' , @OrderBy ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @OrderBy , ' DESC ' , '' )
SET @Operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @OrderBy ) = 0
SET @strSortColumn = REPLACE ( @OrderBy , ' ASC ' , '' )
SET @Operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Table
SET @SortName = @strSortColumn
END
SELECT @Type = t.name, @Prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @Type ) > 0
SET @Type = @Type + ' ( ' + CAST ( @Prec AS nvarchar ) + ' ) '
DECLARE @strStartRow nvarchar ( 50 )
DECLARE @strPageSize nvarchar ( 50 )
DECLARE @strWhere nvarchar ( 1000 )
DECLARE @strWhereAnd nvarchar ( 1000 )
DECLARE @strGroupBy nvarchar ( 1000 )
IF @PageNumber < 1
SET @PageNumber = 1
SET @strPageSize = CONVERT ( nvarchar ( 50 ), @PageSize )
SET @strStartRow = CONVERT ( nvarchar ( 50 ), ( @PageNumber - 1 ) * @PageSize + 1 )
IF @Where IS NOT NULL AND @Where != ''
BEGIN
SET @strWhere = ' WHERE ' + @Where
SET @strWhereAnd = ' AND ' + @Where
END
ELSE
BEGIN
SET @strWhere = ''
SET @strWhereAnd = ''
END
IF @GroupBy IS NOT NULL AND @GroupBy != ''
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy
END
ELSE
BEGIN
SET @strGroupBy = ''
END
DECLARE @strSQL nvarchar ( 4000 )
SET @strSql = ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy
EXEC sp_executesql @strSql ,N ' @RecordCount int OUTPUT ' , @RecordCount OUTPUT -- 计算总页数
EXEC
(
'
DECLARE @Sort ' + @Type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @Sort = ' + @strSortColumn + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Field + ' FROM ' + @Table + ' WHERE ' + @strSortColumn + @Operator + ' @Sort ' + @strWhereAnd + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [ dbo ] . [ sys_QuickSortPaging ]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sys_QuickSortPaging
(
@Table nvarchar ( 4000 ), -- 表名(必须)
@PrimaryKeyField nvarchar ( 50 ), -- 表的主键字段
@Field nvarchar ( 4000 ) = ' * ' , -- 需要返回字段名(必须)
@Where nvarchar ( 1000 ) = NULL , -- Where 条件(可选)
@GroupBy nvarchar ( 1000 ) = NULL , -- 分组
@OrderBy nvarchar ( 1000 ) = NULL , -- 排序用到的字段()
@PageNumber int = 1 , -- 要返回的页(第X页) (默认为第一页)
@PageSize int = 10 , -- 每页大小(默认为5)
@RecordCount int output -- 返回记录总数
)
AS
SET NOCOUNT ON
DECLARE @SortTable nvarchar ( 100 )
DECLARE @SortName nvarchar ( 100 )
DECLARE @strSortColumn nvarchar ( 200 )
DECLARE @Operator nvarchar ( 50 )
DECLARE @Type varchar ( 100 )
DECLARE @Prec int
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy = @PrimaryKeyField
/**/ /* 获取用于定位的字段*/
IF CHARINDEX ( ' DESC ' , @OrderBy ) > 0
BEGIN
SET @strSortColumn = REPLACE ( @OrderBy , ' DESC ' , '' )
SET @Operator = ' <= '
END
ELSE
BEGIN
IF CHARINDEX ( ' ASC ' , @OrderBy ) = 0
SET @strSortColumn = REPLACE ( @OrderBy , ' ASC ' , '' )
SET @Operator = ' >= '
END
IF CHARINDEX ( ' . ' , @strSortColumn ) > 0
BEGIN
SET @SortTable = SUBSTRING ( @strSortColumn , 0 , CHARINDEX ( ' . ' , @strSortColumn ))
SET @SortName = SUBSTRING ( @strSortColumn , CHARINDEX ( ' . ' , @strSortColumn ) + 1 , LEN ( @strSortColumn ))
END
ELSE
BEGIN
SET @SortTable = @Table
SET @SortName = @strSortColumn
END
SELECT @Type = t.name, @Prec = c.prec
FROM sysobjects o
JOIN syscolumns c on o.id = c.id
JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName
IF CHARINDEX ( ' char ' , @Type ) > 0
SET @Type = @Type + ' ( ' + CAST ( @Prec AS nvarchar ) + ' ) '
DECLARE @strStartRow nvarchar ( 50 )
DECLARE @strPageSize nvarchar ( 50 )
DECLARE @strWhere nvarchar ( 1000 )
DECLARE @strWhereAnd nvarchar ( 1000 )
DECLARE @strGroupBy nvarchar ( 1000 )
IF @PageNumber < 1
SET @PageNumber = 1
SET @strPageSize = CONVERT ( nvarchar ( 50 ), @PageSize )
SET @strStartRow = CONVERT ( nvarchar ( 50 ), ( @PageNumber - 1 ) * @PageSize + 1 )
IF @Where IS NOT NULL AND @Where != ''
BEGIN
SET @strWhere = ' WHERE ' + @Where
SET @strWhereAnd = ' AND ' + @Where
END
ELSE
BEGIN
SET @strWhere = ''
SET @strWhereAnd = ''
END
IF @GroupBy IS NOT NULL AND @GroupBy != ''
BEGIN
SET @strGroupBy = ' GROUP BY ' + @GroupBy
END
ELSE
BEGIN
SET @strGroupBy = ''
END
DECLARE @strSQL nvarchar ( 4000 )
SET @strSql = ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy
EXEC sp_executesql @strSql ,N ' @RecordCount int OUTPUT ' , @RecordCount OUTPUT -- 计算总页数
EXEC
(
'
DECLARE @Sort ' + @Type + '
SET ROWCOUNT ' + @strStartRow + '
SELECT @Sort = ' + @strSortColumn + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '
SET ROWCOUNT ' + @strPageSize + '
SELECT ' + @Field + ' FROM ' + @Table + ' WHERE ' + @strSortColumn + @Operator + ' @Sort ' + @strWhereAnd + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
支持任意字段排序的存储过程
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[sys_SortDataPager]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
drop procedure [ dbo ] . [ sys_SortDataPager ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sys_SortDataPager (
@Table nvarchar ( 4000 ), -- 表名(必须)
@PrimaryKeyField nvarchar ( 50 ), -- 表的主键字段
@Field nvarchar ( 4000 ) = ' * ' , -- 需要返回字段名(必须)
@Where nvarchar ( 1000 ) = NULL , -- Where 条件(可选)
@GroupBy nvarchar ( 1000 ) = NULL , -- 分组
@OrderBy nvarchar ( 1000 ) = NULL , -- 排序用到的字段()
@PageNumber int = 1 , -- 要返回的页(第X页) (默认为第一页)
@PageSize int = 10 , -- 每页大小(默认为5)
@RecordCount int out -- 返回记录总数
)
AS
/**/ /*Find the @PrimaryKeyField type*/
DECLARE @PKTable varchar ( 1000 )
DECLARE @PKName varchar ( 1000 )
DECLARE @type varchar ( 1000 )
DECLARE @prec int
IF CHARINDEX ( ' . ' , @PrimaryKeyField ) > 0
BEGIN
SET @PKTable = SUBSTRING ( @PrimaryKeyField , 0 , CHARINDEX ( ' . ' , @PrimaryKeyField ))
SET @PKName = SUBSTRING ( @PrimaryKeyField , CHARINDEX ( ' . ' , @PrimaryKeyField ) + 1 , LEN ( @PrimaryKeyField ))
END
ELSE
BEGIN
SET @PKTable = @Table
SET @PKName = @PrimaryKeyField
END
SELECT @type = t.name, @prec = c.prec FROM sysobjects o JOIN syscolumns c on o.id = c.id JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strWhere varchar ( 1000 )
DECLARE @strGroupBy varchar ( 1000 )
/**/ /*Default Sorting*/
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy = @PrimaryKeyField
/**/ /*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/**/ /*Set paging variables.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/**/ /*Set filter & group variables.*/
IF @Where IS NOT NULL AND @Where != ''
SET @strWhere = ' WHERE ' + @Where + ' '
ELSE
SET @strWhere = ''
IF @GroupBy IS NOT NULL AND @GroupBy != ''
SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
ELSE
SET @strGroupBy = ''
/**/ /*Execute dynamic query*/
DECLARE @strSQL nvarchar ( 4000 )
SET @strSql = ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy
EXEC sp_executesql @strSql ,N ' @RecordCount int OUTPUT ' , @RecordCount OUTPUT -- 计算总页数
EXEC (
' DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '
DECLARE @PrimaryKeyField ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PrimaryKeyField + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '
OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PrimaryKeyField
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PrimaryKeyField)
FETCH NEXT FROM PagingCursor INTO @PrimaryKeyField
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ' + @Field + ' FROM ' + @Table + ' JOIN @tblPK tblPK ON ' + @PrimaryKeyField + ' = tblPK.PK ' + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [ dbo ] . [ sys_SortDataPager ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sys_SortDataPager (
@Table nvarchar ( 4000 ), -- 表名(必须)
@PrimaryKeyField nvarchar ( 50 ), -- 表的主键字段
@Field nvarchar ( 4000 ) = ' * ' , -- 需要返回字段名(必须)
@Where nvarchar ( 1000 ) = NULL , -- Where 条件(可选)
@GroupBy nvarchar ( 1000 ) = NULL , -- 分组
@OrderBy nvarchar ( 1000 ) = NULL , -- 排序用到的字段()
@PageNumber int = 1 , -- 要返回的页(第X页) (默认为第一页)
@PageSize int = 10 , -- 每页大小(默认为5)
@RecordCount int out -- 返回记录总数
)
AS
/**/ /*Find the @PrimaryKeyField type*/
DECLARE @PKTable varchar ( 1000 )
DECLARE @PKName varchar ( 1000 )
DECLARE @type varchar ( 1000 )
DECLARE @prec int
IF CHARINDEX ( ' . ' , @PrimaryKeyField ) > 0
BEGIN
SET @PKTable = SUBSTRING ( @PrimaryKeyField , 0 , CHARINDEX ( ' . ' , @PrimaryKeyField ))
SET @PKName = SUBSTRING ( @PrimaryKeyField , CHARINDEX ( ' . ' , @PrimaryKeyField ) + 1 , LEN ( @PrimaryKeyField ))
END
ELSE
BEGIN
SET @PKTable = @Table
SET @PKName = @PrimaryKeyField
END
SELECT @type = t.name, @prec = c.prec FROM sysobjects o JOIN syscolumns c on o.id = c.id JOIN systypes t on c.xusertype = t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName
IF CHARINDEX ( ' char ' , @type ) > 0
SET @type = @type + ' ( ' + CAST ( @prec AS varchar ) + ' ) '
DECLARE @strPageSize varchar ( 50 )
DECLARE @strStartRow varchar ( 50 )
DECLARE @strWhere varchar ( 1000 )
DECLARE @strGroupBy varchar ( 1000 )
/**/ /*Default Sorting*/
IF @OrderBy IS NULL OR @OrderBy = ''
SET @OrderBy = @PrimaryKeyField
/**/ /*Default Page Number*/
IF @PageNumber < 1
SET @PageNumber = 1
/**/ /*Set paging variables.*/
SET @strPageSize = CAST ( @PageSize AS varchar ( 50 ))
SET @strStartRow = CAST ((( @PageNumber - 1 ) * @PageSize + 1 ) AS varchar ( 50 ))
/**/ /*Set filter & group variables.*/
IF @Where IS NOT NULL AND @Where != ''
SET @strWhere = ' WHERE ' + @Where + ' '
ELSE
SET @strWhere = ''
IF @GroupBy IS NOT NULL AND @GroupBy != ''
SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '
ELSE
SET @strGroupBy = ''
/**/ /*Execute dynamic query*/
DECLARE @strSQL nvarchar ( 4000 )
SET @strSql = ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy
EXEC sp_executesql @strSql ,N ' @RecordCount int OUTPUT ' , @RecordCount OUTPUT -- 计算总页数
EXEC (
' DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '
DECLARE @PrimaryKeyField ' + @type + '
DECLARE @tblPK TABLE (
PK ' + @type + ' NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT ' + @PrimaryKeyField + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '
OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PrimaryKeyField
SET NOCOUNT ON
WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
INSERT @tblPK (PK) VALUES (@PrimaryKeyField)
FETCH NEXT FROM PagingCursor INTO @PrimaryKeyField
SET @PageSize = @PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ' + @Field + ' FROM ' + @Table + ' JOIN @tblPK tblPK ON ' + @PrimaryKeyField + ' = tblPK.PK ' + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
创建一个用于测试的表
CREATE
TABLE
[
dbo
]
.
[
Employees
]
(
[ EmployeesID ] [ numeric ] ( 18 , 0 ) IDENTITY ( 1 , 1 ) NOT NULL ,
[ LastName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ FirstName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ BirthDate ] [ datetime ] NULL ,
[ Address ] [ nvarchar ] ( 60 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ City ] [ nvarchar ] ( 18 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ HomePhone ] [ nvarchar ] ( 24 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Extension ] [ nvarchar ] ( 4 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
[ EmployeesID ] [ numeric ] ( 18 , 0 ) IDENTITY ( 1 , 1 ) NOT NULL ,
[ LastName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ FirstName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ BirthDate ] [ datetime ] NULL ,
[ Address ] [ nvarchar ] ( 60 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ City ] [ nvarchar ] ( 18 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ HomePhone ] [ nvarchar ] ( 24 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Extension ] [ nvarchar ] ( 4 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
生成 1000000 条测试数据
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[InsertTableData]
'
)
and
OBJECTPROPERTY
(id, N
'
IsProcedure
'
)
=
1
)
drop procedure [ dbo ] . [ InsertTableData ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertTableData
AS
DECLARE @cnt bigint
SET @cnt = 0
WHILE @cnt < 1000000
BEGIN
INSERT INTO Employees(LastName,FirstName,BirthDate,Address,City,HomePhone,Extension)
Values
(
CAST ( ' LastName ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' FirstName ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
GETDATE (),
CAST ( ' Address IS No. ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' City ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' 021-0000 ' + LEFT ( CONVERT ( nvarchar ( 10 ), @cnt ), 4 ) as nvarchar ( 30 )),
CAST ( ' 00 ' + LEFT ( CONVERT ( nvarchar ( 10 ), @cnt ) , 1 ) as nvarchar ( 30 ))
)
SET @cnt = @cnt + 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [ dbo ] . [ InsertTableData ]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE InsertTableData
AS
DECLARE @cnt bigint
SET @cnt = 0
WHILE @cnt < 1000000
BEGIN
INSERT INTO Employees(LastName,FirstName,BirthDate,Address,City,HomePhone,Extension)
Values
(
CAST ( ' LastName ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' FirstName ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
GETDATE (),
CAST ( ' Address IS No. ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' City ' + CONVERT ( nvarchar ( 10 ), @cnt ) as nvarchar ( 30 )),
CAST ( ' 021-0000 ' + LEFT ( CONVERT ( nvarchar ( 10 ), @cnt ), 4 ) as nvarchar ( 30 )),
CAST ( ' 00 ' + LEFT ( CONVERT ( nvarchar ( 10 ), @cnt ) , 1 ) as nvarchar ( 30 ))
)
SET @cnt = @cnt + 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO