SQL Server select from stored procedure (9 Examples)

In this SQL Server tutorial, we will learn How to select data from stored procedures in SQL Server and will cover the following topics.

• SQL Server select from stored procedure
• SQL Server select from stored procedure into temp table
• SQL Server select from stored procedure return table
• SQL Server select from stored procedure result set
• SQL Server select from stored procedure with parameters
• SQL Server select from stored procedure openrowset
• SQL Server select from stored procedure into variable
• SQL Server select from stored procedure openquery
• SQL Server select columns from stored procedure
• SQL Server view select from stored procedure

SQL Server select from stored procedure

As the name implies, the SELECT statement in SQL Server is used to select data from a SQL Server table. And the data returned is saved in a result table known as the result-set.

Here is a standard syntax used for SELECT statements in SQL Server.

SELECT column1, column2, ...
FROM table_name;

A stored procedure in SQL Server is used to save a SQL code that we need to execute frequently. So, if we have a SQL query that we need to use multiple times then, we can save it as a stored procedure and then call it to run it.

Here is a standard syntax used to create and execute a stored procedure in SQL Server.

--creating a stored procedure
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

--executing a stored procedure
EXEC procedure_name;

Now, we all might know that we can easily use SELECT statements within a stored procedure. But while working on different projects, there can be a scenario where we need to select some data from the stored procedure.

So in this post, we will try to learn different ways through which we can use a SELECT statement to select some data from a stored procedure in SQL Server.

SQL Server select from stored procedure into temp table

Now we will understand how to select some data from a stored procedure and save it into a temporary table.

A temporary table in SQL Server is a table that exists temporarily in a database, and it gets automatically deleted once the connection to the database is dropped. Usually, the temporary tables are handy for holding immediate result sets which need to be accessed multiple times.

Now to understand this implementation of selecting some data from the store procedure into a temporary table, consider the following sample table.

For demonstration, first, we will create a stored procedure to select all the male records of the sample table. And then, we will store all the records returned by the procedure into the temporary table. And to create a stored procedure to select all the records of the sample, we are going to execute the following query.

CREATE PROCEDURE MaleRecords
AS
BEGIN
    SELECT * FROM SampleTable 
    WHERE gender='Male'
END
GO

In the above query, we are creating a stored procedure that selects all the male records from the sample table.

After this, we have to create a temporary table with the same column types, as defined in the sample table. Next, we have to use the INSERT INTO statement to insert all the records returned from the created procedure.

For this, we are going to execute the following query.

--Create temp table
CREATE TABLE #tmpMaleRecords (
	id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	gender VARCHAR(50),
	Country VARCHAR(50)
);
GO

--Storing values from procedure into temp table
INSERT INTO #tmpMaleRecords
EXEC  MaleRecords
GO

And now if we query the temporary table, we will get all the male records returned by the stored procedure.

Bonus- If you are using SSMS, and you receive an “Invalid object name” error while querying the temporary table then, follow the given steps.

1. Press “Ctrl+Shift+R” to refresh the local cache.
2. Or try to use the SELECT statement for temporary table after “INSERT INTO” statement in the same file.

SQL Server select from stored procedure return table

Now in this section, we will try to understand how to select some data from a table returned using stored procedure.

A stored procedure in SQL Server does not return a table directly neither we can directly select data from a stored procedure. But, for this implementation, we can use a SELECT statement within a stored procedure to return table data, and then we can use table variables to store the data returned from a stored procedure. After this, we can select data from a table variable.

Now for demonstration, consider the following code given below, used to create a stored procedure.

IF OBJECT_ID('TableReturn', 'P') IS NOT NULL  
   DROP PROCEDURE TableReturn;  
GO  
CREATE PROCEDURE TableReturn
AS    
   SELECT [id],[first_name],[gender] FROM SampleTable
RETURN  
GO  

In the query, first, we are using the IF block to check whether the procedure name is not used. After this, we are creating a procedure with the name “TableRetuen” and in the procedure, we are using the SELECT statement to return 3 columns from the sample table.

Next, we are going to execute the following query given below.

