How to execute a stored procedure inside a select query
Thanks @twoleggedhorse.
Here is the solution.
First we created a function
CREATE FUNCTION GetAIntFromStoredProc(@parm Nvarchar(50)) RETURNS INTEGER
AS
BEGIN
DECLARE @id INTEGER
set @id= (select TOP(1) id From tbl where col=@parm)
RETURN @id
ENDthen we do the select query
Select col1, col2, col3,
GetAIntFromStoredProc(T.col1) As col4
From Tbl as T
Where col2=@parm
How to SELECT FROM stored procedure
You can use a User-defined function or a view instead of a procedure.
A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT
statement.
Execute a stored procedure from within another stored procedure's SELECT statement?
The approach what you have tried is invalid. Instead of the X
as the stored procedure convert it as user-defined function. like the below
Create function dbo.fnGetTypeDetail
(
@type varchar(50)
)
returns varchar(100)
As
Begin
return --do your operation;
End
And replace your query as:
SELECT name, type, dbo.fnGetTypeDetail(type) AS TypeDetail
FROM table
For sample, I created a scalar function. Based on your requirement you can create inline table valued function as per the example
Use the result of Stored Procedure as column in SELECT statement
You should consider NOT naming your stored procedures using sp_ as prefix. That is a system reserved prefix and it might cause performance issues. You can read more about it on this article by Aaron Bertrand.
In order to do what you need you could first create a user defined function (as mentioned to you in the comments) that would do the following:
create function myfunc(@Id int, @Country varchar(50))
returns money
as
begin
declare @amount money
select @amount = amount
from Customer
where Id = @Id and Country = @Country
return @amount
end
Only then you can try what you wanted to do:
select c.id,
amount = dbo.myfunc (c.id, c.country)
from customer c
oracle call stored procedure inside select
use a PL/SQL loop:
BEGIN
FOR c IN (SELECT field1, field2 FROM mytable) LOOP
my_proc(c.field1, c.field2);
END LOOP;
END;
Related Topics
Some Sort of "Different Auto-Increment Indexes" Per a Primary Key Values
Select One Row Per Index Value with Max Column Value
Using Start Date and End Date in Access Query
Find Referenced Field(S) of Foreign Key Constraint
Where Should I Start with My Opc-Ua Client
Sample Query to Show Cardinality Estimation Error in Postgresql
How to Give an Alias to a Table in Oracle
Order by with Inner Query, Giving Ora-00907 Missing Right Parenthesis
Column Reference Is Ambiguous in Postgresql Function
Pivot on Multiple Fields and Export from Access
How to Return Two Columns with Function
How to Build a Summary by Joining to a Single Table with SQL Server
How to Get Value Using Join Table with Different Values
How Can This SQL Be Wrong? What am I Not Seeing
Query JSON Dictionary Data in SQL
Access a Column Aliases in the Where Clause in Postgresql
What Did Mongodb Not Being Acid Compliant Before V4 Really Mean