SQL - get output parameter value

Question

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

which rdbms are you using?

Saharsh Shah
– Saharsh Shah

2015-01-06 13:56:40 +00:00
Commented Jan 6, 2015 at 13:56 — Saharsh Shah
– Saharsh Shah, Commented Jan 6, 2015 at 13:56

Saharsh Shah · Accepted Answer · 2015-01-06 14:02:58Z

1

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

edited Jan 6, 2015 at 14:02

answered Jan 6, 2015 at 13:57

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pரதீப் Over a year ago

This will throw error you need to use Nvarchar when sp_executesql is used to execute dynamic sql

Pரதீப் Over a year ago

I don't know what is that OP's requirement. But if you are using sp_executesql then the variable should be declared as Nvarchar else you will get error

Pரதீப் · Accepted Answer · 2015-01-06 13:59:33Z

0

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

answered Jan 6, 2015 at 13:59

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

SQL - get output parameter value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related