| Author |
 Topic  |
|
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 01/21/2010 : 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
|
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
|
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
United Kingdom
22859 Posts |
Posted - 01/21/2010 : 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 - 01/21/2010 : 11:46:02
|
@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 |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 01/21/2010 : 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 |
Edited by - insanepaul on 01/21/2010 11:48:19 |
|
|
|
Kristen
Test
United Kingdom
22859 Posts |
Posted - 01/21/2010 : 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
|
Edited by - Kristen on 01/21/2010 11:52:43 |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 01/21/2010 : 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 |
|
|
| |
Topic |
|
returning 2 values from SP