stored procedure variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure variables

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-08-20 : 06:56:46

i ahve the following procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create PROCEDURE testflu @p_acad_period nvarchar (5)
AS

SET NOCOUNT ON

/* Base Table fields */
DECLARE @t_student_id nvarchar (11)
DECLARE @full_time_student bit
DECLARE @student_id nvarchar (11)
DECLARE @acad_period nvarchar (5)
DECLARE @aos_code nvarchar (11)
DECLARE @aos_period nvarchar (5)
DECLARE @full_desc_static nvarchar (200)
DECLARE @full_desc_session nvarchar (200)
DECLARE @dept_code nvarchar (10)
DECLARE @aos_start_dt datetime
DECLARE @exp_length smallint
DECLARE @unit_length nvarchar (10)
DECLARE @student_year tinyint
DECLARE @hrs_per_week int
DECLARE @aos_end_dt datetime
DECLARE @no_of_weeks numeric
DECLARE @hrs_total decimal (18,2)
DECLARE @main_course_study_is_C2K tinyint
DECLARE @hrs_actual decimal (18,2)
DECLARE @hrs_actual_student decimal (18,2)
DECLARE @hrs_notional_course decimal (18,2)
DECLARE @hrs_notional_student decimal (18,2)
DECLARE @student_type nvarchar (10)
DECLARE @moa_code nvarchar (10)
DECLARE @qual_aim nvarchar (3)
DECLARE @numeric_qual_aim int
DECLARE @subject_code nvarchar (4)
DECLARE @subject_area_main nvarchar (10)
DECLARE @subject_area_group nvarchar (10)
DECLARE @course_group char (1)
DECLARE @FE_HE_type char (2)
DECLARE @nvq_lvl_ind nvarchar (1)
DECLARE @geolocn_code nvarchar (10)
DECLARE @surname nvarchar (40)
DECLARE @forename nvarchar (40)
DECLARE @birth_dt datetime
DECLARE @post_code nvarchar (8)
DECLARE @student_attend_mode nvarchar (10)
DECLARE @funding decimal (18,2)
DECLARE @key_skill_count smallint
DECLARE @additionality_yn bit
DECLARE @fee_waiver nvarchar (10)
DECLARE @start_date datetime
DECLARE @student_status nvarchar (10)
DECLARE @end_date datetime
DECLARE @spec_learn_disab nvarchar (2)
DECLARE @sldd_ind tinyint
DECLARE @age smallint
DECLARE @full_time_ind tinyint
DECLARE @age_1960_ind tinyint
DECLARE @tsn_ind tinyint
DECLARE @link_ind tinyint
DECLARE @ESOL nvarchar (3)
DECLARE @esol_ind tinyint
DECLARE @infill_ind tinyint
DECLARE @RP_entitlement tinyint
DECLARE @threshold1 datetime
DECLARE @threshold2 datetime
DECLARE @threshold3 datetime
DECLARE @student_full_cost_ind tinyint
DECLARE @fundable_ind tinyint
DECLARE @LP_entitlement tinyint
DECLARE @LP2_entitlement tinyint
DECLARE @LP3_entitlement tinyint
DECLARE @outcome nchar (10)
DECLARE @OP_entitlement tinyint
DECLARE @tenhour_ind tinyint
DECLARE @basicIT nchar (3)
DECLARE @basic_it_ind tinyint
DECLARE @special_needs_ind tinyint
DECLARE @Evening_course tinyint
DECLARE @RP_basic decimal (18,2)
DECLARE @RP_NTETS_weight decimal (18,2)
DECLARE @RP_enhance_NTETS decimal (18,2)
DECLARE @RP_enhance_Age decimal (18,2)
DECLARE @RP_enhance_TSN decimal (18,2)
DECLARE @RP_enhance_IT decimal (18,2)
DECLARE @RP_enhance_SLDD decimal (18,2)
DECLARE @RP_enhance_ESOL decimal (18,2)
DECLARE @RP_total decimal (18,2)
DECLARE @LP_basic decimal (18,2)
DECLARE @subj_area_weight decimal (18,2)
DECLARE @LP_weighted decimal (18,2)
DECLARE @LP_enhance_SLDD decimal (18,2)
DECLARE @LP_enhance_VOC decimal (18,2)
DECLARE @LP_enhance_AGE decimal (18,2)
DECLARE @LP_enhance_TSN decimal (18,2)
DECLARE @LP_enhance_HE decimal (18,2)
DECLARE @LP_enhance_FEE decimal (18,2)
DECLARE @LP_total decimal (18,2)
DECLARE @OP decimal (18,2)
DECLARE @spurs_total decimal (18,2)
DECLARE @student_SPURS_total decimal(18,2)
DECLARE @out_weight decimal (18,2)
DECLARE @main_course_study tinyint
DECLARE @exp_days int
DECLARE @act_days int
DECLARE @total_students bigint
DECLARE @total_enrolments bigint
DECLARE @acad_year_spurs decimal (18,2)
DECLARE @RP_1960 tinyint
DECLARE @RP_null_DOB tinyint
DECLARE @RP_TSN tinyint
DECLARE @RP_disab2 tinyint
DECLARE @RP_disab3 tinyint
DECLARE @RP_disab4 tinyint
DECLARE @RP_ESOL tinyint
DECLARE @LP_1960 tinyint
DECLARE @LP_null_DOB tinyint
DECLARE @LP_TSN tinyint
DECLARE @LP_disab2 tinyint
DECLARE @LP_disab3 tinyint
DECLARE @LP_disab4 tinyint
DECLARE @LP_infill tinyint
DECLARE @LP_infill_1960 tinyint
DECLARE @LP_infill_null_DOB tinyint
DECLARE @LP_infill_TSN tinyint
DECLARE @LP_infill_disab2 tinyint
DECLARE @LP_infill_disab3 tinyint
DECLARE @LP_infill_disab4 tinyint
DECLARE @LP_term2 tinyint
DECLARE @LP_term2_1960 tinyint
DECLARE @LP_term2_TSN tinyint
DECLARE @LP_term2_disab2 tinyint
DECLARE @LP_term2_disab3 tinyint
DECLARE @LP_term2_disab4 tinyint
DECLARE @LP_term3 tinyint
DECLARE @LP_term3_1960 tinyint
DECLARE @LP_term3_TSN tinyint
DECLARE @LP_term3_disab2 tinyint
DECLARE @LP_term3_disab3 tinyint
DECLARE @ndaq_qual nvarchar (12)

