Solved

running default value for a stored procedure in sql server 2008

Posted on 2011-02-10
2
392 Views
Last Modified: 2012-05-11
I want to run all for the third parameter ,@divisionID but it is erroring out:
USE [Database]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[usp_Program_Costing]
            @Staff_ID = N'ALL',
            @Terms = '2011F',
            @divisionID ='  '

What do I need to use?

Thanks,
Nigluc

USE [database]
GO
/****** Object:  StoredProcedure [dbo].[usp_Program_Costing]    Script Date: 02/10/2011 21:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


ALTER                     PROCEDURE [dbo].[usp_Program_Costing]

(@Staff_ID nvarchar(4000),
@Terms varchar(500),
@divisionID nvarchar(100))

 
AS


create table #SWF

(      Staff_ID nvarchar (7) NOT NULL ,
        divisionID nvarchar (2)  NOT NULL ,
      Term nvarchar (5) NOT NULL ,
      Course nvarchar (10)  NOT NULL ,
      SWF_Section_Size int NULL  ,
      Start_Date datetime NOT NULL ,
      End_Date datetime NOT NULL ,
        Sect nvarchar (2) NULL ,
        Ckey int NOT NULL,
        Assigned_Teaching_Hours int NULL,
        Preparation_Attributed_Hours money Null,
        Evaluation nvarchar (2) NULL,
        Evaluation_Factor money NULL,
        Evaluation_Attributed_Hours money NULL,
        Section_Total_Hours money NULL,
        WeeklyTCH  Float Null,
        Enrol_Tot int NULL,
        MyCount int NULL,
        Program nvarchar (4) NULL,
        ProgramACAD nvarchar (4) NULL,
        TCW int NULL,
        Revision int Null,
        Complementary_Hours_Assigned money Null,
        EmpName nvarchar (30) NULL,

       
       
)

create table #SWF_Header_HKey2
( Term2 nvarchar(5)Not Null,
  divisionID2 nvarchar (5) Not Null,
  Staff_Id2 nvarchar (7) Not Null,
  Seq2 int Not Null,
  HKey2 int Not Null)

create table #SWF_Header_Course2
(divisionID3 varchar(2) Not Null,
 HKey3 Int Not Null,
 Ckey3 Int Not Null)
     
IF @Staff_ID='ALL'
BEGIN


INSERT #SWF_HEADER_HKey2(Term2, divisionID2, Staff_Id2, Seq2,HKey2)
Select Term,
       divisionID,
       Staff_Id,
       Seq,
       HKey
from SWF_Header SWFH
INNER JOIN iter_charlist_to_table(@Terms, DEFAULT) s ON  SWFH.TERM = s.nstr
INNER JOIN iter_charlist_to_table(@divisionID, DEFAULT)s3 ON  SWFH.divisionID = s3.nstr  

Insert #SWF_Header_Course2(divisionID3,HKey3, Ckey3)
Select divisionID
       ,Hkey
       ,Ckey
from SWF_Header_Course SWF_HC
LEFT OUTER JOIN  #SWF_Header_HKey2 SWF_HH on SWF_HC.HKey = SWF_HH.HKey2

INSERT #SWF (Staff_ID,divisionID,Term,Course,Start_Date,End_Date/*,SWF_Section_Size*/,Ckey,Assigned_Teaching_Hours,Preparation_Attributed_Hours,Evaluation,Evaluation_Factor,Evaluation_Attributed_Hours,Section_Total_Hours,Revision,Complementary_Hours_Assigned )
Select DISTINCT
Staff_ID
,divisionID
,Term      
,Course
,Start_Date
,End_Date
--,SWF_Section_Size
,Ckey
,Assigned_Teaching_Hours
,Preparation_Attributed_Hours
,Evaluation
,Evaluation_Factor
,Evaluation_Attributed_Hours
,Section_Total_Hours
,Revision
,Complementary_Hours_Assigned