--Creating a table variable
declare @t table(id int, first_name varchar(50), gender varchar(50))

--Inserting values in table variable using procedure
insert @t
exec TableReturn

--Selecting values from table variable
SELECT * from @t

In the above example, first, we are creating a table variable and inserting values in the table variable using a stored procedure. After this, we are using the SELECT statement to select values inserted in the table variable.

After executing the above-mentioned example, we will get the following result-set as an output.

Read SQL Server stored procedure output parameter

SQL Server select from stored procedure with parameters

The true power of stored procedures lies in their capacity to pass multiple parameters and handle a variety of queries. In a stored procedure, we can pass multiple parameters and also use these parameter values to execute multiple queries within a stored procedure.

And in this topic, we will try to learn how to select data returned by a stored procedure having multiple parameters.

As already discussed, we cannot directly select data returned from a stored procedure using the SELECT statement. For this implementation, we can follow the approach mentioned below.

• First, create a stored procedure that uses multiple parameters to execute some task and return the result.
• Next, store the result returned by a stored procedure in a table variable.
• In the end, use the SELECT statement to fetch some data from the table variable.

Now, for demonstration, we will first create a stored procedure that will use the parameters to insert values in a sample table. And for this implementation, consider the following query given below.

IF OBJECT_ID('InsertRecord', 'P') IS NOT NULL  
   DROP PROCEDURE InsertRecord;  
GO  
CREATE PROCEDURE InsertRecord(
	@id int,
	@firstName varchar(50),
	@lastName varchar(50),
	@gender varchar(50),
	@country varchar(50)
)
AS    
   INSERT INTO [SampleTable] values (@id, @firstName, @lastName, @gender, @country)
   SELECT * FROM [SampleTable] WHERE [id]=@id
RETURN  
GO  

In the above example, we are creating a stored procedure with 5 input parameters. And the procedure will first use the parameters to insert a record in a sample table, and then we are using the SELECT statement to return the inserted record.

Next, to select the value returned by the stored procedure, we will execute the following query given below.

--Creating a table variable
declare @t table(
	id int,
	firstName varchar(50),
	lastName varchar(50),
	gender varchar(50),
	country varchar(50))

--Inserting values in table variable using procedure
insert @t
exec InsertRecord 11, 'Felicio', 'Batty', 'Female', 'Poland'

--Selecting values from table variable
select * from @t

By using the above query, first, we have created a table variable with the same columns as mentioned in the parameters. Next, we are inserting the value returned by the stored procedure in the table variable. In the end, we have used the SELECT statement to fetch the inserted record.

After execution, we will get the following result-set as an output.

Read String or binary data would be truncated in SQL Server

SQL Server select from stored procedure openrowset

OPENROWSET is a T-SQL function in SQL Server that lets you read data from a variety of sources. This approach is a one-time, ad hoc method of connecting and accessing distant data using OLE DB. It is an alternative to accessing tables in a connected server.

So, in this section, we will learn to implement the OPENROWSET function within a stored procedure. And after this, selecting the result returned by this stored procedure using SELECT statement.

For implementing example, we are going to create a stored procedure that will read a text file from a file system using OPENROWSET. And for this, consider the following query given below.

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE PROC txt_file_read 
    @os_file_name NVARCHAR(256) 
   ,@text_file VARCHAR(MAX) OUTPUT  
AS  
DECLARE @sql NVARCHAR(MAX) 
      , @parmsdeclare NVARCHAR(4000)  

SET NOCOUNT ON  

SET @sql = 'select @text_file=(select * from openrowset ( 
           bulk ''' + @os_file_name + ''' 
           ,SINGLE_CLOB) x 
           )' 

SET @parmsdeclare = '@text_file varchar(max) OUTPUT'  

EXEC sp_executesql @stmt = @sql 
                 , @params = @parmsdeclare 
                 , @text_file = @text_file OUTPUT

In the above query, we have created a procedure that will accept a file name, reads that file, and return its content as an output parameter. Now to execute the above procedure, we are using the following code.

DECLARE @t VARCHAR(MAX) 
EXEC txt_file_read 'E:\SampleText.txt', @t output 
SELECT @t AS [SampleText.txt]  

