March 7, 2013 at 4:03 pm
We already have a SP which selects data from db.
I need to modify the SP so that it selects one more column called supervisor.
The code to get the supervisor data is
SELECT * INTo #sStaffNames
FROM (select p.personid,p.LastName + N', ' + p.FirstName AS Supervisor, S.StaffID---
from Staff as s inner join people as p
on s.PersonID = p.personid)A
select peoplelinkid ,staffassigned,Supervisor
from clientinfo as ci LEFT JOIN #StaffNames as s ON Ci.StaffAssigned=S.StaffID
where ci.staffassigned<>0
drop table #sstaffnames
This code works perfectly and I am able to get the desired result.
Now the problem is I am not able to figure out how to put the above code in a SP which selects data.
Thanks in advance
March 7, 2013 at 4:31 pm
Does this work for you?
create procedure dbo.yourprocedurename
as
begin
SELECT * INTo #sStaffNames
FROM (select p.personid,p.LastName + N', ' + p.FirstName AS Supervisor, S.StaffID---
from Staff as s inner join people as p
on s.PersonID = p.personid)A
select peoplelinkid ,staffassigned,Supervisor
from clientinfo as ci LEFT JOIN #StaffNames as s ON Ci.StaffAssigned=S.StaffID
where ci.staffassigned<>0
drop table #sstaffnames
end
March 7, 2013 at 4:38 pm
Hi my condition is that I already have a sp and I have to add this code and modify the sp so that it returns a column supervisor which comes from the code above
March 7, 2013 at 5:56 pm
You either use modify procedure instead of create,
or you do drop procedure and then create procedure with the new code.
If this is unclear look up modify procedure in BOL.
March 7, 2013 at 9:30 pm
ntreelevel (3/7/2013)
Hi my condition is that I already have a sp and I have to add this code and modify the sp so that it returns a column supervisor which comes from the code above
In that case you would just change CREATE PROCEDURE to ALTER PROCEDURE in the code sample Erin showed.
http://msdn.microsoft.com/en-us/library/ms189762(v=sql.105).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 7, 2013 at 11:41 pm
ntreelevel (3/7/2013)
We already have a SP which selects data from db.I need to modify the SP so that it selects one more column called supervisor.
The code to get the supervisor data is
SELECT * INTo #sStaffNames
FROM (select p.personid,p.LastName + N', ' + p.FirstName AS Supervisor, S.StaffID---
from Staff as s inner join people as p
on s.PersonID = p.personid)A
select peoplelinkid ,staffassigned,Supervisor
from clientinfo as ci LEFT JOIN #StaffNames as s ON Ci.StaffAssigned=S.StaffID
where ci.staffassigned<>0
drop table #sstaffnames
This code works perfectly and I am able to get the desired result.
Now the problem is I am not able to figure out how to put the above code in a SP which selects data.
Thanks in advance
can you post the code that selects the data ... ??
or explain why are you not able to put the query in the SP ???
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 8, 2013 at 6:01 am
Below is the sample code
now my question is where do I add the above code so that this sp will return the col supervisor
ALTER PROCEDURE [dbo].[mc_selClientsActiveNoService]
-- Add the parameters for the stored procedure here
@StartDatedate,
@EndDatedate,
@Branchsmallint,
@EligDescriptionVarchar(Max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Term Client, active authorization
Select p.LastName,
p.FirstName,
ci.CaseNumber,
max(ca.EffectiveDate) As EffectiveDate,
ci.ReferralDate,
hd.HistoryDate,
'' As HistoryText,
Max(cp.PlanStartDate)as "Service Start",
Max(cp.PlanEndDate)as "Service End",
Sum(ca.AuthUnits / uf.UnitFactorValue) as AuthHours,
'Term Client, active authorization' As StatusText,
fc.EligibilityCode + '-' + fc.EligDescription As Funder,
isnull(p1.LastName + ', ' + p1.FirstName, '') as Caseworker
fromPeopleLink pl
Inner JoinClientInfo ci
On(pl.PeopleLinkID = ci.PeopleLinkID)
And(ci.FundingID in (@EligDescription))
March 8, 2013 at 8:17 am
It looks like your code got truncated. It will not parse (missing END?) and also does not contain the original statement you were asking about beginning with SELECT * INTo #sStaffNames. Try again and post the entire proc definition?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 11, 2013 at 11:16 am
I got the solution.
It is just that I had to add more of inner join and left join and no need of any temp tables.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply