returning 2 values from SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

returning 2 values from SP

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-21 : 11:26:56

In the trimmed SP below I'm returning a value from the insert part but I need to use the same SP to return a value from a select statement. Do I just use another BEGIN and END? I'm not sure how I would do this.


@ItemId INT, @PageNumber INT, @DashboardPageID INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
    
	INSERT INTO Page (
			DashboardID
	)
	SELECT	DashboardID,
			@PageNumber
	FROM	Dashboard
	WHERE	ItemID = @ItemId;

	SET  @DashboardPageID =  SCOPE_IDENTITY();
END

BEGIN
    SELECT ......
END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 11:35:19

just do:


@ItemId INT, @PageNumber INT, @DashboardPageID INT OUTPUT, @otherVariable <x> OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
    
	INSERT INTO Page (
			DashboardID,
	)
	SELECT	DashboardID,
			@PageNumber
	FROM	Dashboard
	WHERE	ItemID = @ItemId;

	SET  @DashboardPageID =  SCOPE_IDENTITY();

	-- Your second stuff here to @otherVariable
	SELECT ......
END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 11:45:25

Or just do a SELECT and your application will get the RecordSet back (as well as the OUTPUT parameter's value)

Depends whether you want a Table back, or just a second scalar value.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-21 : 11:46:02

[code]
@ItemId INT, @PageNumber INT, @DashboardPageID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Page (
DashboardID
)
SELECT DashboardID,
@PageNumber
FROM Dashboard
WHERE ItemID = @ItemId;

SET @DashboardPageID = SCOPE_IDENTITY();
END

BEGIN
SELECT ......
END

[/code]
[/quote]

Thanks for the help, I'm getting some errors and wanted to know if to use @IsDashboardPublic BIT <x> OUTPUT or @IsDashboardPublic BIT <DashboardPublic> OUTPUT........where DashboardPublic is what I'm returning from the select command.

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-21 : 11:47:12

quote:
Originally posted by Kristen

Or just do a SELECT and your application will get the RecordSet back (as well as the OUTPUT parameter's value)

Depends whether you want a Table back, or just a second scalar value.

In this instance I need a second scalar not a table

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 11:52:22

Hopefully this:


@ItemId INT, @PageNumber INT, @DashboardPageID INT OUTPUT, @IsDashboardPublic BIT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
    
	INSERT INTO Page (
			DashboardID,
	)
	SELECT	DashboardID,
			@PageNumber
	FROM	Dashboard
	WHERE	ItemID = @ItemId;

	SET  @DashboardPageID =  SCOPE_IDENTITY();

	-- Your second stuff here to @otherVariable
	SELECT @IsDashboardPublic = ... some expression ...
END

insanepaul
Posting Yak Master

178 Posts

Posted - 2010-01-21 : 11:56:01

quote:

Originally posted by Kristen

Hopefully this:


@ItemId INT, @PageNumber INT, @DashboardPageID INT OUTPUT, @IsDashboardPublic BIT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
    
	INSERT INTO Page (
			DashboardID,
	)
	SELECT	DashboardID,
			@PageNumber
	FROM	Dashboard
	WHERE	ItemID = @ItemId;

	SET  @DashboardPageID =  SCOPE_IDENTITY();

	-- Your second stuff here to @otherVariable
	SELECT @IsDashboardPublic = ... some expression ...
END

Excellent, that executed thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources