CODEWITHSUNDEEP
sqlsql-serverdatabasestored-proceduresrdbms
Zoro
Zoro

Reputation: 21

SQL Server stored procedure with more than 1 query

I am unable to create a stored procedure in SQL Server.

Calculate the total of students to summation of Mark1, Mark2 and Mark3 and update for student whose city is Chennai using a stored procedure. In the same procedure, add a new column AvgMark in StudentMarks table and calculate average for all students.

This is what I have tried so far

create or alter procedure TotMarks
as 
begin
    update StudentMarks 
    set Total = Mark1 + Mark2 + Mark3 
    from StudentMarks 
    join CityMaster on StudentMarks.CityID = CityMaster.CityID 
    where CityName = 'Chennai' 

    alter table StudentMarks 
        add AvgMark decimal default Total/3
end

I am getting this error and I don't know why.

The name "Total" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables.
Column names are not permitted.

And I created a null column Total in StudentMarks while creating the table, so no need to create that column in the stored procedure to update the summation of Mark1, Mark2 and Mark3.

Upvotes: 1

Views: 314

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you always want to save Total/3 in AvgMark:

create or alter procedure TotMarks
as 
begin
update StudentMarks set Total=coalesce(Mark1,0)+coalesce(Mark2,0)+coalesce(Mark3,0) from StudentMarks join CityMaster on StudentMarks.CityID=CityMaster.CityID WHERE CityName='Chennai' 
alter table StudentMarks add AvgMark as Total/3
end

Upvotes: 1

Related Questions