Solved

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

Posted on 2010-09-13
17
369 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
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 250 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to generate total number of records in db 35 26
SQL Help - 12 36
Hidden Field Value 10 34
Creating Alerts in sql sever 2 13
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now