DECLARE @base CURSOR

UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period

DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)

SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00

SET @base = CURSOR LOCAL FAST_FORWARD
FOR

SELECT stmaos.student_id, stmaos.acad_period, stmaos.aos_code, stmaos.aos_period, stcstatd.full_desc, stcsessd.full_desc AS SessionDescription,
stcsessd.dept_code, stcsessd.aos_start_dt, stcsessd.exp_length, stcsessd.unit_length, ISNULL(stmaos.student_year, 0) AS student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, stcsessd.aos_end_dt, stcsessd.no_of_weeks, stcsessd.hrs_total / 100 AS hrs_total,
stmfesqa.student_type, stcsessd.moa_code, stcfesdt.qual_aim, stcfesdt.subject_code, delsubj.subj_area, ISNULL(delsubj.subj_course_group, 0)
AS subj_course_group, stcfesdt.nvq_lvl_ind, stcsessd.geolocn_code, stmbiogr.surname, stmbiogr.forename, stmbiogr.birth_dt, stmadres.post_code,
stmaos.attend_mode, stmfesqa.fee_override, stmaos.start_date, RTRIM(stmfesqa.course_status) AS course_status, stmfesqa.course_status_dt as end_date,
stmfesqa.spec_learn_disab, FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, delESOL.subj_code AS ESOL, delspurs.threshold1, /* Log No.132694 - SQL changed*/
delspurs.threshold2, delspurs.threshold3, stmfesqa.outcome, delbasicIT.qual_aim AS basicIT, ISNULL(delsubjarea.subj_area_weight, 0.00)
AS subj_area_weight, ISNULL(deloutwgt.weighting, 0.10) AS weighting, ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref
FROM stsacper INNER JOIN
stmaos INNER JOIN
stcsessd ON stmaos.aos_code = stcsessd.aos_code AND stmaos.acad_period = stcsessd.acad_period AND
stmaos.aos_period = stcsessd.aos_period INNER JOIN
stcfesdt ON stmaos.acad_period = stcfesdt.acad_period AND stmaos.aos_code = stcfesdt.aos_code AND
stmaos.aos_period = stcfesdt.aos_period INNER JOIN
stmfesqa ON stmaos.student_id = stmfesqa.student_id AND stmaos.aos_code = stmfesqa.aos_code AND
stmaos.acad_period = stmfesqa.acad_period AND stmaos.aos_period = stmfesqa.aos_period INNER JOIN
stmbiogr ON stmaos.student_id = stmbiogr.student_id ON stsacper.acad_period = stmaos.acad_period AND
stsacper.start_date <= stcsessd.aos_start_dt AND stsacper.end_date >= stcsessd.aos_end_dt INNER JOIN
delspurs ON stmaos.acad_period = delspurs.acad_period INNER JOIN
stcstatd ON stmaos.aos_code = stcstatd.aos_code LEFT OUTER JOIN
deloutwgt ON stcfesdt.nvq_lvl_ind = deloutwgt.nvq_lvl_ind LEFT OUTER JOIN
delbasicIT ON stcfesdt.qual_aim = delbasicIT.qual_aim AND stcfesdt.subject_code = delbasicIT.subj_code LEFT OUTER JOIN
delESOL ON stcfesdt.subject_code = delESOL.subj_code LEFT OUTER JOIN
stmadres ON stmbiogr.student_id = stmadres.student_id AND stmbiogr.perm_add_id = stmadres.add_id LEFT OUTER JOIN
delsubjarea RIGHT OUTER JOIN
delsubj ON delsubjarea.subj_area = delsubj.subj_area ON stcfesdt.subject_code = delsubj.subj_code left outer join
stcstdet on stmaos.aos_code = stcstdet.aos_code inner join
nicisreports.dbo.ndaqquals on replace(stcstdet.text_field1,'/','') = nicisreports.dbo.ndaqquals.qual_ref
where stmaos.acad_period = @p_acad_period
and (stmaos.return_ind = 'F' OR stmaos.return_ind = 'B')
AND (stmaos.stage_ind = 'E')
AND (stcfesdt.qual_aim IS not null)
AND (stcfesdt.qual_aim IS not null)
and (stmfesqa.fund_source <> '09' OR stmfesqa.fund_source IS NULL)
and stmaos.start_date is not null
and stmaos.start_date <= getdate()

