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

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?

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 

Select allOpen in new window

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

Select allOpen in new window

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?

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... :)

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

Select allOpen in new window

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.

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.

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

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

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

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

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

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

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

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