How i can retrieve data from stored procedure in table format like normal query

Question

I want to retrieve data from stored procedure in table format like normal sql query.

what i have to change?

my procedure is.....

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[agg] 

AS    
DECLARE @stud int,
@lec int,
@dept varchar(50),
@sem int

select @stud=s.sem_no_stud,
@lec=sum(convert (int,sub.sub_lec)),@dept=d.dept_name,@sem=count(s.sem_id)
from sem_info s,sub_info sub,dept_info d,course_info co 
where sub.sdept=d.dept_id and 
s.sem_caurse_id=co.co_id and 
sub.sub_sem_id=s.sem_id and co.co_id=149 
group by d.dept_name,s.sem_id,s.sem_no_stud

return (@stud)
return(@lec)

exec agg;

Answer 1

You just write a SELECT statement with the variables in like this:

SELECT @stud AS ColumnNameA, @lec AS ColumnNameB

However, something to point out is what if the query returns multiple rows? Assigning into variables like this will only return one of those records - the last records values to be assigned in to them will be returned.

Answer 2

The variables appear to serve no purpose. You can just SELECT directly. Additionally you need a go before your exec to avoid inadvertently creating a recursive procedure and you will be safer using explicit JOIN syntax as below to avoid inadvertent Cartesian joins.

ALTER PROCEDURE [dbo].[agg] 

AS    

 SELECT   s.sem_no_stud ,
         SUM(CONVERT (INT,sub.sub_lec)),
         d.dept_name ,
         COUNT(s.sem_id)
FROM     sem_info s
         JOIN sub_info sub
         ON       sub.sub_sem_id =s.sem_id
         JOIN dept_info d
         ON       sub.sdept =d.dept_id
         JOIN course_info co
         ON       s.sem_caurse_id=co.co_id
WHERE    co.co_id =149
GROUP BY d.dept_name,
         s.sem_id ,
         s.sem_no_stud

go

exec [dbo].[agg] ;