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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 returning 2 values from SP
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

insanepaul
Posting Yak Master

178 Posts

Posted - 01/21/2010 :  11:26:56  Show Profile  Reply with Quote
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


Edited by - insanepaul on 01/21/2010 11:36:21

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/21/2010 :  11:35:19  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 01/21/2010 :  11:45:25  Show Profile  Reply with Quote
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.
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts

Posted - 01/21/2010 :  11:46:02  Show Profile  Reply with Quote

@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


[/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.

Edited by - insanepaul on 01/21/2010 11:46:42
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts

Posted - 01/21/2010 :  11:47:12  Show Profile  Reply with Quote
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

Edited by - insanepaul on 01/21/2010 11:48:19
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 01/21/2010 :  11:52:22  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/21/2010 11:52:43
Go to Top of Page

insanepaul
Posting Yak Master

178 Posts

Posted - 01/21/2010 :  11:56:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000