15,922,407 members
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

## Solution 3

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

## Solution 2

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)```

## Solution 1

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.