0

I have a stored procedure with input and output parameter.

CREATE PROC [dbo].[pr_MySP]
    @date DATETIME,
    @usd MONEY OUTPUT
WITH ENCRYPTION
AS
BEGIN
    ...
END
GO

This stored procedure call another stored procedures, so I can't create UDF to do it. And now I need to use this proc result as column in another select. Somethin like this

select *, ([dbo].[pr_MySP] result) from ...

Is there any way to do this?

Oleg Volkov
  • 189
  • 4
  • 15

3 Answers3

0

For one SP run:

--Declare variables to hold output
DECLARE @usdout MONEY 
--Exec SP and hold value
EXEC pr_MySP @date = null, @usd = @usdout OUT
--Use received value in normal SELECT
select *, @usdout from ...

For multirow run, check possible approaches here: How do I execute a stored procedure once for each row returned by query?

For example, it's possible to use cursor, in each loop insert fresh data in temp table and then join it.

Danila Polevshchikov
  • 2,228
  • 2
  • 24
  • 35
0

Insert SP result into a temporary table:

create table #t (result ...)
declare @date datetime = getdate()
declare @usd money
insert into #t exec [dbo].[pr_MySP] @date = @date, @usd = @usd output
select * from #t
Alexey
  • 909
  • 6
  • 11
0

Instead of sp just you have to execute function as a column

CREATE FUNCTION fn_Function (@Id)
(   
    -- Add the parameters for the function here
    @param varchar(1000)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @param as Value
)
GO

Then you can write sp as

SELECT *,fn_Function(TBL.Id)FROM tbl_Table TBL
Arun J
  • 687
  • 4
  • 14
  • 27
Kishore
  • 9
  • 5