Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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 TripathiDatabase AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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