Row Count in Stored Procedure

  • HI

    I need to put a row count in my SP but when I do it doesnt show how many rows there are it just shows the results of the first query.

    How can I display the rowcount?

    thanks

  • try playing around with SELECT @@ROWCOUNT

  • I have the code below.

    I need the row count to display in the text file but it doesnt. I can see it in my query result.

    How can I get it to display in the text file?

    thanks so far.

    ALTER PROCEDURE [dbo].[PROCEDURE]

    AS

    BEGIN

    select field1,field2 from table

    declare @sql varchar(8000)

    select @@rowcount

    select @sql = 'bcp "exec PROCEDURE" queryout C:\bcp\pro.txt -c -t, -T -S'+ @@servername

    exec xp_cmdshell @sql

    END

  • guestuser31 (8/27/2009)


    I have the code below.

    I need the row count to display in the text file but it doesnt. I can see it in my query result.

    How can I get it to display in the text file?

    thanks so far.

    ALTER PROCEDURE [dbo].[PROCEDURE]

    AS

    BEGIN

    select field1,field2 from table

    declare @sql varchar(8000)

    select @@rowcount

    select @sql = 'bcp "exec PROCEDURE" queryout C:\bcp\pro.txt -c -t, -T -S'+ @@servername

    exec xp_cmdshell @sql

    END

    Is this meant to be self-referencing?

    Basically, you need to SELECT @@rowcount immediately after the execution of whatever the query is doing in order to see it accurately. In the example above, I'd put it between the SELECT statement and the DECLARE statement.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I don't think you can do this with the bcp command. You aren't running T-SQL here, you'd shelling out to a command prompt and then running a utility to pull data out.

    I'm not sure bcp supports a row count (http://msdn.microsoft.com/en-us/library/ms162802.aspx)

    What you could do is set up another proc, shell out to SQLCMD, run the proc, which then selects the same data, then selects @@rowcount, returns that value, append to the file.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply