Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how do I create StoredProc with multiple queries and return custom results

Posted on 2010-09-13
17
Medium Priority
?
415 Views
Last Modified: 2012-05-10
how would I create a stored-proc that has multiple select statements and returns them to look like one statement? I need to pass 2 parameters to a stored proc from my asp.net page and have the sp make 4 diff queries (within the sp) and return parts of each query to look like as if I did one select query. (I'm using MS SQL 2008 and ASP.Net 4.0)

select a,b,c from table1 where name=@parm1
select d,e,f from table2 where id=@parm2
select g,h,i from table3 where id=5

return a,c,e,h
0
Comment
Question by:TARJr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33664733
I don't quite understand what you mean by "and return parts of each query to look like as if I did one select query."  Are you looking to execute those Select statements, and then do one single Select statement for the return from the stored procedure?
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33664746
Dear TARJr

you have different ways to do that

1. Store the results in variables and concatenate them and return them as one variable

2. Store all results in a temporary table and return this temp table results only (see code below)


select a,b,c into #tmp1 from table1 where name=@parm1
insert into #tmp1 select d,e,f from table2 where id=@parm2
insert into #tmp1 select g,h,i from table3 where id=5

select * from #tmp1 

Open in new window

0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 33664761
Save each value to vars, and tehn select the group at the end. The stored proc will return the last select statement:
CREATE PROCEDURE GetRecords(
	@parm1 varchar(50),
	@parm2 int
)
AS

BEGIN
	DECLARE @a varchar(50),
		@b varchar(50),
		@c varchar(50),
		@d varchar(50),
		@e varchar(50),
		@f varchar(50),
		@g varchar(50),
		@h varchar(50),
		@i varchar(50)
		
	SELECT @a = a, @b = b, @c = c
	FROM table1
	WHERE name = @parm1

	SELECT @d = d, @e = e, @f = f
	FROM table2
	WHERE id = @parm2

	SELECT @g = g, @h = h, @i = i
	FROM table3
	WHERE id = 5

	SELECT a = @a, b = @b, c = @c, d = @d, e = @e, f = @f, g = @g, h = @h, i = @i 
END

Open in new window

0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 1

Author Comment

by:TARJr
ID: 33665206
yesthatbob, AmmarR this looks good, but I forgot that I need to return TOP 20 rows, like

select TOP 20 a,b,c from table1 where name=@parm1
select TOP 20 d,e,f from table2 where id=@parm2
etc

what's the best way to return this?
0
 
LVL 8

Expert Comment

by:Som Tripathi
ID: 33665239
Not sure about this question - one end, you say like - you want to get a single result set.
On the other end you say return value as a,e...

If you have same data-type and number of columns- you can use a union all between statements to get single result set. Such as -

select a,b,c from table1 union all
select d,e,f from table2 where a > b union all
select g,h,i from table3

- But, I think you requirement is something else... :)
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33665252
use the same script but just add the top 20 in the statement

select top 20 a,b,c into #tmp1 from table1 where name=@parm1
insert into #tmp1 select top 20  d,e,f from table2 where id=@parm2
insert into #tmp1 select top 20  g,h,i from table3 where id=5

select * from #tmp1


or if you want to combine them all first and then get the top 20 use the other script

select a,b,c into #tmp1 from table1 where name=@parm1
insert into #tmp1 select d,e,f from table2 where id=@parm2
insert into #tmp1 select g,h,i from table3 where id=5

select top 20 * from #tmp1

but dont forget to add the order by, it effects which top 20 you get

Regards
select top 20 a,b,c into #tmp1 from table1 where name=@parm1
insert into #tmp1 select top 20  d,e,f from table2 where id=@parm2
insert into #tmp1 select top 20  g,h,i from table3 where id=5

select * from #tmp1

Open in new window

0
 
LVL 1

Author Comment

by:TARJr
ID: 33665271
I can't use a Union because each select is not the same # of parms...each of my queries returns the most recent (TOP 20) rows, based on date (order by date), so I need to return those rows merged as one select query.
0
 
LVL 1

Author Comment

by:TARJr
ID: 33665379
AmmarR, I am getting the following erros when creating the SP:


Msg 194, Level 15, State 1, Procedure GetCompanyData2, Line 18
A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.
Msg 199, Level 15, State 1, Procedure GetCompanyData2, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33665578
TARJr: can you send me the script you are using

because i tried the script i gace you and its not showing any error in my pc

or ehck the way you are declaring the parameters
0
 
LVL 1

Author Comment

by:TARJr
ID: 33665758
CREATE PROCEDURE GetCompanyData2(
      @nid int,
      @ind int
)
AS

BEGIN
      DECLARE @a smalldatetime,
            @b real,
            @c real,
            @d varchar(50),
            @e smalldatetime,
            @f real,
            @g real,
            @h smallint


SELECT TOP 20
      @a=[PERIOD_END_DT],
      @b=[SL1],
      @c=[SL2]
      into #tmp1
  FROM [myDB].[dbo].[table1]
  where NID_S=@nid
  order by PERIOD_END_DT desc


      SELECT * from #tmp1
      
