Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

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

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
TARJr
Asked:
TARJr
1 Solution
 
Bob LearnedCommented:
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
 
AmmarRCommented:
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
 
yesthatbobCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
TARJrAuthor Commented:
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
 
Som TripathiCommented:
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
 
AmmarRCommented:
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
 
TARJrAuthor Commented:
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
 
TARJrAuthor Commented:
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
 
AmmarRCommented:
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
 
TARJrAuthor Commented:
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
 
TARJrAuthor Commented:
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
 
AmmarRCommented:
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
 
TARJrAuthor Commented:
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
 
AmmarRCommented:
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
 
TARJrAuthor Commented:
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
 
AmmarRCommented:
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
 
TARJrAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now