Stored procedure of SqlServer2012 instance

--1. Call the help system to find the system stored procedure or function to display the version number and current system time of SQL Server. (hint: in help, you can query the corresponding functions or stored procedures according to keywords to complete the task.) the code screenshot and running result screenshot are as follows.

--3. Add a field seleNUM to the Course table, and write the stored procedure: after passing in the student ID and Course ID -- Course ID, insert the corresponding Course selection record into the s ﹣ C table, and add 1 to the -- seleNUM of the corresponding Course in the Course table. Call the stored procedure,

--Test execution results. Code screenshot and running result screenshot are as follows

--4. Create a stored procedure. The parameter is student ID. the output information is the name, course name and grade of the specified student ID.

--When running, if no student number is specified, the system will prompt "please enter student number"; if the student number does not exist, the system will prompt "student number does not exist".

--5. Create a stored procedure Pro Accum, and use the while loop to realize the cumulative calculation function of integers from 1 to input parameter n,

--The calculation results are stored in the output parameter sum. The stored procedure defined is called by T-SQL to calculate 1 + 2 + +100 and output.

--1,Call help system to find system stored procedure or function to display SQL Server Version number and current system time of. (hint: in help, you can query the corresponding functions or stored procedures according to keywords to complete the task.) the code screenshot and running result screenshot are as follows.
select @@VERSION 'Version number' ,GETDATE() 'system time'
--2,Create a stored procedure to query the average score and the highest score of each student's selected course. Call the stored procedure,
--Test execution results. The code screenshot and running result screenshot are as follows.
create procedure PD1_selectresult
as
begin
	select s_id Student ID,avg(result) Average score,max(result) Highest score
	from s_c 
	group by s_id,course_id
end

exec PD1_selectresult
--3,take Course Add a field to the table seleNUM,And write stored procedure: pass in student number s_id Course number--course_id After, complete at S_C Insert the corresponding course selection record in the table, and Course For the corresponding courses in the table--seleNUM Plus 1. Call the stored procedure,
--Test execution results. Code screenshot and running result screenshot are as follows
alter table course
add seleNUM int

select * from course

update course
set seleNUM =(select count(s_id)
	from s_c
	where s_c.course_id=course.course_id)

select * from course

create procedure PD2_course_record
@s_id char(10),
@course_id char(10)
as
begin
	insert into s_c (s_id,course_id)
	values(@s_id,@course_id)
	update course
	set seleNUM =seleNUM +1
	where course_id=@course_id
end

select * from s_c
exec PD2_course_record '2016010101','0007'
select * from s_c
select * from course
--4,Create a stored procedure. The parameter is student number s_id,The output information is the name, course name and grade corresponding to the specified student number.
--When running, if no student number is specified, the system will prompt "please enter student number"; if the student number does not exist, the system will prompt "student number does not exist".
create procedure PD2_print_result
@s_id char(10)=null
as
begin
	if @s_id is NULL
		print 'Please enter student ID'
	else
		begin 
			if exists(select s_id from s_c where s_id = @s_id )
			begin
				select student.s_id 'Student ID', s_name 'Full name',course.course_id 'Course title',result 'achievement'
				from student,course,s_c
				where student.s_id =s_c.s_id  and s_c.course_id =course.course_id and student.s_id =@s_id 
			end
			else
				print'Student ID does not exist'
		end
end

exec PD2_print_result
exec PD2_print_result '2016010101'
exec PD2_print_result '2016010109'
--5,Create a stored procedure Pro_Accum,utilize while Loop implementation calculation from 1 to input parameters n The accumulation calculation function of integers between,
--Save calculation results into output parameters sum. And pass T-SQL Calling defined stored procedure, calculating 1+2+...+100 And output.
create procedure Pro_Accum
@n int
as
begin
	declare @sum int 
	declare @i int 
	set @sum=0
	set @i=1
	while(@i<=@n)
	begin
		set @sum = @sum+@i
		set @i=@i+1
	end 
	print @sum
end 

exec Pro_Accum '100'

 

Tags: Stored Procedure SQL

Posted on Fri, 31 Jan 2020 00:05:26 -0800 by Mundo