UNION the results of multiple stored procedures with ASP.NET

UNION is used to combine the result from multiple SELECT statements into a single result set.

If we have a stored procedure and need to call several different times passing in different parameters each time. We would like to collect the results as a single dataset.

exec MyStoredProcedure 1
UNION
exec MyStoredProcedure 3
UNION
exec MyStoredProcedure 5    

this shows an syntax above but got the error ...

Incorrect syntax near the keyword 'Union'.

You'd have to use a temp table like this.

CREATE TABLE #TempTABLE

{

COLUMN1 datatype

COLUMN2 datatype

}

INSERT #TempTABLE

exec MyStoredProcedure 1

INSERT #TempTABLE

exec MyStoredProcedure 2

INSERT #TempTABLE

exec MyStoredProcedure 3

EXAMPLE

create table users

(

userId int identity(1,1) not null,

userName varchar(50) not null,

userPass varchar(20) not null

)

insert into users(userName, userPass) values('UserName1', 'Password')

insert into users(userName, userPass) values('UserName2', 'Password')

insert into users(userName, userPass) values('UserName3', 'Password')

insert into users(userName, userPass) values('UserName4', 'Password')

insert into users(userName, userPass) values('UserName5', 'Password')

insert into users(userName, userPass) values('UserName6', 'Password')

Create the Store procedure

CREATE PROCEDURE Sp_GetUserInformation

@userId INT

AS

BEGIN

SELECT * FROM users WHERE userid=@userId

END

GO

You want to fetch the record whose userid is 1 3 5 and store it into dataset.

CREATE PROCEDURE Sp_Collectalluser

AS

BEGIN

--Create temporary table

CREATE TABLE #TEMP

(

userId INT,

userName VARCHAR(255),

userPass VARCHAR(255)

)

INSERT INTO #TEMP

exec Sp_GetUserInformation 1

INSERT INTO #TEMP

exec Sp_GetUserInformation 3

INSERT INTO #TEMP

exec Sp_GetUserInformation 5

SELECT * FROM #TEMP

END

OUTPUT

After executing the store procedure

UserId	UserName	Userpass
1	UserName1	Password
3	UserName3	Password
5	UserName5	Password