x

Stored Procedure, Return Rowcount for two inserts

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
more ▼

asked Nov 25, 2011 at 09:25 AM in Default

TelepathicSheep2 gravatar image

TelepathicSheep2
140 14 15 17

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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
more ▼

answered Nov 25, 2011 at 12:23 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

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, 2011 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, 2011 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, 2011 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, 2011 at 02:11 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Nov 25, 2011 at 09:34 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

I've never worked with output variables, if you were able to provide some sample code it'd be great (save me a lot of time). Otherwise, I'll google it and try my best - thanks for your help.

Sheep
Nov 25, 2011 at 10:09 AM TelepathicSheep2
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x414
x107
x14

asked: Nov 25, 2011 at 09:25 AM

Seen: 8555 times

Last Updated: Nov 25, 2011 at 09:25 AM