410
Part IV: Programming with T-SQL
WHERE c.ProductCategoryID = @ProductCategoryID',
N'@ProductCategoryID int',
0);Sys.dm_exec_describe_first_result_set_for_object
In addition passing a T-SQL batch to a DMO to get result metadata, you may pass a stored
procedure or trigger to sys.dm_exec_describe_first_result_set_for_object.SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object
(OBJECT_ID(N'dbo.uspGetEmployeeManagers'), 0)Sp_describe_undeclared_parameters
This system stored procedure wins the SQL Server 2012 prize for most diffi cult to under-
stand name. Basically, you can pass in a T-SQL batch and zero or more of the parameters
included in the batch. Whatever you don’t declare, you’ll be returned the metadata for
those parameters. It’s best to see an example.sp_describe_undeclared_parameters
N'SELECT c.Name, s.Name, p.Name
FROM Production.ProductCategory c
JOIN Production.ProductSubcategory s
ON c.ProductCategoryID = s.ProductCategoryID
JOIN Production.Product p
ON s.ProductSubcategoryID = p.ProductSubcategoryID
WHERE c.ProductCategoryID = @ProductCategoryID
AND s.ProductSubcategoryID = @ProductSubcategoryID',
N'@ProductCategoryID int';parameter_ordinal name
----------------- ----------------------------
1 @ProductSubCategoryID...
Because the parameter @ProductSubCategoryID wasn’t passed into the system stored
procedure, the metadata that is returned is for this parameter.Offset and Fetch
Paging on a website looks so simple. You perform a search for your favorite widget. There
are 118 results returned. The fi rst 20 are shown, with the ability to go to the next page
if you want to see more. If the results paging was done in T-SQL prior to SQL Server 2012,
the backend code is likely ugly. Microsoft has made this task much simpler by provid-
ing OFFSET and FETCH in SQL Server 2012. It is integrated into the ORDER BY clause and
allows you to specify where to start returning rows (OFFSET) and how many rows to return
(FETCH).c16.indd 410c16.indd 410 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM
http://www.it-ebooks.info