handle resultset in SP

Question

handle resultset in SP

abhas

SSCarpal Tunnel

Points: 4681
More actions
July 31, 2014 at 10:07 pm

#293890

Hi,
I am having SP where I am inserting records into table. Now I want to return if result is inserted into table then 1 else 0.
How can I handle this?
Is this something like return with output parameter:
Thanks
Abhas.

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You can use @@ROWCOUNT and if the @@ROWCOUNT is greater then 0 then pass a 1 to the output parameter otherwise pass a 0 to the output parameter.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

You can count the rows before and after or use @@ROWCOUNT

😎

USE tempdb;

GO

DECLARE @PRECOUNT INT = 0;

DECLARE @RET_VAL INT =0;

DECLARE @TABLEX TABLE (IVAL INT NOT NULL);

SELECT @PRECOUNT = COUNT(*) FROM @TABLEX

INSERT INTO @TABLEX (IVAL) VALUES (1),(1);

SELECT @RET_VAL = SIGN ((SELECT COUNT(*) FROM @TABLEX) - @PRECOUNT)

SELECT @RET_VAL

INSERT INTO @TABLEX (IVAL) VALUES (1),(1),(1);

SELECT @RET_VAL = SIGN(@@ROWCOUNT);

SELECT @RET_VAL;

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 3

Thanks for reply,

But i want to bind this with output parameter.

If record insert then 1 else 0

Thanks

Abhas.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

What is it that you don't understand yet?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

The code below demonstrates how to use output parameters in a stored procedure

😎

CREATE PROCEDURE dbo.WasInsertSuccessfulQuestionMark

(

@ValueToInsert INT

,@ThisTellsMeIfSuccessful INT OUTPUT

)

AS

/*

usage

declare @return_value int;

declare @insert_value int;

set @insert_value = 123;

exec dbo.WasInsertSuccessfulQuestionMark @insert_value, @return_value OUTPUT;

select @return_value;

*/

/*Declare a variable holding the number of records in the

table before the insert

*/

DECLARE @PRECOUNT INT = 0;

/* Count the records */

SELECT @PRECOUNT = COUNT(*) FROM dbo.MyTableToInsertInto

/*Do the insert */

INSERT INTO dbo.MyTableToInsertInto (TheValueToBeInserted) VALUES (@ValueToInsert);

/* This statements determines if there is a difference between the record count

before and after. If 0 then nothing happened, if negative then more records

where deleted than inserted and if positive 1 then all thumbs up

*/

SELECT @ThisTellsMeIfSuccessful = SIGN ((SELECT COUNT(*) FROM dbo.MyTableToInsertInto) - @PRECOUNT)

GO