How to pass multiple values one by one for a parameter in an SQL query and union the results?

Question

0.00/5 (No votes)

See more:

I have an SQL Statement which accepts a parameter @EndDate as DateTime. I want to be able to pass several values for @EndDate one by one and then Union the results of all the Queries. I have tried using CTE for this but it is of no use. I want to pass several Dates for @EndDate. Any help would be greatly appreciated.

My Query is:

DECLARE	@EndDate as DateTime
SET @EndDate = '2018/02/25'

SELECT  
CONVERT(VARCHAR, @EndDate, 101)  [R_Date]
,[Name] 	
[Type] 
FROM [dbo].[S_Table]

when @EndDate = '2018/02/24'

R_Date	Name	Type
2/24/2018	A	Monthly
2/24/2018	A	Daily

<pre>when @EndDate = '2018/02/24'

R_Date	Name	Type
2/25/2018	TEST_1	Monthly
2/25/2018	TEST_2	Monthly
2/25/2018	TEST_3	Daily
2/25/2018	TEST_4	Weekly
2/25/2018	TEST_5	Monthly
2/25/2018	TEST_6	Monthly
2/25/2018	TEST_7	Monthly
2/25/2018	TEST_8	Monthly
2/25/2018	TEST_9	Monthly
2/25/2018	TEST_10	Monthly

What I have tried:

I have tried a WHILE Loop but it will generate two or more results depending on the repetition. I want the result as a single query result may be a UNION ALL.

Posted 27-Feb-18 1:08am

ZohaibRazaTheDProgrammer

Updated 27-Feb-18 2:03am

Add a Solution

Comments

Santosh kumar Pithani 27-Feb-18 7:35am

create one #temp table and use while loop for insert records in #temp

ZohaibRazaTheDProgrammer 27-Feb-18 7:39am

give an example please

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Accepted Answer · 2018-02-27T02:04:00

DROP TABLE IF EXISTS #TEMP;
CREATE #temp table(COLS..)
Declare  @filters table(id int identity(1,1),dt datetime);
Declare @cnt int=1;
WHILE(@cnt<=(select Count(*) from RealTable))
BEGIN
INSERT INTO #TEMP SELECT * FROM RealTable WHERE  DateCOL IN(SELECT DT from @filters WHERE ID=@cnt);
SET @CNT=@CNT+1;
END;
SELECT * FROM #Temp;