CODEWITHSUNDEEP
sqlselectstored-procedures
PeteTheGreek
PeteTheGreek

Reputation: 729

SQL - get output parameter value

I am trying to output a value from a select statement, but not sure how to complete this. I call the procedure passing example values 'mypackage' (package_id) and 117 (total_cost). I need it to output the total_cost parameter as a INT.

ALTER PROCEDURE [aren1002].[ArenDataGetPackageWithRoomCost]
(
    @room_type as int=NULL,
    @package_id as varchar(30)=NULL,
    @total_cost as INT OUTPUT,
    @sql as varchar(400)
)
AS
SET NOCOUNT ON

BEGIN
SELECT @total_cost AS
    string @sql = 'SELECT ' + @package_id + ' from aren1002.HOLIDAY_ROOM where room_standard_id = ' + @room_type
    exec @sql
END

Upvotes: 1

Views: 107

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

For SQL SERVER

ALTER PROCEDURE [aren1002].[ArenDataGetPackageWithRoomCost]
(
    @room_type AS INT=NULL,
    @package_id AS VARCHAR(30)=NULL,
    @total_cost AS INT OUTPUT,
    @sql AS VARCHAR(400)
)
AS
SET NOCOUNT ON

BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'SELECT @total_cost = ' + @package_id + ' from aren1002.HOLIDAY_ROOM where room_standard_id = ' + @room_type
    sp_executesql @sql, '@total_cost AS INT OUTPUT', @total_cost AS INT OUTPUT
END

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

ALTER PROCEDURE [aren1002].[ArenDataGetPackageWithRoomCost]
(
    @room_type as int=NULL,
    @package_id as varchar(30)=NULL,
    @total_cost as INT OUTPUT,
    @sql as varchar(400)
)
AS
SET NOCOUNT ON

BEGIN
    DECLARE @sql NVARCHAR(MAX)
    select @sql = 'SELECT @total_cost=' + @package_id + ' from aren1002.HOLIDAY_ROOM 
      where room_standard_id = ' + @room_type
    exec sp_executesql @sql,N'@total_cost INT Output',@total_cost output
END

Upvotes: 0

Related Questions