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

avatar image

TelepathicSheep2
140 15 15 19

(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

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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

avatar image

Phil Factor
4.2k 8 23 20

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.

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:

x457
x138
x17

asked: Nov 25, 2011 at 09:25 AM

Seen: 12492 times

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

Copyright 2016 Redgate Software. Privacy Policy