SQL Server Forums
Profile | Register | 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
 New Topic  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
22403 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
22403 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  
 New 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.07 seconds. Powered By: Snitz Forums 2000