Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I want to make One Select Query With Union All
and that how many times i have to add union all is pass from form One No.
and that no of times Union all Block will generate and that is my final Query.

for more Clarification I put example like this

I have Query Like this,

Select StudentID, StudentName, Std, Percentage
From TBLStudent


now I pass One No From My HTML Page, for Example no is 4 then My Qyery is

Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent


and if No is 2 then

Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent


What I have tried:

For Example, I have Pass No From Form is 3 then My Final Query will be

Select '' as A
Union All
Select '' as A
Union All
Select '' as A


and if I Pass No : 2 then my Query will be

Select '' as A
Union All
Select '' as A
Posted
Updated 26-Jan-23 5:38am
v3

It's not clear precisely what you're trying to do, but based on your example query, you don't need a UNION at all.
SQL
DECLARE @No int = 3;
SELECT TOP (@No) '' As A FROM sys.all_columns;
This uses the sys.all_columns catalog view[^] since it's likely to have enough rows to satisfy your query. You could use your own tally table[^] if required.
 
Share this answer
 
Well, we don't understand the business requirement that needs such kind of Query Generation but anyway you can use below approach to get what you want.

SQL
DECLARE @SQL VARCHAR(8000) ;
DECLARE @Rept INT = 4;

SELECT @SQL = COALESCE(@SQL + ' UNION ALL ', '') + A 
FROM 
(
  SELECT A
  FROM
  (
     SELECT 'Select StudentID, StudentName, Std, Percentage From TBLStudent' AS A
  ) T
  CROSS JOIN
  (
      SELECT TOP (@Rept) *
      FROM sysobjects
  ) BT
) P
;

SELECT @SQL
--EXEC (@SQL)
 
Share this answer
 
As the others have said, it really isn't clear what you are trying to do or why, however, based on your statement
Quote:
and if No is 2 then

Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent
then you are expecting to get the entire contents of TBLStudent repeated No number of times. To achieve that you can use the query from Solution 1 in a cross-join e.g.
SQL
Select StudentID, StudentName, Std, [Percentage]
From TBLStudent
cross join
(SELECT TOP (@No) '' As A FROM sys.all_columns) as A;
BUT I suspect what you really want is to get the first No of records from that table in which case your query should simply be
SQL
select top (@No) StudentID, StudentName, Std, [Percentage]
from TBLStudent;
Instead of showing us what the query would look like, give some sample data and your expected results - it is an incredibly powerful way of showing us the problem
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900