October 16, 2018 at 7:46 am
Does anyone see any reason multiple UPDATE statements like below cannot work in a single SP? Does the keyword END prohibit this running? The SP runs with no errors:
CREATE Procedure [dbo].[ETL]
As
UPDATE GCDF_DB..People SET
GCDFInstructor = CASE InstructorInt when -1 then 1 when 0 then 0
END
UPDATE GCDF_DB..People SET
gender = CASE genderText when 'Male' then 1 when 'Female' then 2 END,
ethnicity = CASE raceText when 'Unknown' then 1 when 'Caucasian' then 2
when 'African American' then 3 when 'Hispanic' then 4
when 'Asian American' then 5 when 'Native American' then 6
when 'Multiracial' then 7 when 'Other' then 8
END
UPDATE a SET
a.applicationDate = b.applicationDate, a.certificationDate = b.certificationDate, a.certificationExpireDate = b.certificationExpireDate,
a.FinalRenewalNoticeSentDate = b.FinalRenewalNoticeSentDate, a.RecertificationDate = b.RecertDate,
a.InactiveNoticeSentDate = b.InactiveNoticeSentDate, a.certstatustext = b.status_text, a.ExpiredNoticeSentDate = b.ExpiredNoticeSentDate
FROM GCDF_DB..certs a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
UPDATE GCDF_DB..certs SET StatusID = CASE certstatustext
when 'Applicant' then 1 when 'Active' then 2
when 'Inactive' then 3 when 'Expired Full' then 4
when 'Expired Provisional' then 5 when 'Conditional' then 6
when 'Relinquished' then 7 when 'Closed' then 8
END
GO
October 16, 2018 at 7:54 am
The END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?
October 16, 2018 at 8:03 am
Jonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?
The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
October 16, 2018 at 8:06 am
briancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?
October 16, 2018 at 8:09 am
Jonathan AC Roberts - Tuesday, October 16, 2018 8:06 AMbriancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?
Is there a way of getting a row count for each individual UPDATE block?
October 16, 2018 at 8:15 am
briancampbellmcad - Tuesday, October 16, 2018 8:09 AMJonathan AC Roberts - Tuesday, October 16, 2018 8:06 AMbriancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?Is there a way of getting a row count for each individual UPDATE block?
You could edit the SP a bit to either SELECT or PRINT @@ROWCOUNT :
ALTER Procedure [dbo].[ETL] As
UPDATE GCDF_DB..People SET
GCDFInstructor = CASE InstructorInt when -1 then 1 when 0 then 0
END
SELECT @@ROWCOUNT
UPDATE GCDF_DB..People SET
gender = CASE genderText when 'Male' then 1 when 'Female' then 2 END,
ethnicity = CASE raceText when 'Unknown' then 1 when 'Caucasian' then 2
when 'African American' then 3 when 'Hispanic' then 4
when 'Asian American' then 5 when 'Native American' then 6
when 'Multiracial' then 7 when 'Other' then 8
END
UPDATE a SET
a.applicationDate = b.applicationDate, a.certificationDate = b.certificationDate, a.certificationExpireDate = b.certificationExpireDate,
a.FinalRenewalNoticeSentDate = b.FinalRenewalNoticeSentDate, a.RecertificationDate = b.RecertDate,
a.InactiveNoticeSentDate = b.InactiveNoticeSentDate, a.certstatustext = b.status_text, a.ExpiredNoticeSentDate = b.ExpiredNoticeSentDate
FROM GCDF_DB..certs a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
SELECT @@ROWCOUNT
UPDATE GCDF_DB..certs SET StatusID = CASE certstatustext
when 'Applicant' then 1 when 'Active' then 2
when 'Inactive' then 3 when 'Expired Full' then 4
when 'Expired Provisional' then 5 when 'Conditional' then 6
when 'Relinquished' then 7 when 'Closed' then 8
END
SELECT @@ROWCOUNT
GO
The SP will only output an @@ROWCOUNT value of 1 after this.
Or just run it with SET NOCOUNT OFF and it will output the number of rows for each query.
October 16, 2018 at 8:34 am
Jonathan AC Roberts - Tuesday, October 16, 2018 8:15 AMbriancampbellmcad - Tuesday, October 16, 2018 8:09 AMJonathan AC Roberts - Tuesday, October 16, 2018 8:06 AMbriancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?Is there a way of getting a row count for each individual UPDATE block?
You could edit the SP a bit to either SELECT or PRINT @@ROWCOUNT :
ALTER Procedure [dbo].[ETL] AsUPDATE GCDF_DB..People SET
GCDFInstructor = CASE InstructorInt when -1 then 1 when 0 then 0
END
SELECT @@ROWCOUNTUPDATE GCDF_DB..People SET
gender = CASE genderText when 'Male' then 1 when 'Female' then 2 END,
ethnicity = CASE raceText when 'Unknown' then 1 when 'Caucasian' then 2
when 'African American' then 3 when 'Hispanic' then 4
when 'Asian American' then 5 when 'Native American' then 6
when 'Multiracial' then 7 when 'Other' then 8
ENDUPDATE a SET
a.applicationDate = b.applicationDate, a.certificationDate = b.certificationDate, a.certificationExpireDate = b.certificationExpireDate,
a.FinalRenewalNoticeSentDate = b.FinalRenewalNoticeSentDate, a.RecertificationDate = b.RecertDate,
a.InactiveNoticeSentDate = b.InactiveNoticeSentDate, a.certstatustext = b.status_text, a.ExpiredNoticeSentDate = b.ExpiredNoticeSentDate
FROM GCDF_DB..certs a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
SELECT @@ROWCOUNTUPDATE GCDF_DB..certs SET StatusID = CASE certstatustext
when 'Applicant' then 1 when 'Active' then 2
when 'Inactive' then 3 when 'Expired Full' then 4
when 'Expired Provisional' then 5 when 'Conditional' then 6
when 'Relinquished' then 7 when 'Closed' then 8
END
SELECT @@ROWCOUNT
GO
The SP will only output an @@ROWCOUNT value of 1 after this.
Or just run it with SET NOCOUNT OFF and it will output the number of rows for each query.
It still just outputs about 16,000 rows and only one line of count
October 16, 2018 at 9:03 am
briancampbellmcad - Tuesday, October 16, 2018 8:34 AMJonathan AC Roberts - Tuesday, October 16, 2018 8:15 AMbriancampbellmcad - Tuesday, October 16, 2018 8:09 AMJonathan AC Roberts - Tuesday, October 16, 2018 8:06 AMbriancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?Is there a way of getting a row count for each individual UPDATE block?
You could edit the SP a bit to either SELECT or PRINT @@ROWCOUNT :
ALTER Procedure [dbo].[ETL] AsUPDATE GCDF_DB..People SET
GCDFInstructor = CASE InstructorInt when -1 then 1 when 0 then 0
END
SELECT @@ROWCOUNTUPDATE GCDF_DB..People SET
gender = CASE genderText when 'Male' then 1 when 'Female' then 2 END,
ethnicity = CASE raceText when 'Unknown' then 1 when 'Caucasian' then 2
when 'African American' then 3 when 'Hispanic' then 4
when 'Asian American' then 5 when 'Native American' then 6
when 'Multiracial' then 7 when 'Other' then 8
ENDUPDATE a SET
a.applicationDate = b.applicationDate, a.certificationDate = b.certificationDate, a.certificationExpireDate = b.certificationExpireDate,
a.FinalRenewalNoticeSentDate = b.FinalRenewalNoticeSentDate, a.RecertificationDate = b.RecertDate,
a.InactiveNoticeSentDate = b.InactiveNoticeSentDate, a.certstatustext = b.status_text, a.ExpiredNoticeSentDate = b.ExpiredNoticeSentDate
FROM GCDF_DB..certs a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
SELECT @@ROWCOUNTUPDATE GCDF_DB..certs SET StatusID = CASE certstatustext
when 'Applicant' then 1 when 'Active' then 2
when 'Inactive' then 3 when 'Expired Full' then 4
when 'Expired Provisional' then 5 when 'Conditional' then 6
when 'Relinquished' then 7 when 'Closed' then 8
END
SELECT @@ROWCOUNT
GO
The SP will only output an @@ROWCOUNT value of 1 after this.
Or just run it with SET NOCOUNT OFF and it will output the number of rows for each query.It still just outputs about 16,000 rows and only one line of count
Have you tried running just the three UPDATE statements in one go in SSMS?
October 16, 2018 at 9:08 am
Jonathan AC Roberts - Tuesday, October 16, 2018 8:15 AMbriancampbellmcad - Tuesday, October 16, 2018 8:09 AMJonathan AC Roberts - Tuesday, October 16, 2018 8:06 AMbriancampbellmcad - Tuesday, October 16, 2018 8:03 AMJonathan AC Roberts - Tuesday, October 16, 2018 7:54 AMThe END is just the end of a CASE statement.
If it runs already with no errors why do you think it might not run?The second 3rd UPDATE statement usually returns 17,000 as the record count when ran alone, the others about 16,000, but the SP run gives about 16,000.
How are you getting the count?
@@ROWCOUNT returns the number of rows updated by the last statement, so that would be from the last update in your SP?Is there a way of getting a row count for each individual UPDATE block?
You could edit the SP a bit to either SELECT or PRINT @@ROWCOUNT :
ALTER Procedure [dbo].[ETL] AsUPDATE GCDF_DB..People SET
GCDFInstructor = CASE InstructorInt when -1 then 1 when 0 then 0
END
SELECT @@ROWCOUNTUPDATE GCDF_DB..People SET
gender = CASE genderText when 'Male' then 1 when 'Female' then 2 END,
ethnicity = CASE raceText when 'Unknown' then 1 when 'Caucasian' then 2
when 'African American' then 3 when 'Hispanic' then 4
when 'Asian American' then 5 when 'Native American' then 6
when 'Multiracial' then 7 when 'Other' then 8
ENDUPDATE a SET
a.applicationDate = b.applicationDate, a.certificationDate = b.certificationDate, a.certificationExpireDate = b.certificationExpireDate,
a.FinalRenewalNoticeSentDate = b.FinalRenewalNoticeSentDate, a.RecertificationDate = b.RecertDate,
a.InactiveNoticeSentDate = b.InactiveNoticeSentDate, a.certstatustext = b.status_text, a.ExpiredNoticeSentDate = b.ExpiredNoticeSentDate
FROM GCDF_DB..certs a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
SELECT @@ROWCOUNTUPDATE GCDF_DB..certs SET StatusID = CASE certstatustext
when 'Applicant' then 1 when 'Active' then 2
when 'Inactive' then 3 when 'Expired Full' then 4
when 'Expired Provisional' then 5 when 'Conditional' then 6
when 'Relinquished' then 7 when 'Closed' then 8
END
SELECT @@ROWCOUNT
GO
The SP will only output an @@ROWCOUNT value of 1 after this.
Or just run it with SET NOCOUNT OFF and it will output the number of rows for each query.
Nevermind... this works... Thanks Jonathan!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply