• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Select Stored Procedure

Now, I have a stored procedure that return value of candidate_name, mark 1, mark 2.....
and a view that return the sum of marks of each candidate
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
0
Supporter
Asked:
Supporter
  • 7
  • 5
1 Solution
 
ProactivationCommented:
How about:

select candidate_name, mark_1, mark_2, mark_3, sum(mark_1, mark_2, mark_3) as total
0
 
SupporterAuthor Commented:
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
----------------------------------------------------------------------------------------------------------------------------------------
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 ... ')
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SupporterAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
SupporterAuthor Commented:
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
0
 
SupporterAuthor Commented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Which version of sql server you are using
0
 
SupporterAuthor Commented:
I'm using sql server 2000
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
SupporterAuthor Commented:
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
-----------------------------------------------------------------------------------------------------------------------------------------


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Now  i got it,
You need two temp tables for this


CREATE TABLE #temp1 (Candidate_Code int , Candidate_Name  varchar(111), Date_Of_Birth  datetiem, Sex char(1), ... )

-- The above table should contain all the fields retuirned by the firat sp

Similarly create another table to store the results of the second SP

create table #temp2 (Candidate_Code int, sum_mark int)

Now you can use the join to get the results

SELECT t1.*, t2.Sum_Mark
from #temp1 t1
INNER JOIN #temp2 t2
ON t1.candidate_code = t2.Candidate_code
0
 
SupporterAuthor Commented:
Thanks so much, I will try it now
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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