[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

On 3/24/08, jwcolby <jwcolby at colbyconsulting.com> wrote:
>
> A stored procedure can return a data set correct?  Is that data set
> updateable?
>
> I need to run a stored procedure, where I pass in the Value for the TOP N
> (the N part) and then build up a SQL statement and execute that inside of
> the sp.  That sp needs to return the data set to another SP, and that SP
> has
> to update a field in the data set returned by the first sp.
>
> Is this possible?
>
> ATM I have a view called TopN, which returns a date field.  I have a query
> that updates that date field to today's date.  The problem is that the
> TopN
> view is hard coded with a number (30000 for today's order) but that value
> could be any number.  So I need to turn the view into a SP where I can
> pass
> in the number of records to update and then pass the set up date fields
> back
> to an update SP to do the update.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>