Simple question

Question

Post reply

Simple question

sqlnaive

SSCoach

Points: 17437
More actions
August 5, 2011 at 5:24 am

#104084

Declare @Sno int
exec @Sno = exec proc1 @p1, @p2
/*Say exec proc1 @p1, @p2 returns int value 2 */
select @Sno-- It does not show any value neither throws any error
Though we can do it using table vriable or output variable.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

it can be confusing how you are able to get data back from a procedure.

there are three ways, and you can use them ALL at the same time:

1. reutrn a resultset of rows/table.

2. output parameters

3. the RETURN command to return an integer.

CREATE Proc ReturningStuff(@name varchar(128),@id int OUTPUT) --the output param can be returned

AS

BEGIN

SELECT @id = object_id from sys.tables where name = @name -- assigning a value to the output variable

SELECT * FROM sys.tables where name = @name --creating a dataset/table to return back

--maybe you wanted to know how many rows?

--return can ONLY return an integer...

--usually 0 for no errors, anything else for failure SQL defaults the return to zero if not explcitly returning a value.

RETURN @@rowcount

END

GO

--and to test:

declare @AnObject varchar(128),

@myid as integer,

@results integer

SET @AnObject= 'TallyCalendar'

EXEC @results = ReturningStuff @AnObject,@myid OUTPUT --you have to say OUTPUT to sue the variable for output

PRINT CONVERT(varchar,@results) + ' Rows int he table?'

PRINT CONVERT(varchar,@myid) + ' objectID?'

/*

(1 row(s) affected)

1 Rows int he table?

405576483 objectID?

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlnaive SSCoach Points: 17437 More actions · Answer 2

Thanks Powell. Since I could not change the existing procedure, tried putting the result into table variable.