OPEN @base
FETCH NEXT FROM @base
INTO @student_id, @acad_period, @aos_code, @aos_period, @full_desc_static, @full_desc_session,
@dept_code, @aos_start_dt, @exp_length, @unit_length, @student_year,
@hrs_per_week, @aos_end_dt, @no_of_weeks, @hrs_total,@student_type,
@moa_code, @qual_aim, @subject_code,@subject_area_group, @course_group,
@nvq_lvl_ind, @geolocn_code, @surname, @forename, @birth_dt, @post_code,
@student_attend_mode, @fee_waiver, @start_date, @student_status, @end_date,
@spec_learn_disab, @age, @ESOL, @threshold1, @threshold2, @threshold3, @outcome, @basicIT,
@subj_area_weight, @out_weight, @additionality_yn,@ndaq_qual

when i run the follwoing i get

exec testflu '06/07'

Server: Msg 16924, Level 16, State 1, Procedure testflu, Line 210
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

i am unsure cos from what i can see i ahve included all, am i missing something

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 07:09:04

You have 44 selected columns

SELECT		stmaos.student_id,
		stmaos.acad_period,
		stmaos.aos_code,
		stmaos.aos_period,
		stcstatd.full_desc,
		stcsessd.full_desc AS SessionDescription, 
		stcsessd.dept_code,
		stcsessd.aos_start_dt,
		stcsessd.exp_length,
		stcsessd.unit_length,
		ISNULL(stmaos.student_year, 0) AS student_year, 
		stcsessd.hrs_per_week / 100 AS hrs_per_week,
		stcsessd.aos_end_dt,
		stcsessd.no_of_weeks,
		stcsessd.hrs_total / 100 AS hrs_total, 
		stmfesqa.student_type,
		stcsessd.moa_code,
		stcfesdt.qual_aim,
		stcfesdt.subject_code,
		delsubj.subj_area,
		ISNULL(delsubj.subj_course_group, 0) AS subj_course_group,
		stcfesdt.nvq_lvl_ind,
		stcsessd.geolocn_code,
		stmbiogr.surname,
		stmbiogr.forename,
		stmbiogr.birth_dt,
		stmadres.post_code,
		stmaos.attend_mode,
		stmfesqa.fee_override,
		stmaos.start_date,
		RTRIM(stmfesqa.course_status) AS course_status,
		stmfesqa.course_status_dt as end_date, 
		stmfesqa.spec_learn_disab,
		FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age,
		delESOL.subj_code AS ESOL,
		delspurs.threshold1, /* Log No.132694 - SQL changed*/
		delspurs.threshold2,
		delspurs.threshold3,
		stmfesqa.outcome,
		delbasicIT.qual_aim AS basicIT,
		ISNULL(delsubjarea.subj_area_weight, 0.00) AS subj_area_weight,
		ISNULL(deloutwgt.weighting, 0.10) AS weighting,
		ISNULL(stmaos.additionality_yn, 0) AS additionality_yn,
		nicisreports.dbo.ndaqquals.qual_ref

