[dba-SQLServer] Returning a dataset

Arthur,

>TOP n also depends on which version of SQL Server you are using. In 2005
and 2008, TOP accepts an argument so you don't have to construct the
statement, you can just pass in >a variable and plug it into the code:

>CREATE PROCEDURE myProc (@n int)
>AS
>    SELECT TOP @n [PERCENT] * FROM someTables         -- percent is
optional
>    ... etc.

OH now THAT is cool.   

So what is the syntax for returning the dataset?  Is the select statement
just implicitly assigned as the output?


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Tuesday, March 25, 2008 6:41 AM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Returning a dataset

Depends what you mean by data set. Result set is the more common term in SQL
circles, but that may be what you mean. Update, delete and insert sprocs
don't really return result sets, although they do tell you how many rows
were affected. Select sprocs always return result sets, although said set
may consist of zero rows.

TOP n also depends on which version of SQL Server you are using. In 2005 and
2008, TOP accepts an argument so you don't have to construct the statement,
you can just pass in a variable and plug it into the code:

CREATE PROCEDURE myProc (@n int)
AS
    SELECT TOP @n [PERCENT] * FROM someTables         -- percent is optional
    ... etc.

Arthur