Can we use stored procedures in a SELECT statement in SQL SERVER?
- How-Tos FAQs
- December 17, 2018
We can not directly use stored procedures in a SELECT statement. The database objects that can be used in a SELECT statement are:
What can be used in a SELECT statement?
- Table-Valued Function
- View
If possible, we can convert a stored procedure’s logic into a Table-valued function or in a View.
Strictly Using the SQL Alternative
Strictly using SQL, let us consider the below-stored procedure.
IF (OBJECT_ID('orders') IS NOT NULL)
DROP TABLE orders
GO
-- Let us create a table ‘orders’ to be used in the stored procedure
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO orders
SELECT 1,'Jack', '2020-02-03', 4 UNION ALL
SELECT 1,'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19 UNION ALL
SELECT 3,'John', '2020-02-03', 45
GO
IF (OBJECT_ID('sp_orders') IS NOT NULL)
DROP PROCEDURE sp_orders
GO
-- Creating stored procedures to return the information of a given customer.
CREATE PROC sp_orders
@customer_name VARCHAR(50)
AS
BEGIN
SELECT order_id,
customer_name,
order_date,
total_orders
FROM orders
WHERE customer_name = @customer_name
END
GO
-- Below is the syntax for calling a stored procedure. We cannot use stored procedure in a SELECT statement
EXEC sp_orders 'Jack'
GO
-- Output
order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00.000 4
1 Jack 2020-02-03 00:00:00.000 4Convert to Table-Valued Function
IF (OBJECT_ID('udf_orders') IS NOT NULL)
DROP FUNCTION udf_orders
GO
-- Table-Valued Function equivalent to above stored procedure
CREATE FUNCTION udf_orders (
@customer_name VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
SELECT order_id,
customer_name,
order_date,
total_orders
FROM orders
WHERE customer_name = @customer_name
GO
-- Now, the function ‘udf_orders’ can be used in a SELECT statement.
SELECT *
FROM udf_orders('Jack')
-- Output
order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00.000 4
1 Jack 2020-02-03 00:00:00.000 4Convert to View
IF (OBJECT_ID('vw_order') IS NOT NULL)
DROP VIEW vw_order
GO
-- View equivalent to above stored procedure
CREATE VIEW vw_order
AS
SELECT order_id,
customer_name,
order_date,
total_orders
FROM orders
GO
-- Now, the VIEW 'vw_order' can be used in a SELECT statement
SELECT *
FROM vw_order
WHERE customer_name = 'Jack'
-- Output
order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00.000 4
1 Jack 2020-02-03 00:00:00.000 4The above solutions cannot be applied in all cases. If there are any Data Definition Language(DDL) operations like creating/altering tables, updating/deleting table data in a stored procedure, it cannot be converted into Table-Valued Function or View.
We may not have permission to convert stored procedure into Table-Valued Function or View even if technically possible.
In this case, we have one workaround. We can follow the below steps.
Step 1: Insert the output of the stored procedure into a temporary table
Step 2: Use that temporary table in a SELECT statement.
IF (OBJECT_ID('temp..#orders') IS NOT NULL)
DROP TABLE #orders
GO
-- Create a temporary table to hold the output of stored procedure
CREATE TABLE #orders (
order_id INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
-- INSERT the output of stored procedure to the temporary table
INSERT INTO #orders
EXEC sp_orders 'Jack'
-- Use the temporary table in SELECT statement
SELECT * FROM #orders
-- Output
order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00.000 4
1 Jack 2020-02-03 00:00:00.000 4More On Views – Leveraging the Power Of Datameer
Datameer offers a more accessible and exciting way to use the contents of a table; you can directly create and save a view that can be queried at any moment with a ‘SELECT’ statement without affecting the original data design.
If analysts are not technical and prefer a GUI-based approach, Datameer provides an easy-to-use interface that enables the same end result as if the transformations were written directly in Snowflake.
Watch the video below to see how to perform this task with a few clicks in Datameer:
Up Next:
Read How to UPDATE a table with a SELECT statement in SQL server?
