The following does not work unfortunately unless table1 already exists with columns with matching field names, data types and field order.
INSERT INTO table1
exec sp_GetProducts @CategoryID=12
Stored procedures that returns tabular results in SQL Server do not allow saving the results in a new table like we would expect (The receiving table must exist with all columns' data types and fields names ahead of time for the query to work).
The workaround is to use either OpenRowset or by creating a new function.
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT *
INTO table1
FROM OPENROWSET (
'sqlncli',
'server=(local)\SQL12;trusted_connection=yes',
'exec Northwind.dbo.sp_GetProducts @CategoryID=12'
);
When using a user-defined function instead, we are basically re-writing the stored procedure in user-defined function.
CREATE FUNCTION GetProductsByCategory
(
@CategoryID int
)
RETURNS TABLE
AS
RETURN
select * from dbo.Products where CategoryID=@CategoryID
GO
SELECT *
INTO table1
FROM GetProductsByCategory(12);
GO
