SQL server stored procedure return a table
Consider creating a function which can return a table and be used in a query.
https://msdn.microsoft.com/en-us/library/ms186755.aspx
The main difference between a function and a procedure is that a function makes no changes to any table. It only returns a value.
In this example I'm creating a query to give me the counts of all the columns in a given table which aren't null or empty.
There are probably many ways to clean this up. But it illustrates a function well.
USE Northwind
CREATE FUNCTION usp_listFields(@schema VARCHAR(50), @table VARCHAR(50))
RETURNS @query TABLE (
FieldName VARCHAR(255)
)
BEGIN
INSERT @query
SELECT
'SELECT ''' + @table+'~'+RTRIM(COLUMN_NAME)+'~''+CONVERT(VARCHAR, COUNT(*)) '+
'FROM '+@schema+'.'+@table+' '+
' WHERE isnull("'+RTRIM(COLUMN_NAME)+'",'''')<>'''' UNION'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table and TABLE_SCHEMA = @schema
RETURN
END
Then executing the function with
SELECT * FROM usp_listFields('Employees')
produces a number of rows like:
SELECT 'Employees~EmployeeID~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("EmployeeID",'')<>'' UNION
SELECT 'Employees~LastName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("LastName",'')<>'' UNION
SELECT 'Employees~FirstName~'+CONVERT(VARCHAR, COUNT(*)) FROM dbo.Employees WHERE isnull("FirstName",'')<>'' UNION
I do this frequently using Table Types to ensure more consistency and simplify code. You can't technically return "a table", but you can return a result set and using INSERT INTO .. EXEC ...
syntax, you can clearly call a PROC and store the results into a table type. In the following example I'm actually passing a table into a PROC along with another param I need to add logic, then I'm effectively "returning a table" and can then work with that as a table variable.
/****** Check if my table type and/or proc exists and drop them ******/
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'returnTableTypeData')
DROP PROCEDURE returnTableTypeData
GO
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'myTableType')
DROP TYPE myTableType
GO
/****** Create the type that I'll pass into the proc and return from it ******/
CREATE TYPE [dbo].[myTableType] AS TABLE(
[someInt] [int] NULL,
[somenVarChar] [nvarchar](100) NULL
)
GO
CREATE PROC returnTableTypeData
@someInputInt INT,
@myInputTable myTableType READONLY --Must be readonly because
AS
BEGIN
--Return the subset of data consistent with the type
SELECT
*
FROM
@myInputTable
WHERE
someInt < @someInputInt
END
GO
DECLARE @myInputTableOrig myTableType
DECLARE @myUpdatedTable myTableType
INSERT INTO @myInputTableOrig ( someInt,somenVarChar )
VALUES ( 0, N'Value 0' ), ( 1, N'Value 1' ), ( 2, N'Value 2' )
INSERT INTO @myUpdatedTable EXEC returnTableTypeData @someInputInt=1, @myInputTable=@myInputTableOrig
SELECT * FROM @myUpdatedTable
DROP PROCEDURE returnTableTypeData
GO
DROP TYPE myTableType
GO
A procedure can't return a table as such. However you can select from a table in a procedure and direct it into a table (or table variable) like this:
create procedure p_x
as
begin
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t values('a', 1,1,1)
insert @t values('b', 2,2,2)
select * from @t
end
go
declare @t table(col1 varchar(10), col2 float, col3 float, col4 float)
insert @t
exec p_x
select * from @t