In the above code, first, we have declared a variable that will hold the value of the output parameter. Then, we are executing the procedure by providing the file path and variable. In the end, we are using the SELECT statement to display the result.

Read SQL Server DateTime vs Datetime2

SQL Server select from stored procedure into variable

There can be different ways to store the stored procedure result into a variable. The first is by using stored procedure output parameter, and the second is using table variable.

Using Ouput Parameters

A stored procedure in SQL Server can have one or more output parameters that return a value when we execute the procedure. And the output parameters are useful when we want individual values.

Let’s understand this with the help of an example. And for this demonstration, consider the following procedure given below.

CREATE PROCEDURE FindCountry
	@Name VARCHAR(50),
	@Country VARCHAR(50) OUTPUT
AS
BEGIN
	SELECT @Country = [country] FROM SampleTable 
	WHERE first_name = @Name
END

In the example, we have created a procedure with 1 input and 1 output parameter. And it will try to find the country name specified in the sample table corresponding to “first_name“. In the end, it will return the country variable as an output variable.

Next, to execute the procedure, we will execute the following query given below.

DECLARE	@emp_country VARCHAR(50) 
EXEC  FindCountry 'Chrisy', @emp_country OUTPUT

PRINT 'The employee is from ' + @emp_country

In the above code, first, we have declared a variable that will store the value of the output parameter. Next, while executing the procedure, we have given the values for both parameters. And in the last, we are using the PRINT statement to display the result.

Using Table Variable

A table variable can be useful in situations where we want to select some result-set values returned by the procedure.

For this, first, we need to declare a table variable with the columns that we want to store. And then, we have to insert the values in the table variable using the stored procedure. In the end, we can use the SELECT statement to select some data from the table variable.

Now for demonstration, consider the following stored procedure given below.

CREATE PROCEDURE Name_country_records
AS    
   SELECT [first_name],[Country] FROM SampleTable
RETURN  
GO  

By using the above query, we have created a procedure that will return all the “first name” and “country” values from the sample table.

Next, to execute the stored procedure, we will run the following query given below.

--Creating a table variable
declare @t table(Name varchar(50), Country varchar(50))

--Inserting values in table variable using procedure
insert @t
exec Name_Country_Records

--Selecting values from table variable
SELECT * from @t

In the above query, we are creating a table variable with 2 columns. After this, we are using the INSERT statement to insert values returned by the procedure. In the end, we are using the SELECT statement to fetch the values inserted in the table variable.

After executing the above example, we will get the following output shown below.

Read Create Foreign Key in SQL Server

SQL Server select columns from stored procedure

In SQL Server, we cannot directly select columns from a result returned by a stored procedure. And if try to use the SELECT statement with the procedure execution statement, the SQL Server will return an error.

But still, there are two ways through which we can select some columns from the stored procedure result.

The first method that we can use to select columns is by using temporary tables. And the complete implementation with example is already explained in the “SQL Server select from stored procedure into temp table” topic.

The second method is by using table variable in SQL Server and it is also explained in detail with an example in “SQL Server select from stored procedure into variable” topic.

Read Types of Backup in SQL Server

SQL Server select from stored procedure result set

Now, a result set can be referred to in two different ways in SQL Server, and both are mentioned below.

• A query’s output is called a result set. It could produce a one-row, one-column output or a million-row, 100-column output. In either case, it can be refered as result-set. And differenet methods to select data from a stored procedure is already mentioned in the above topics.
• There is also a clause named “RESULT SET” in SQL Server, that is used to declare alternate data types and column names for EXECUTED statement or Stored Procedure result sets.

And in this section, we will understand the implementation of the RESULT SET clause while executing the stored procedure.

Now for demonstration, consider the following stored procedure, used to select name and country column from sample table.

CREATE PROCEDURE Name_country_records
AS    
   SELECT [first_name],[Country] FROM SampleTable
RETURN  
GO  

Next, to execute the stored procedure with the RESULT SETS clause, we will use the following query given below.

