Data Matters: SQL Server

Joni 的个人资料Data Matters日志列表

工具

帮助

日志

摘要

排列方式：

<< 第一页 < 上一页下一页 > 最后一页 >>

2006/3/29

Script that helps writing stored procedure parameters

When writing stored procedures (sprocs) for the SQL Server, it is tiresome to write all the parameters for a typical sproc that modifies a table:

CREATE PROCEDURE dbo.Table1_Modify

@DataID

AS int,
@FieldID AS int,
@Title AS varchar(100),
@Value AS varchar(255)

...

To reduce some repetitive typing, I did a small helper procedure that gives you all the columns of a given table as a parameter list:

CREATE

PROCEDURE dbo.GenerateSprocParams

@TableName

As varchar(255)

--Generates parameter list for the given table

SELECT

' @' + column_name + ' AS ' + data_type +

CASE

WHEN character_maximum_length IS NOT NULL
THEN '(' + cast(character_maximum_length as varchar(10)) + ')'

ELSE ''

END + ', ' As 'Parameters'

FROM

information_schema.columns

WHERE

table_name = @TableName

After that all you need to do is call the procedure:

EXEC

GenerateSprocParams 'MyTable'

And the result will be like:

Parameters
-----------------------------
@DataID AS int,
@FieldID AS int,
@Title AS varchar(100),
@Value AS varchar(255),

...

From the results it is easy to copy the list for the CREATE PROCEDURE-script. Be sure to change the result type into text-mode from the Query-menu of the Query Analyzer or Management Studio before executing the script. (The example was tested on SQL Server 2005)

Next step would be to generate whole procedures similarly, but there are tools for that already.

Anyway, I hope this gives you at least some ideas how to utilize the metadata of the SQL Server!

2005/9/5

Paging in SQL Server 2005

Paging has always been bit of a problem in SQL Server-world. For example, some other databases support LIMIT in ORDER BY, making the often needed paging easier to implement.

Now that the next SQL Server supports ROW_NUMBER(), things are getting easier for ASP.NET-developers. ROW_NUMBER() allows you to add a running number to the result set without the need of temporary tables and other less scalable approaches.

Here are two little examples for how to add paging functionality to a stored procedure:

Use Northwind
Go
CREATE PROCEDURE dbo.Products_GetPaged
@StartRow as int = 1, --Default starting row: 1
@PageSize as int = 10 --Default page size: 10
AS
SET NOCOUNT ON

SELECT *
FROM --The temporary result is stored inside a derived table "Paged"
(SELECT
        ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNumber,
        ProductID,
        ProductName,
        UnitPrice
FROM
        dbo.Products) As Paged
WHERE
        RowNumber BETWEEN @StartRow AND @StartRow + @PageSize - 1
Go
--Examples:
EXEC Products_GetPaged --First 10 rows (1-10)
EXEC Products_GetPaged 11 --Next 10 rows (11-20)
EXEC Products_GetPaged 21,5 --Next 5 rows (21-25)

This approach works fine in some scenarios, but what if you want to send page numbers instead of starting row number? Here is a slightly altered version that takes @PageNum -parameter instead of @StartRow:

CREATE PROCEDURE dbo.Products_GetPagedByPageNum
@PageNum as int = 1, --Default starting page: 1
@PageSize as int = 10 --Default page size: 10
AS
SET NOCOUNT ON

DECLARE @StartRow as int, @EndRow as int
SET @StartRow = (@PageNum-1) * @PageSize + 1
SET @EndRow = @PageNum * @PageSize

SELECT *
FROM
(SELECT
        ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNumber,
        ProductID,
        ProductName,
        UnitPrice
FROM
        dbo.Products) As Paged
WHERE
        RowNumber BETWEEN @StartRow AND @EndRow
Go

EXEC Products_GetPagedByPageNum --First 10 rows (1-10)
EXEC Products_GetPagedByPageNum 2 --Page 2 (11-20)
EXEC Products_GetPagedByPageNum 3, 5 --Page 3, with page size of 5 (11-15)

21:32:48 | 固定链接 | 查看引用通告 (30) | 写入日志 | SQL Server