Using create procedure statement to create stored procedure proc with input parameter, output parameter and return value_ Stuscore, query the highest and lowest scores of the corresponding course score according to the input course name parameter, set the highest and lowest scores as the output parameters, and return the status value of the program execution @ @ error. Executing stored procedure proc_ Stuscore to calculate the highest and lowest scores of "advanced mathematics
时间: 2024-03-06 14:48:10 浏览: 27
Sure, I can help you with that. Here's the stored procedure code:
```
CREATE PROCEDURE proc_Stuscore
@course_name VARCHAR(50),
@highest_score INT OUTPUT,
@lowest_score INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Declare variables
DECLARE @error INT;
DECLARE @course_id INT;
-- Get course id for given course name
SELECT @course_id = course_id FROM courses WHERE course_name = @course_name;
-- Query highest and lowest scores for given course id
SELECT @highest_score = MAX(score), @lowest_score = MIN(score)
FROM scores
WHERE course_id = @course_id;
-- Get error status
SET @error = @@ERROR;
-- Return status value
RETURN @error;
END
```
To execute the stored procedure for the "advanced mathematics" course, you can use the following code:
```
DECLARE @highest_score INT;
DECLARE @lowest_score INT;
DECLARE @status INT;
EXEC @status = proc_Stuscore 'advanced mathematics', @highest_score OUTPUT, @lowest_score OUTPUT;
IF @status <> 0
BEGIN
PRINT 'Error executing stored procedure';
END
ELSE
BEGIN
PRINT 'Highest score: ' + CONVERT(VARCHAR(10), @highest_score);
PRINT 'Lowest score: ' + CONVERT(VARCHAR(10), @lowest_score);
END
```
This will output the highest and lowest scores for the "advanced mathematics" course, as well as the status of the stored procedure execution.
相关推荐
![application/x-zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)