Stored Procedures
-
Hi,
I created a stored procedure that creates a temporary table, fills it with data, and runs a select and returns the data from the one column in temporary table and then drops the temporary table.
How can I grab the results from the stored procedure and use it in a query. I was to exclude the data in the from the stored procedure in my query.
I can't use a temporary table in the query - I'm not allowed.
-
Hi,
Does this help?
IF OBJECT_ID(N'usp_InsertData', N'P') IS NOT NULL
DROP PROCEDURE usp_InsertData
GO
CREATE PROCEDURE usp_InsertData
AS
BEGIN
SELECT * INTO #Test
FROM
(
SELECT 1 AS TestData UNION ALL
SELECT 2 AS TestData UNION ALL
SELECT 3 AS TestData UNION ALL
SELECT 4 AS TestData UNION ALL
SELECT 5 AS TestData UNION ALL
SELECT 6 AS TestData
) A
SELECT * FROM #Test
DROP TABLE #Test
END
GO
sp_executesql N'exec usp_InsertData'Thanks,
Debasis.
-
Can you use a table variable?
-
Ok how do I use a Table Variable
Additional Info:
The temporary Table in my Stored Procedures reads a text file to populate the temporary Table. I then use a Select * to read all of the data. I want to use the data in the temporary Table data as an exclude in my query
-
Have you created or are you creating the stored proc?
Why are you not allowed to use a temp table?
what do you want to do with the results of the query?
If you want to create a SP to populate data and then query from it there are many options. for example common table expressions CTE
;With Test_dataas ( select col1,col2,col3,col4
from some table a
join some other table b on a.col=bol
where some condition )
select col1
from Test_data***The first step is always the hardest *******
-
can you share you SP with us?
i guess your using bulk insert to read from the txt file and insert into a temp table?
what do you want use the data in the temp table to exclude?
are you trying to process rows from the text file into another table?
***The first step is always the hardest *******
-
Have you created or are you creating the stored proc? Yes
Why are you not allowed to use a temp table? Politics
what do you want to do with the results of the query?
I want to use stored procedure query to be a list of things I want to exclude from the query I run
Select Name
FROM myDB
Where Name NOT IN (Results from SP)
- TJ_T wrote:
Ok how do I use a Table Variable
A quick example:
DECLARE @Who TABLE
(
SPID CHAR(5)
,Status NVARCHAR(50)
,Login NVARCHAR(50)
,HostName NVARCHAR(50)
,BlkBy VARCHAR(50)
,DBName NVARCHAR(50)
,Command NVARCHAR(50)
,CPUTime VARCHAR(50)
,DiskIO VARCHAR(50)
,LastBatch VARCHAR(50)
,ProgramName NVARCHAR(50)
,SPID2 CHAR(50)
,REQUESTID CHAR(50)
);
INSERT @Who
(
SPID
,Status
,Login
,HostName
,BlkBy
,DBName
,Command
,CPUTime
,DiskIO
,LastBatch
,ProgramName
,SPID2
,REQUESTID
)
EXEC sys.sp_who2;
SELECT *
FROM @Who w; -
CREATE PROCEDURE [dbo].[Excluded_List]
AS
CREATE TABLE #TempTable (Software NVARCHAR(MAX));
BULK INSERT #TempTable
FROM 'c:\temp\EXCLUDE_LIST.txt'
WITH
(
DATAFILETYPE= 'char',
FIELDTERMINATOR=',',
ROWTERMINATOR='\r' -- Carriage Return
);
SELECT * FROM #TempTable;
-- Remove our temporary table.
DROP TABLE #TempTable
-
so you cant create a temp table but whoever created this SP can? politics
why not just convert the SP into a select and run your query before you drop the temp table?
you could look to use open rowset but you may need to set some advance settings and well if you cant create a temp table they may not let you do this either
select * FROM openrowset('SQLNCLI',
'Server=localhost;Trusted_Connection=yes;',
'EXEC [YOUR_SP_HERE]')***The first step is always the hardest *******
-
Yes I tried Openrowset, but unfortunately they have the SQL Server 2017 in compatibility mode 2012 and openrowset won't work.
- TJ_T wrote:
Yes I tried Openrowset, but unfortunately they have the SQL Server 2017 in compatibility mode 2012 and openrowset won't work.
can you tell us which error messages you get when you try it?
As far as I am aware Openrowset does not care about the compatibility mode of a database
-
Why aren't you using the method Phil Parkin provided to insert the results from the SP into a table. Then you can do with it whatever you like?
i.e:
CREATE TABLE #TempTable (Software NVARCHAR(MAX));
INSERT INTO #TempTable(Software)
EXEC [dbo].[Excluded_List]; - Jonathan AC Roberts wrote:
I would say that won't work as the SP is already creating a temp table using a insert into
that can give
Msg 8164, Level 16, State 1, Procedure xxx, Line yyy
An INSERT EXEC statement cannot be nested.
But the main reason the OP gave was "Why are you not allowed to use a temp table? Politics"
- frederico_fonseca wrote:Jonathan AC Roberts wrote:
I would say that won't work as the SP is already creating a temp table using a insert into that can give Msg 8164, Level 16, State 1, Procedure xxx, Line yyy An INSERT EXEC statement cannot be nested. But the main reason the OP gave was "Why are you not allowed to use a temp table? Politics"
I just tried it and it works, here's some code you can just run:
CREATE PROCEDURE [dbo].[Excluded_List] AS
CREATE TABLE #TempTable (Software NVARCHAR(MAX));
INSERT #TempTable
SELECT * FROM (VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i')) T(Software)
SELECT * FROM #TempTable;
-- Remove our temporary table.
DROP TABLE #TempTable
GO
CREATE TABLE #TempTable (Software NVARCHAR(MAX));
INSERT INTO #TempTable(Software)
EXEC [dbo].[Excluded_List]
GO
SELECT * FROM #TempTable
GO
DROP TABLE #TempTable
GO
DROP PROCEDURE [dbo].[Excluded_List]
GO
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply