August 5, 2014 at 5:08 am
Hello,
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
Can you help what all these things I need to take when I convert it to as a SQL query for report generation?
August 5, 2014 at 5:12 am
You could use a table variable instead of the temp table. Be aware that this might have an impact on performance.
Do you need a temp table by the way? Maybe this extra step can be eliminated.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:13 am
On a sidenote: why doesn't the business want a SP or temp table? Any reasons for that decision?
(and why is the business meddling with technical stuff?)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:13 am
*duplicate post*
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 5, 2014 at 5:16 am
Junglee_George (8/5/2014)
The business is very specific not to use SP or temp table in the query for the report generation.
Why? What reason do they have for such a strange requirement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 9:02 am
I think it's a crazy requirement, but the only option you have is to rewrite the query without using the temp table. You could try using whatever query loaded the temp table as either a derived table or a common table expression. Since these are just queries, it can work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2014 at 4:18 am
Also, have a sql code like a temp table creation and insertion inside the Stored proc, please guide me how this can be achieved using CTE or by any other way.
CREATE TABLE #Local_Import (
[Testcode] [nvarchar](255) NULL,
[Parametername] [nvarchar](255) NULL,
Parametercode [nvarchar](255) NULL
) ON [PRIMARY]
INSERT INTO #Local_Import
VALUES ('A7158', '2,3,7,8-TetraCDD', '7300A101')
INSERT INTO #Local_Import
VALUES ('A7158', '1,2,3,7,8-PentaCDD', '7300A102')
INSERT INTO #Local_Import
VALUES ('A7158', '1,2,3,4,7,8-HexaCDD', '7300A103')
August 6, 2014 at 4:27 am
Junglee_George (8/6/2014)
Also, have a sql code like a temp table creation and insertion inside the Stored proc, please guide me how this can be achieved using CTE or by any other way.
CREATE TABLE #Local_Import (
[Testcode] [nvarchar](255) NULL,
[Parametername] [nvarchar](255) NULL,
Parametercode [nvarchar](255) NULL
) ON [PRIMARY]
INSERT INTO #Local_Import
VALUES ('A7158', '2,3,7,8-TetraCDD', '7300A101')
INSERT INTO #Local_Import
VALUES ('A7158', '1,2,3,7,8-PentaCDD', '7300A102')
INSERT INTO #Local_Import
VALUES ('A7158', '1,2,3,4,7,8-HexaCDD', '7300A103')
WITH CTE_Values AS
(
SELECT Testcode = 'A7158', Parametername= '2,3,7,8-TetraCDD', Parametercode = '7300A101'
UNION ALL
SELECT 'A7158', '1,2,3,7,8-PentaCDD', '7300A102'
UNION ALL
SELECT 'A7158', '1,2,3,4,7,8-HexaCDD', '7300A103'
)
--insert/update/delete statement that uses the CTE
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 6, 2014 at 7:20 am
Thank You on that. Also how I can modify the part using a CTE where the values from a select statemnt are inserted into the temp table like the one below-
Select t1.Col1, t2.col2, t3.col3 INTO #Local_Import
From table1 t1
Join table2 t2 on t1.id=t2.id
Join table t3 on t2.id=t3.id
August 7, 2014 at 12:21 am
You really should do some research on how to work with CTEs.
Into what object do you want to insert the rows?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply