如何创建存储过程 [英] How to create a stored procedure

问题描述

我需要创建一个程序来计算入学率.我有以下需要在1个过程中一起使用的查询.

1.计算一个年级的学习者总数.

SELECT成绩，计数(learner_id)
来自学习者的
GROUP BY年级；

2.计算该年级通过的学习者人数.

SELECT成绩，计数(learner_id)
来自学习者的
状态=``P''
GROUP BY年级；

3.获取通过率.

num_passed/tot_num * 100;



在此先感谢:-)

I need to create a a procedure for calculating a school pass rate. i''ve got the following queries that i need to use together in 1 proc.

1. count total number of learners in a grade.

SELECT grade, count(learner_id)
FROM learners
GROUP BY grade;

2. count number of learners passed in that grade.

SELECT grade, count(learner_id)
FROM learners
WHERE Status = ''P''
GROUP BY grade;

3. Get the pass rate.

num_passed/tot_num * 100;



Thanks in advance :-)

推荐答案

下面的链接可能会帮助您在SQl Server中创建新过程.
http://msdn.microsoft.com/en-us/library/ms187926.aspx

您的SQL过程中可以有多个查询，并且它将根据您在过程中使用的选择查询的数量返回多个记录集.

Below link may help you to Create new Procedure in SQl Server.
http://msdn.microsoft.com/en-us/library/ms187926.aspx

You can have multiple Queries in your SQL Procedure, And it will return you multiple recordsets based on number of select queries you used in your Procedure.

A step by step tutorial[^].
The short of it...

create procedure [procedure_name]
   -- optionally some params.
   @status char(1)
as
   --your query here
   select someStuff
   from someTable
   where status = @status
end

此SP从someTable返回someStuff，其状态等于提供的参数.
希望对您有所帮助！ :)


我可能暗示在SP中只能使用简单的select语句.但是事实并非如此，您可以在SP中执行联接，更新，删除，使用游标，循环，临时表等操作.它也不一定需要返回值.

This SP returns someStuff from someTable where the status is equal to the parameter provided.
Hope that helps! :)


I may imply that only simple select statements are possible in an SP. This is not the case however, you can do joins, updates, deletes, use cursors, loops, temp tables, practically do anything in an SP. It doesn''t necissarily need a return value either.

这篇关于如何创建存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！