FROM   dbo.SWF_Section SWF(NOLOCK)
LEFT OUTER JOIN #SWF_Header_Course2 SWF_HC (NOLOCK)ON SWF_HC.CKey3 =SWF.cKey
INNER  JOIN iter_charlist_to_table(@Terms, DEFAULT) d ON  SWF.TERM = d.nstr
INNER JOIN iter_charlist_to_table(@divisionID, DEFAULT)s3 ON  SWF.divisionID = s3.nstr  
WHERE  (Preparation IS NOT NULL
AND Preparation_Attributed_Hours IS NOT NULL
AND Evaluation IS NOT NULL
AND Evaluation_Factor IS NOT NULL
AND Evaluation_Attributed_Hours IS NOT NULL)
ORDER BY Course


UPDATE #SWF
Set Sect = Sect.Sect
FROM #SWF,
SWF_Section AS Sect (NOLOCK)
WHERE #SWF.Ckey=Sect.cKey
--#SWF.divisionID =Sect.divisionID
--and   #SWF.Term = Sect.Term
--and   #SWF.Course = Sect.Course

UPDATE #SWF
Set SWF_Section_Size = Sect.SWF_Section_Size
FROM #SWF,
SWF_Section AS Sect (NOLOCK)
WHERE #SWF.Ckey=Sect.cKey
--#SWF.divisionID =Sect.divisionID
--and   #SWF.Term = Sect.Term
--and   #SWF.Course = Sect.Course
--AND   #SWF.Start_Date=Sect.Start_Date
--AND   #SWF.End_Date=Sect.End_Date
--AND   #SWF.Staff_ID = Sect.Staff_ID

/* weekly tch */
UPDATE #SWF
Set WeeklyTCH = Course.Credit_Value
FROM #SWF
LEFT OUTER JOIN Course AS  Course (NOLOCK)on #SWF.divisionID =Course.divisionID
and   #SWF.Term = Course.Term
and   #SWF.Course = Course.Course

UPDATE #SWF
Set Enrol_Tot = Enrol.ENRL_TOT
FROM #SWF
LEFT OUTER JOIN tblActualEnrolment AS Enrol (NOLOCK) ON #SWF.Term=Enrol.Term
where #SWF.Course=(Enrol.SUBJECT+Enrol.CATALOG_NBR)
and #SWF.Sect=Enrol.CLASS_SECTION



/* will be changed to reflect the dbo.PS_CLASS_TBL */
UPDATE #SWF
Set MyCount= Enrol.MyCount
FROM #SWF
LEFT OUTER JOIN tblActualEnrolment AS Enrol (NOLOCK) ON #SWF.Term=Enrol.Term
where #SWF.Course=(Enrol.SUBJECT+Enrol.CATALOG_NBR)
and #SWF.Sect=Enrol.CLASS_SECTION


/*Program*/
UPDATE #SWF
Set Program=LoadDet.Program
FROM #SWF
LEFT OUTER JOIN  dbo.Loadings_Detail AS LoadDet (NOLOCK) ON #SWF.DivisionID=LoadDet.DivisionID
where #SWF.Term = LoadDet.Term
and   #SWF.Course = LoadDet.Course
and   #SWF.Sect = LoadDet.Sect

UPDATE #SWF
Set ProgramACAD = PAO.ACAD_Org
FROM #SWF
LEFT OUTER JOIN dbo.ProgramACAD_Org AS PAO (NOLOCK) ON PAO.Program = #SWF.Program


UPDATE #SWF
Set TCW = swfh.Prev_TCW
FROM #SWF
LEFT OUTER JOIN dbo.SWF_Totals AS swfh (NOLOCK) ON #SWF.DivisionID=swfh.DivisionID
where #SWF.Term = swfh.Term
and   #SWF.Staff_ID = swfh.Staff_ID

UPDATE #SWF
Set #SWF.EmpName= Staff.Surname + ',' + Staff.First_Name
from #SWF
LEFT OUTER JOIN dbo.Staff AS Staff ON Staff.[ID] =  #SWF.Staff_ID

Select * from #SWF
END




0
Comment
Question by:Lucia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34869051
Can you explain little more? What error are you getting now?
0
 

Author Closing Comment

by:Lucia
ID: 34875932
It has been awhile since I have run stored procedures.  I wanted to run for all in the division category.  

I managed to do it.

Thank you for responding to my post.

Niglluc
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question