EXEC Name_Country_Records 
WITH RESULT SETS
	(([EMPLOYEE NUMBER] varchar(5) NOT NULL,
	[NAME OF EMPLOYEE] varchar(15) NOT NULL));

In the above example, we have used the RESULT SETS clause after defining the execution statement. And with the RESULT SET clause, we have defined the column name and data type in which we want the result. For the name column, we have defined varchar(5) as a data type and for the country, we have defined varchar(15) as data type.

After executing the above example, we will get the following result.

Read Identity Column in SQL Server

SQL Server select from stored procedure openquery

There can be scenarios where we want to fetch some data from a stored procedure remotely. In such cases, we can use the OPENQUERY function in SQL Server

The OPENQUERY in SQL Server is a one-time ad-hoc connection mechanism used to connect to a remote server using the linked server. We can use the OPENQUERY function by following the given syntax.

OPENQUERY ( linked_server ,’query’ )

• The OPENQUERY requires two arguments: the query and the name of the associated server.
• The FROM clause of a SELECT, INSERT, UPDATE, or DELETE statement are utilized with the OPENQUERY function.

Now, we can use the SELECT statement with the OPENQUERY function to remotely fetch some table columns from a stored procedure. But before implementing, we have to enable the DATA ACCESS option in the remote server. And for this, we have to use the following query.

EXEC sp_serveroption 'server_name', 'DATA ACCESS', TRUE

We have to specify the exact server name in place of “server_name“.

Example

Next, we have created a stored procedure to fetch all the female records from the sample table. For this, we have used the following query.

CREATE PROCEDURE FemaleRecords
AS
BEGIN
    SELECT * FROM SampleTable 
    WHERE gender='Female'
END
GO

Now, to fetch the data from the above procedure, we will execute the following code given below.

SELECT * 
FROM OPENQUERY([DESKTOP-0EMHCK4\SQLEXPRESS], 'EXEC sqlserverguides.dbo.FemaleRecords')

By using the OPENQUERY function, we are executing the stored procedure remotely. And then, we are using the SELECT statement with OPENQUERY to fetch all the data return from the execution of the stored procedure.

After successfully implementing the above example, we will get the following output.

Read Saving changes is not permitted in SQL Server

SQL Server view select from stored procedure

In SQL Server, a VIEW is a virtual table that holds data from one or more tables. It does not exist in the database physically. The view name, just like the name of a SQL table, should be unique in the database. It includes a set of SQL queries for retrieving data from the database.

To create a view in SQL Server, we can use the following syntax.

CREATE VIEW view_name
AS
sql_statement
GO

Now in this section, we will try to understand how to select data from a stored procedure and store it in a view. And let’s understand this implementation with the help of an example.

For this implementation, we are using a stored procedure that fetches all the female records from a sample table.

CREATE PROCEDURE FemaleRecords
AS
BEGIN
    SELECT * FROM SampleTable 
    WHERE gender='Female'
END
GO

Next, to select and store the data from a stored procedure into a view, we will execute the following query given below.

CREATE VIEW FemaleRecordView
AS
SELECT * 
FROM OPENQUERY([DESKTOP-0EMHCK4\SQLEXPRESS], 
'EXEC sqlserverguides.dbo.FemaleRecords')

In the above example, first, we have used the CREATE VIEW clause to create a view. After this, we are using the SELECT statement with the OPENQUERY function to execute and fetch the data from the procedure. And we can easily query the view to get the procedure result

You may like the following sql server tutorials:

• Delete Duplicate Rows in SQL Server
• SQL Server Row_Number
• IDENTITY_INSERT in SQL Server
• SQL Server Add Column + Examples
• SQL Server Agent won’t start

In this SQL Server tutorial, we have learned How to select data from stored procedures in SQL Server and we have also covered the following topics.

• SQL Server select from stored procedure
• SQL Server select from stored procedure into temp table
• SQL Server select from stored procedure return table
• SQL Server select from stored procedure result set
• SQL Server select from stored procedure with parameters
• SQL Server select from stored procedure openrowset
• SQL Server select from stored procedure into variable
• SQL Server select from stored procedure openquery
• SQL Server select columns from stored procedure
• SQL Server view select from stored procedure