END

0
 
LVL 1

Author Comment

by:TARJr
ID: 33665929
got the first error fixed, but now I get:

"Column name or number of supplied values does not match table definition."

when trying to insert data from the second query:

select TOP 20 d,e,f,g from table2 where nid=@ind
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33665970
Dear


why are you assiging the value to these variables

  @a=[PERIOD_END_DT],
      @b=[SL1],
      @c=[SL2]

whats the point of doing that

a variable can contain one value and the way you are using the variables its like you are trying to return all the rows of a column into it, and it wont work this way.

explain to me what you are trying to achieve with it so that i can help

or just use normal script


SELECT TOP 20
      [PERIOD_END_DT],
      [SL1],
      [SL2]
      into #tmp1
  FROM [TajdeedSite].[dbo].[Bwscms_Post]
  where NID_S=@nid
  order by PERIOD_END_DT desc
0
 
LVL 1

Author Comment

by:TARJr
ID: 33666094
AmmarR, this isn't working, each select statement has a different number of parameters and types, so when I execute the Sp it says:

Column name or number of supplied values does not match table definition

and if I define the table (create table...) in the SP, it says table already exists...
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33666183
let me tell you what i understood

ok you have 3 tables with different columns

thats why you cant use union and you cant use 1 temp table.

and you are trying to define columns on the fly,

is it so.

if so, send me the structure of these 3 tables and will see which is the best way to combine them and get all data.

Regards
0
 
LVL 1

Author Comment

by:TARJr
ID: 33666423
ok, here are the 3 queries, I just want to return the TOP 20 from each as if they were one table:


SELECT TOP 20
      [PERIOD_END_DT]
      [SL1]
      [SL2]
  FROM [table1]
  where NID_S=@nid
  order by PERIOD_END_DT desc

select TOP 20
      b.name, a.period_end_dt,
      c.SL1,
      a.SL2, r.SL3
      from table2 a left join
             table4 b
             on a.nid_s=b.nid_s left join
             table3 r
             on r.NID_S=a.NID_S and r.MNID_S=0 and a.period_end_dt=r.period_end_dt left join
             table5 c
             on a.nid_s=c.nid_s and a.period_end_dt=c.period_end_dt
      where a.NID_S = @ind
      order by a.PERIOD_END_DT desc  

SELECT TOP 20
s.PERIOD_END_DT
,s.SL8
,s.SL9
from [table12] s
where s.NID_S = @nid and s.MNID_S = @ind
order by s.PERIOD_END_DT desc

0
 
LVL 15

Accepted Solution

by:
AmmarR earned 1000 total points
ID: 33666955
ok

so you have these 3 columns in query 1
      [PERIOD_END_DT] ,      [SL1],      [SL2]
-----------------

these 5 columns in query 2
b.name, a.period_end_dt,      c.SL1,      a.SL2, r.SL3
--------------
and 3 columns in query 3
s.PERIOD_END_DT  ,s.SL8  ,s.SL9


so to fix this problem you need to get 5 columns in all the queries and with same name
to do so, use alias

check code below

i hope i clears things now

--Query 1

SELECT TOP 20 
      '' as 'Name',
      [PERIOD_END_DT] as 'PERIOD_END_DT',
      [SL1] as 'SL1',
      [SL2] as 'SL2',
	  '' as 'SL3'
  into #tmp1 FROM [table1]
  where NID_S=@nid
  order by PERIOD_END_DT desc

--Query 2
insert into #tmp1
select TOP 20
      b.name as 'Name', 
	  a.period_end_dt as 'PERIOD_END_DT',
      c.SL1 as 'SL1',
      a.SL2 as 'SL2', 
	  r.SL3 as 'SL3'
      from table2 a left join
             table4 b
             on a.nid_s=b.nid_s left join
             table3 r
             on r.NID_S=a.NID_S and r.MNID_S=0 and a.period_end_dt=r.period_end_dt left join
             table5 c
             on a.nid_s=c.nid_s and a.period_end_dt=c.period_end_dt
      where a.NID_S = @ind
      order by a.PERIOD_END_DT desc  

--Query 3
insert into #tmp1
SELECT TOP 20
      '' as 'Name',
      s.PERIOD_END_DT as 'PERIOD_END_DT',
      s.SL8 as 'SL1',
      s.SL9 as 'SL2',
	  '' as 'SL3'
from [table12] s 
where s.NID_S = @nid and s.MNID_S = @ind
order by s.PERIOD_END_DT desc

SELECT * from #tmp1

Open in new window

0
 
LVL 1

Author Comment

by:TARJr
ID: 33668378
Thanks AmmarR,
I actually added this:

SELECT TOP 20
IDENTITY(smallint, 1, 1) as id,

so I could do this to get what I want:

SELECT x.id, x.a, x.b, x.c, y.a, y.c, y.d, z.a, z.b
 from #t1 x inner join
 #t2 y
 on y.id = x.id inner join
 #t3 z
 on z.id = x.id

0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question