Stored Procedure, Return Rowcount for two inserts

Question

Hey there, quick question for you gurus: I have a very simple stored procedure that creates two tables, my requirement is to output the numbe of rows inserted into each table, I stumbled across @@ROWCOUNT and this has allowed me to return the value from the last table, or if I put that in between, execution stops immediately after - which is not good, this is my code: Any suggestions would be appreciated.

SELECT * 
INTO dbo.NEWTABLE
FROM SOURCE
WHERE PROJECT_NUMBER LIKE '111aa'

SELECT * 
INTO dbo.NEWTABLE1
FROM SOURCE
WHERE PROJECT_NUMBER LIKE '222aa'
RETURN @@ROWCOUNT

Any suggestions would be appreciated, thanks!

Sheep

Answer 1

0

Instead of using RETURN you could simply SELECT @@ROWCOUNT like this

SELECT * 
INTO dbo.NEWTABLE
FROM SOURCE
WHERE PROJECT_NUMBER LIKE '111aa'

SELECT @@ROWCOUNT

SELECT * 
INTO dbo.NEWTABLE1
FROM SOURCE
WHERE PROJECT_NUMBER LIKE '222aa'

SELECT @@ROWCOUNT

answered Nov 25 '11 at 12:23 PM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

The problem with this approach is if you want to process both outputs within other T-SQL code. However, that doesn't seem to be concerning the @TelepathicSheep2, but I thought I should mention it anyway!

Nov 25 '11 at 12:56 PM ThomasRushton ♦

Yes I was just going for the assumption that OP wants to see 'output', and the return was stopping the batch mid-way

Nov 25 '11 at 01:05 PM Kev Riley ♦♦

He explicitly said it was for a stored procedure. I'm scratching my head wondering how you can access two results from a stored procedure in TSQL. I've never known of a general way. In this case you can insert them into one table (by insert EXEC) as they are the same and same no. of columns, but then you've got the problem of accessing them. No, horrible.

Nov 25 '11 at 01:55 PM Phil Factor

Didn't even read that bit! As ever, what the OP wants, and what the OP says and what the OP likes are mutually exclusive.....

Nov 25 '11 at 02:11 PM Kev Riley ♦♦

(comments are locked)

Answer 2

By far the most serviceable way to do this is to use two output variables to pass back the rowcounts. If you can't do that for some reason, then you can pass back the two rowcounts as columns in a single row table, but it is messy to pick up these values outside the stored procedure if you need to assign them to variables.

  -- ===============================================
  -- Create stored procedure with OUTPUT parameters
  -- ===============================================
  -- Drop stored procedure if it already exists
  IF EXISTS ( SELECT  *
              FROM    INFORMATION_SCHEMA.ROUTINES
              WHERE   SPECIFIC_SCHEMA = N'MySchema' AND SPECIFIC_NAME = N'MyProcedure' ) 
    DROP PROCEDURE MySchema.MyProcedure
  GO

  CREATE PROCEDURE MySchema.MyProcedure
    @MyParameter VARCHAR(10) = '',
    @MySecondParameter VARCHAR(10) = '',
    @TheFirstRowcount INT OUTPUT,
    @TheSecondRowcount INT OUTPUT
  AS 
  SELECT  *
  INTO    dbo.NEWTABLE
  FROM    SOURCE
  WHERE   PROJECT_NUMBER LIKE @MyParameter

  SELECT  @TheFirstRowcount = @@RowCount

  SELECT  *
  INTO    dbo.NEWTABLE1
  FROM    SOURCE
  WHERE   PROJECT_NUMBER LIKE @MyParameter

  SELECT  @TheSecondRowcount = @@RowCount
  GO

  -- =============================================
  -- Example to execute the stored procedure
  -- =============================================
  DECLARE @FirstRowcount_output int
  DECLARE @SecondRowcount_output int

  EXECUTE MySchema.MyProcedure '111aa','222aa', 
     @FirstRowcount_output OUTPUT, @SecondRowcount_output OUTPUT

  SELECT @FirstRowcount_output, @SecondRowcount_output
  GO

Stored Procedure, Return Rowcount for two inserts

Follow this question

Related Questions