|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Procedure expects parameter which was not supplied'@SubjectID', which was not supplied." In my stored procedure. I'm new to SQL and SQL syntax, so I was hoping someone could look at my procedure and walk me thru why it's wrong? This is it below: CREATE PROCEDURE PAFF_Activity_Calcs @SubjectID nvarchar, @VisitNumber int, @Activity_Strength nvarchar as BEGIN declare @Weekday_Hours float declare @Weekend_Hours float declare @Hour_Per_Week float declare @KCal_Per_Week float declare @Percent_KCal_Per_Week float select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) Update PAFF_Activity_Calcs Set Hour_Per_week = @Hour_Per_Week where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength END GO I appreciate any help!!! Thanks! What is your synatx when you execute the procedure?
It should be PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 Show quote "ST" <S*@discussions.microsoft.com> wrote in message news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects > parameter > '@SubjectID', which was not supplied." In my stored procedure. I'm new to > SQL and SQL syntax, so I was hoping someone could look at my procedure and > walk me thru why it's wrong? This is it below: > > CREATE PROCEDURE PAFF_Activity_Calcs > @SubjectID nvarchar, > @VisitNumber int, > @Activity_Strength nvarchar > > as > BEGIN > declare @Weekday_Hours float > declare @Weekend_Hours float > declare @Hour_Per_Week float > declare @KCal_Per_Week float > declare @Percent_KCal_Per_Week float > > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) > > Update PAFF_Activity_Calcs > Set Hour_Per_week = @Hour_Per_Week > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > END > GO > > I appreciate any help!!! Thanks! Well, I just did this: exec PAFF_Activity_Calcs 'ST-1', '1',
'Moderate' and now i get: Invalid object name 'PAFF_Activity_Calcs'. Do I have typos somewhere? Sorry, I'm not good with the syntax yet.... Immy wrote: Show quote > What is your synatx when you execute the procedure? > It should be > PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 > > > > "ST" <S*@discussions.microsoft.com> wrote in message > news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... > > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects > > parameter > > '@SubjectID', which was not supplied." In my stored procedure. I'm new to > > SQL and SQL syntax, so I was hoping someone could look at my procedure and > > walk me thru why it's wrong? This is it below: > > > > CREATE PROCEDURE PAFF_Activity_Calcs > > @SubjectID nvarchar, > > @VisitNumber int, > > @Activity_Strength nvarchar > > > > as > > BEGIN > > declare @Weekday_Hours float > > declare @Weekend_Hours float > > declare @Hour_Per_Week float > > declare @KCal_Per_Week float > > declare @Percent_KCal_Per_Week float > > > > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > > > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) > > > > Update PAFF_Activity_Calcs > > Set Hour_Per_week = @Hour_Per_Week > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > END > > GO > > > > I appreciate any help!!! Thanks! Well, I just did this: exec PAFF_Activity_Calcs 'ST-1', '1',
'Moderate' and now i get: Invalid object name 'PAFF_Activity_Calcs'. Do I have typos somewhere? Sorry, I'm not good with the syntax yet.... Show quote "Immy" wrote: > What is your synatx when you execute the procedure? > It should be > PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 > > > > "ST" <S*@discussions.microsoft.com> wrote in message > news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... > > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects > > parameter > > '@SubjectID', which was not supplied." In my stored procedure. I'm new to > > SQL and SQL syntax, so I was hoping someone could look at my procedure and > > walk me thru why it's wrong? This is it below: > > > > CREATE PROCEDURE PAFF_Activity_Calcs > > @SubjectID nvarchar, > > @VisitNumber int, > > @Activity_Strength nvarchar > > > > as > > BEGIN > > declare @Weekday_Hours float > > declare @Weekend_Hours float > > declare @Hour_Per_Week float > > declare @KCal_Per_Week float > > declare @Percent_KCal_Per_Week float > > > > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > > > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) > > > > Update PAFF_Activity_Calcs > > Set Hour_Per_week = @Hour_Per_Week > > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > > Activity_Strength = @Activity_Strength > > END > > GO > > > > I appreciate any help!!! Thanks! > > > You're syntax is trying to update the stored procedure!!!
You need to update a table, not the procedure. Check your update statement and replace the name with the correct table that you should be updating. Immy Show quote "ST" <S*@discussions.microsoft.com> wrote in message news:D21E8704-859D-4930-8470-A87A0F71ACB7@microsoft.com... > Well, I just did this: exec PAFF_Activity_Calcs 'ST-1', '1', > 'Moderate' > and now i get: Invalid object name 'PAFF_Activity_Calcs'. > > Do I have typos somewhere? Sorry, I'm not good with the syntax yet.... > > "Immy" wrote: > >> What is your synatx when you execute the procedure? >> It should be >> PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 >> >> >> >> "ST" <S*@discussions.microsoft.com> wrote in message >> news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... >> > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects >> > parameter >> > '@SubjectID', which was not supplied." In my stored procedure. I'm new >> > to >> > SQL and SQL syntax, so I was hoping someone could look at my procedure >> > and >> > walk me thru why it's wrong? This is it below: >> > >> > CREATE PROCEDURE PAFF_Activity_Calcs >> > @SubjectID nvarchar, >> > @VisitNumber int, >> > @Activity_Strength nvarchar >> > >> > as >> > BEGIN >> > declare @Weekday_Hours float >> > declare @Weekend_Hours float >> > declare @Hour_Per_Week float >> > declare @KCal_Per_Week float >> > declare @Percent_KCal_Per_Week float >> > >> > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> > Activity_Strength = @Activity_Strength >> > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> > Activity_Strength = @Activity_Strength >> > >> > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) >> > >> > Update PAFF_Activity_Calcs >> > Set Hour_Per_week = @Hour_Per_Week >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> > Activity_Strength = @Activity_Strength >> > END >> > GO >> > >> > I appreciate any help!!! Thanks! >> >> >> DUH!! Argh...it's been a long day. Thanks for pointing out the typo. Ok so
now that THAT is fixed...this is my new SP, and table...it keeps saying "0 rows updated" and it should be inserting the calc into Hour_Per_Week when I do exec PAFF_Activity_Calcs 'ST-1', '1', 'Moderate': (**Hr_Per_Wk is abbreviated here) TABLE SubjectID|VisitNumber|Activity_Strength|Weekday_Hour|Weekend_Hours|Hr_Per_Wk| ST-1 1 Moderate 2 3 ST-1 1 Vigorous 3 2 ST-1 1 Light 1 1 ST-1 2 Moderate 1 2 ST-1 2 Light 3 3 NEW SP: CREATE PROCEDURE PAFF_Activity_Calcs @SubjectID nvarchar, @VisitNumber int, @Activity_Strength nvarchar as declare @Weekday_Hours float declare @Weekend_Hours float declare @Hour_Per_Week float declare @KCal_Per_Week float declare @Percent_KCal_Per_Week float select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Answers where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Answers where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) Update PAFF_Activity_Answers Set Hour_Per_week = @Hour_Per_Week where SubjectID = @SubjectID and VisitNumber = @VisitNumber and Activity_Strength = @Activity_Strength --insert into PAFF_Activity_Answers(Hour_Per_Week) --Values(@Hour_Per_Week) GO Show quote "Immy" wrote: > You're syntax is trying to update the stored procedure!!! > You need to update a table, not the procedure. > Check your update statement and replace the name with the correct table that > you should be updating. > > Immy > > "ST" <S*@discussions.microsoft.com> wrote in message > news:D21E8704-859D-4930-8470-A87A0F71ACB7@microsoft.com... > > Well, I just did this: exec PAFF_Activity_Calcs 'ST-1', '1', > > 'Moderate' > > and now i get: Invalid object name 'PAFF_Activity_Calcs'. > > > > Do I have typos somewhere? Sorry, I'm not good with the syntax yet.... > > > > "Immy" wrote: > > > >> What is your synatx when you execute the procedure? > >> It should be > >> PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 > >> > >> > >> > >> "ST" <S*@discussions.microsoft.com> wrote in message > >> news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... > >> > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects > >> > parameter > >> > '@SubjectID', which was not supplied." In my stored procedure. I'm new > >> > to > >> > SQL and SQL syntax, so I was hoping someone could look at my procedure > >> > and > >> > walk me thru why it's wrong? This is it below: > >> > > >> > CREATE PROCEDURE PAFF_Activity_Calcs > >> > @SubjectID nvarchar, > >> > @VisitNumber int, > >> > @Activity_Strength nvarchar > >> > > >> > as > >> > BEGIN > >> > declare @Weekday_Hours float > >> > declare @Weekend_Hours float > >> > declare @Hour_Per_Week float > >> > declare @KCal_Per_Week float > >> > declare @Percent_KCal_Per_Week float > >> > > >> > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs > >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > >> > Activity_Strength = @Activity_Strength > >> > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs > >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > >> > Activity_Strength = @Activity_Strength > >> > > >> > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) > >> > > >> > Update PAFF_Activity_Calcs > >> > Set Hour_Per_week = @Hour_Per_Week > >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > >> > Activity_Strength = @Activity_Strength > >> > END > >> > GO > >> > > >> > I appreciate any help!!! Thanks! > >> > >> > >> > > > I will never insert as you have the insert commented out.
You only have an active update in the proc. Show quote "ST" <S*@discussions.microsoft.com> wrote in message news:551D9EB3-9030-41C0-A2D4-446580182431@microsoft.com... > DUH!! Argh...it's been a long day. Thanks for pointing out the typo. Ok > so > now that THAT is fixed...this is my new SP, and table...it keeps saying "0 > rows updated" and it should be inserting the calc into Hour_Per_Week when > I > do exec PAFF_Activity_Calcs 'ST-1', '1', 'Moderate': (**Hr_Per_Wk is > abbreviated here) > > TABLE > SubjectID|VisitNumber|Activity_Strength|Weekday_Hour|Weekend_Hours|Hr_Per_Wk| > ST-1 1 Moderate 2 3 > ST-1 1 Vigorous 3 2 > ST-1 1 Light 1 1 > ST-1 2 Moderate 1 2 > ST-1 2 Light 3 3 > > NEW SP: > CREATE PROCEDURE PAFF_Activity_Calcs > @SubjectID nvarchar, > @VisitNumber int, > @Activity_Strength nvarchar > > as > > declare @Weekday_Hours float > declare @Weekend_Hours float > declare @Hour_Per_Week float > declare @KCal_Per_Week float > declare @Percent_KCal_Per_Week float > > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Answers > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Answers > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) > > Update PAFF_Activity_Answers > Set Hour_Per_week = @Hour_Per_Week > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and > Activity_Strength = @Activity_Strength > > --insert into PAFF_Activity_Answers(Hour_Per_Week) > --Values(@Hour_Per_Week) > GO > > > > > "Immy" wrote: > >> You're syntax is trying to update the stored procedure!!! >> You need to update a table, not the procedure. >> Check your update statement and replace the name with the correct table >> that >> you should be updating. >> >> Immy >> >> "ST" <S*@discussions.microsoft.com> wrote in message >> news:D21E8704-859D-4930-8470-A87A0F71ACB7@microsoft.com... >> > Well, I just did this: exec PAFF_Activity_Calcs 'ST-1', '1', >> > 'Moderate' >> > and now i get: Invalid object name 'PAFF_Activity_Calcs'. >> > >> > Do I have typos somewhere? Sorry, I'm not good with the syntax yet.... >> > >> > "Immy" wrote: >> > >> >> What is your synatx when you execute the procedure? >> >> It should be >> >> PAFF_Activity_Calcs Parameter1, Parameter2, Parameter3 >> >> >> >> >> >> >> >> "ST" <S*@discussions.microsoft.com> wrote in message >> >> news:82B26859-C4D0-4753-91F4-CCA7D05B01FB@microsoft.com... >> >> > Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects >> >> > parameter >> >> > '@SubjectID', which was not supplied." In my stored procedure. I'm >> >> > new >> >> > to >> >> > SQL and SQL syntax, so I was hoping someone could look at my >> >> > procedure >> >> > and >> >> > walk me thru why it's wrong? This is it below: >> >> > >> >> > CREATE PROCEDURE PAFF_Activity_Calcs >> >> > @SubjectID nvarchar, >> >> > @VisitNumber int, >> >> > @Activity_Strength nvarchar >> >> > >> >> > as >> >> > BEGIN >> >> > declare @Weekday_Hours float >> >> > declare @Weekend_Hours float >> >> > declare @Hour_Per_Week float >> >> > declare @KCal_Per_Week float >> >> > declare @Percent_KCal_Per_Week float >> >> > >> >> > select @Weekday_Hours = Weekday_Hours from PAFF_Activity_Calcs >> >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> >> > Activity_Strength = @Activity_Strength >> >> > select @Weekend_Hours = Weekend_Hours from PAFF_Activity_Calcs >> >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> >> > Activity_Strength = @Activity_Strength >> >> > >> >> > set @Hour_Per_Week = ((@Weekday_Hours *5) + (@Weekend_Hours * 2)) >> >> > >> >> > Update PAFF_Activity_Calcs >> >> > Set Hour_Per_week = @Hour_Per_Week >> >> > where SubjectID = @SubjectID and VisitNumber = @VisitNumber and >> >> > Activity_Strength = @Activity_Strength >> >> > END >> >> > GO >> >> > >> >> > I appreciate any help!!! Thanks! >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics