Mssql server creates databases to stored procedures and user-defined functions

Create database MyDb

On

(

Name = mainDb,

Filename = 'C: MyDbmainDb. mdf ',

Size = 10,

Maxsize = 100,

Filegrowth = 4

),

(

Name = secondDb,

Filename = 'C: MyDbsecondDb. Ndf ',

Size = 15,

Maxsize = 28,

Filegrowth = 2

)

Log on

(

Name = log_Db,

Filename = 'C: MyDblog_Db ',

Size = 20,

Filegrowth = 10%

)

-- General format of creating a database tutorial

Use mydb

 

Create table student

(

StuId int primary key identity (1, 1 ),

StuName varchar (20) not null,

StuAge int not null check (stuAge between 20 and 50 ),

StuSex varchar (4) not null check (stusex in ('F', 'M ')),

StuDept varchar (20) check ')),

StuAddress varchar (20) not null

)

Drop table student

 

Select * from student

Insert into student values ('Sun yebao', 22, 'M', 'soft engineering department ', 'xingtai city, Hebei province ')

Insert into student values ('sunting', 20, 'F', 'e-Commerce department ', 'xingtai city, Hebei province ')

Insert into student values ('mongos', 22, 'F', 'e-Commerce department ', 'xingtai city, Hebei province ')

Insert into student values ')

Insert into student values ('Wang dand', 22, 'M', 'soft engineering department ', 'fuyang city, Hebei province ')

Insert into student values ('Chen Haibo ', 22, 'M', 'soft engineering department', 'Hefei city, Hebei province ')

 

 

-- Stored Procedure for a single input/output parameter,

Create proc Myproc

@ Dept varchar (20), @ count int output

As

If not exists (select * from student where Studept = @ dept)

Print 'no student of the specified type exists !! '

Else

Select @ count = Count (*) from student where studept = @ dept

Drop proc myproc

 

 

-- Execute the stored procedure

Declare @ result int

Exec myproc 'software', @ result output

Print @ result

 

 

-- Stored procedure of multiple inputs and outputs.

Create proc Searchstu

@ Area varchar (20), @ Sex varchar (2), @ count int output, @ avg_age int output

As

Select @ count = count (*), @ avg_age = Avg (stuage) from student

Where stuaddress = @ area and stusex = @ sex

 

-- Execute the stored procedure

 

Declare @ stuNo int, @ stuAvg_age int

Exec searchstu 'xingtai city, Hebei province ', 'M', @ stuNo output, @ stuAvg_age output

Select @ stuNo as total number of students, @ stuavg_age as average age

 

-- User-defined function (cube volume definition Title function returns a single value)

Create function dbo. CubicVolume

(@ CubeLength int, @ CubeHenght int, @ CubeWidth int)

Returns int

As

Begin

Return (@ CubeLength * @ CubeHenght * @ CubeWidth)

End

Drop function CubicVolume

 

-- Call this method

Select dbo. CubicVolume (10, 10, 10)

 

-- User-defined functions (a table is returned in the form of an embedded table)

Create function f_stuInfo (@ studept varchar (20 ))

Returns table

As

Return

(

Select * from student where studept = @ studept

)

-- Call this method

Select * from dbo. f_stuInfo ('softwary ')

 

 

-- User-defined function (multi-statement table value function, returns a table of some data that the user wants to display)

 

Create function f_stuSexTye (@ stuDept varchar (10 ))

Returns @ t_stuDetailInfo table (

StuName varchar (20 ),

StuAge int,

StuSex varchar (4)

)

As

Begin

Insert into @ t_stuDetailInfo

Select Stuname, stuage,

Case stusex

When 'm' then 'male'

When 'F' then 'female'

End

From student

Where stuDept = @ studept

Return

End

 

-- Call this method function

Select * from dbo. f_stuTye ('softwary ')