Select Stored Procedure

How about:

select candidate_name, mark_1, mark_2, mark_3, sum(mark_1, mark_2, mark_3) as total

yeah
but candidate_name, mark_1, mark_2, mark_3 is the returned value in my stored procedure. How can I do it
I means how to use my procedure to creat a new one that have new column, eg sum(mark_1, mark_2, mark_3)

here is my stored procedure (with sp_ags_crosstab is my extended stored procedure)

----------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Marks AS
exec sp_ags_crosstab 'SELECT     dbo.TblCandidates.*, dbo.TblSubjects.Subject_Name AS Expr1, dbo.TblMarks.Mark AS Expr2
FROM         dbo.TblCandidates INNER JOIN
                      dbo.TblMarks ON dbo.TblCandidates.Candidate_Code = dbo.TblMarks.Candidate_Code INNER JOIN
                      dbo.TblSubjects ON dbo.TblMarks.Subject_Code = dbo.TblSubjects.Subject_Code' , '[Candidate_Code], [Candidate_Name], [Date_Of_Birth], [Sex], [House_number], [Street_name], [Precinct], [District], [City], [City_code]' , '[Expr1]' , '[Expr2]' , '[5]' , 'MCSM'

GO
----------------------------------------------------------------------------------------------------------------------------------------

Use Dynamic SQL

You can get the results of an sp inside another sp, by using temporary tables,
create a temp table that can srore all the values returning from the procedure

insert into #temp
EXEC urSp


now the values will be in the #temp table . put the following code to create a view or procedure


EXEc ('CREATE VIEW ... ')

I don't understand, Can you help me write a code clearly. Bcoz I'm a newcomer of SQL
Assuming that. I have a stored procedure. Now I want to create another stored procedure that have all column which is in my stored procedure, and have new comlumn that is the sum of marks of each candidates
Thanks so much

Supporter,
> SELECT     dbo.TblCandidates.*, dbo.TblSubjects.Subject_Name AS Expr1,
> dbo.TblMarks.Mark AS Expr2

I want to know exactly  what recordset is returning from the sp, i dont want * , instead you specify the columns
Otherwise i'll give you an example
Assuming that your sp returns the following records studentID, StudentName, Expr1 , Expr2

Step1 .
create a temporary table to hold the results from the sp

create table #temp (StudentID int, StudentName varchar(32) , Expr1 int, expr1 int)


Step2.


insert into #temp
exec marks

The above table stores the Temp table.. Hope this is sufficient for you

OK,
I use PIVOT/Crosstab techniques to create my stored procedure, and sp_ags_crosstab is my extended stored procedure
So
my stored procedure will return the folowing column
-------------------------------------------------------------------------------------------------------------------------------------
Candidate_Code | Candidate_Name | Date_Of_Birth | Sex | House_Number | Street_name| Precinct |[District | City |
     value                      value                  value           value         value              value            value        value    value
City_code | Subject_Name_1 | Subject_Name_2 ....
value               mark                       mark

I have 3 table
------------------------                      ------------------------                     -------------------------
TblCandidate:                                        TblMarks                                    TblSubjects
------------------------                      -------------------------                    ---------------------------
+Candidate_Code (PK)                     +Exam_Paper_Code (PK)                 +Subject Code (PK)
+Candidate_Name                           +Subject Code                                +Subject Name
+DOB                                             +Candidate_Code
+Sex                                              +Mark
+House Number
+Street Name
+Precinct
+District
+City
+City Code

------------------------------------------------------------------------------------------------------------------------
If you have question for me. I'm online. I can meet up with your question immediately.

Which version of sql server you are using

I'm using sql server 2000

Supporter,
> how can I use my stored procedure and my view to creat a new view, or
> stored procedure, or table that have something like : candidate_name,
> mark 1, mark 2, ..., sum_of_marks

Now can you tell me exactly what you need.

If you need the results of the above sp in a table , then you can make use of my previous post...using temp table

ok
what if I have 2 stored procedure
One is which I showed you
-------------------------------------------------------------------------------------------------------------------------------------
Candidate_Code | Candidate_Name | Date_Of_Birth | Sex | House_Number | Street_name| Precinct |[District | City |
     value                      value                  value           value         value              value            value        value    value
City_code | Subject_Name_1 | Subject_Name_2 ....
value               mark                       mark
---------------------------------------------------------------------------------------------------------------------------

One is which return these columns:
---------------------------------------
Candidate_Code | Sum_Mark
------------------------------------------

How can I convert 2 stored procedure into one unique. That return these columns:
------------------------------------------------------------------------------------------------------------------------------------
Candidate_Code | Candidate_Name | Date_Of_Birth | Sex | House_Number | Street_name| Precinct |[District | City |
     value                      value                  value           value         value              value            value        value    value
City_code | Subject_Name_1 | Subject_Name_2 .... | Sum_Mark | <~~~~ this is the column of my second one
value               mark                       mark                       value
-----------------------------------------------------------------------------------------------------------------------------------------

Thanks so much, I will try it now