asked
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
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
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!
Yes I was just going for the assumption that OP wants to see 'output', and the return was stopping the batch mid-way
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.
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.....
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
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