but only 42 varaibles to put them in

INTO		@student_id,
		@acad_period,
		@aos_code,
		@aos_period,
		@full_desc_static,
		@full_desc_session,
		@dept_code,
		@aos_start_dt,
		@exp_length,
		@unit_length,
		@student_year,
		@hrs_per_week,
		@aos_end_dt,
		@no_of_weeks,
		@hrs_total,@student_type,
		@moa_code,
		@qual_aim,
		@subject_code,
		@subject_area_group,
		@course_group, 
		@nvq_lvl_ind,
		@geolocn_code,
		@surname,
		@forename,
		@birth_dt,
		@post_code,
		@student_attend_mode,
		@fee_waiver,
		@start_date,
		@student_status,
		@end_date,
		@spec_learn_disab, 
		@age, @ESOL,
		@threshold1,
		@threshold2,
		@threshold3,
		@outcome,
		@basicIT,
		@subj_area_weight,
		@out_weight, 
		@additionality_yn,
		@ndaq_qual

Also, the line

FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age,

should be replaced with

		CASE
			WHEN DATEPART(day, stmbiogr.birth_dt) > DATEPART(day, stsacper.age_date) THEN DATEDIFF(month, stmbiogr.birth_dt, stsacper.age_date) - 1
			ELSE DATEDIFF(month, stmbiogr.birth_dt, stsacper.age_date
		END / 12 AS Age

E 12°55'05.25"
N 56°04'39.16"

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-08-20 : 07:19:40

Cheers for help, is it better practice to use 'case', this is a bit new to me and i am greatful of your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 07:45:21

The CASE above calculates the right age exactly to the day, whereas the dividing by 365 do not.

If you persist in dividing, you should use 365.2425

E 12°55'05.25"
N 56°04'39.16"

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-08-20 : 09:02:25

Hi there, just checked and i get 44 variables, you have two on same row of another so i am still getting error

stmaos.student_id, @student_id,
stmaos.acad_period, @acad_period,
stmaos.aos_code, @aos_code,
stmaos.aos_period, @aos_period,
stcstatd.full_desc, @full_desc_static,
stcsessd.full_desc AS SessionDescription, @full_desc_session,
stcsessd.dept_code, @dept_code,
stcsessd.aos_start_dt, @aos_start_dt,
stcsessd.exp_length, @exp_length,
stcsessd.unit_length, @unit_length,
ISNULL(stmaos.student_year, 0) AS student_year, @student_year,
stcsessd.hrs_per_week / 100 AS hrs_per_week, @hrs_per_week,
stcsessd.aos_end_dt, @aos_end_dt,
stcsessd.no_of_weeks, @no_of_weeks,
stcsessd.hrs_total / 100 AS hrs_total, @hrs_total,
stmfesqa.student_type, @student_type
stcsessd.moa_code, @moa_code,
stcfesdt.qual_aim, @qual_aim,
stcfesdt.subject_code, @subject_code,
delsubj.subj_area, @subject_area_group,
ISNULL(delsubj.subj_course_group, 0) AS subj_course_group @course_group,
stcfesdt.nvq_lvl_ind, @nvq_lvl_ind,
stcsessd.geolocn_code, @geolocn_code,
stmbiogr.surname, @surname,
stmbiogr.forename, @forename,
stmbiogr.birth_dt, @birth_dt,
stmadres.post_code, @post_code,
stmaos.attend_mode, @student_attend_mode,
stmfesqa.fee_override, @fee_waiver,
stmaos.start_date, @start_date,
RTRIM(stmfesqa.course_status) AS course_status, @student_status,
stmfesqa.course_status_dt as end_date, @end_date,
stmfesqa.spec_learn_disab, @spec_learn_disab,
FLOOR(DATEDIFF(day, stmbiogr.birth_dt, stsacper.age_date))/365 AS Age, @age,
delESOL.subj_code AS ESOL, @ESOL,
delspurs.threshold1, @threshold1,
delspurs.threshold2, @threshold2,
delspurs.threshold3, @threshold3,
stmfesqa.outcome, @outcome, delbasicIT.qual_aim AS basicIT, @basicIT,
ISNULL(delsubjarea.subj_area_weight, 0.00) AS subj_area_weight, @subj_area_weight,
ISNULL(deloutwgt.weighting, 0.10) AS weighting, @out_weight,
ISNULL(stmaos.additionality_yn, 0) AS additionality_yn, @additionality_yn,
nicisreports.dbo.ndaqquals.qual_ref @ndaq_qual

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:22:01

You're right. My bad.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:24:51

What about the DECLARATION part?

DECLARE @base CURSOR

UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period

DELETE FROM delflubase
WHERE (acad_period = @p_acad_period)

SET @total_students = 0
SET @total_enrolments = 0
SET @acad_year_spurs = 0.00

SET @base = CURSOR LOCAL FAST_FORWARD
FOR ...

The red part seems malplaced...

UPDATE delflu
SET tot_students = 0,
tot_enrolments = 0,
flu_total = 0,
processing = 1,
process_start = GETDATE(),
process_end = NULL
WHERE acad_period = @p_acad_period

DELETE FROM delflubase
WHERE acad_period = @p_acad_period

SELECT @total_students = 0,
@total_enrolments = 0,
@acad_year_spurs = 0.00

DECLARE @base CURSOR LOCAL FAST_FORWARD
FOR ...

E 12°55'05.25"
N 56°04'39.16"

pmccann1
Posting Yak Master

107 Posts

Posted - 2007-08-20 : 09:25:15

i am glad you loooked that case change you give me actauly makes the code run quicker which is great

cant figure out why i am getting that error though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources