Tuesday, August 30, 2011

UNION the results of multiple stored procedures with ASP.NET

Posted by Rahul Kharde at 1:40 AM

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




If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top