Set a variable being embedded in SQL

[djdidge]

Hi there,

I have finally come to the point where i can't proceed without learning how to use return parameters!

i need to return minGroupID or ProductUID ... they are the same. So somehow i need to set the variable @Returny

ideas?
............................................................


CREATE PROCEDURE [dbo].[InsertProductRecordBasedOn]
@ProdID as int,
@Returny as int OUTPUT

AS

INSERT INTO tblProductRecord
SELECT
(SELECT MIN(ProductUID) + 1 AS minGroupID
FROM tblProductRecord
WHERE ((ProductUID + 1) NOT IN
(SELECT ProductUID
FROM tblProductRecord))) as ProductUID,
ProductName + "-Copy", TypeUID, CampaignUID, PMUID, PPUID, StartDate, EndDate, ChargeUID, Objective, Measure, Confidential, Notes, Fellows, Chartered,
Technical, TechnicalTrainees, Trainees, Students, Affiliates, Retired, Members, Consumers, Business, Government, Professionals, Prospective,
Academia, Employers, Media, Completed, getdate(), getdate(), CreatorID, Deleted, DateDelivered, LifeExpect, BookID, EBMSid,
FoundID, eBriefFreq, Publically, Cancelled, NumberAtPublish
FROM tblProductRecord
WHERE (ProductUID = @ProdID)

GO

 

[djdidge]

i tried this .. .didnt work (tried to use the AS to populate @Returny

INSERT INTO tblProductRecord
SELECT
(SELECT MIN(ProductUID) + 1 AS @Returny
FROM tblProductRecord
WHERE ((ProductUID + 1) NOT IN
(SELECT ProductUID
FROM tblProductRecord))) as ProductUID,
ProductName + "-Copy", TypeUID, CampaignUID, PMUID, PPUID, StartDate, EndDate, ChargeUID, Objective, Measure, Confidential, Notes, Fellows, Chartered,
Technical, TechnicalTrainees, Trainees, Students, Affiliates, Retired, Members, Consumers, Business, Government, Professionals, Prospective,
Academia, Employers, Media, Completed, getdate(), getdate(), CreatorID, Deleted, DateDelivered, LifeExpect, BookID, EBMSid,
FoundID, eBriefFreq, Publically, Cancelled, NumberAtPublish
FROM tblProductRecord
WHERE (ProductUID = @ProdID)

 

[djdidge]

had it answered in another forum...

You just need to assign the value you're interested in to a previously declared variable and RETURN it at the end; viz:
CREATE PROC ReturnAsExitValue
AS BEGIN
DECLARE @RowID INT
SELECT @RowID = MIN(TransactionID) FROM tbl_Transactions
RETURN @RowID
END

then when calling the proc, you can do
SELECT @myValue = EXEC ReturnAsExitValue

 

[donutman]

That works for INT parameters, but even so it isn't best practice. The RETURN value is designed to return the success or failure of the SP. Had you assigned @ReturnY the way you had assigned @RowID it would have worked in your first example and would have been preferred.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[djdidge]

noted thx