SQL Server 2008 Database Tutorial

--Stored procedures with input parameters--

Create proc Getcomment

(@commentid int)

As

SELECT * from Comment where commentid= @commentid

--A stored procedure with input and output parameters--

Create proc Getcommentcount

@newsid int,

@count int Output

As

Select @count =count (*) from Comment where newsid= @newsid

--a function that returns a single value--

Create function MyFunction

(@newsid int)

returns int

As

Begin

DECLARE @count int

Select @count =count (*) from Comment where newsid= @newsid

return @count

End

--Call Method--

DECLARE @count int

EXEC @count =myfunction 2

Print @count

--The return value is a table function--

Create function getfunctiontable

(@newsid int)

Returns table

As

Return

(SELECT * from Comment where newsid= @newsid)

--Returns a call to a function of a table--

SELECT * from Getfunctiontable (2)

Multi-condition query without concatenation of SQL strings in SQL Server stored procedure

--The previous splicing

Set @sql = ' select * FROM table where 1=1 '

If (@addDate is not null)

Set @sql = @sql + ' and adddate = ' + @addDate + '

if (@name ' and is not null)

Set @sql = @sql + ' and name = ' + @name + '

EXEC (@sql)

The following is a solution that does not use a concatenation SQL string to implement a multiple conditional query

--The first way is to feel the code is a bit redundant

If (@addDate is not null) and (@name ")

SELECT * FROM table where adddate = @addDate and name = @name

else if (@addDate is not null) and (@name = ')

SELECT * FROM table where adddate = @addDate

else if (@addDate is null) and (@name ")

SELECT * FROM table where and name = @name

else if (@addDate is null) and (@name = ')

SELECT * FROM table

--The second way is

SELECT * FROM table where (adddate = @addDate or @addDate is null) and (name = @name or @name = ")

--The Third Way is

SELECT * FROM table where

Adddate = Case @addDate are NULL THEN adddate ELSE @addDate End,

name = Case @name when ' THEN name ELSE @name End

SQL Server stored procedure Basic syntax

One, define variables

--Simple assignment

DECLARE @a int

Set @a=5

Print @a

--Using the SELECT statement to assign a value

declare @user1 nvarchar (50)

Select @user1 = ' John '

Print @user1

declare @user2 nvarchar (50)

Select @user2 = Name from St_user where id=1

Print @user2

--Using the UPDATE statement to assign a value

declare @user3 nvarchar (50)

Update St_user Set @user3 = Name where id=1

Print @user3

Second, table, temporary table, table variables

--Create temporary table 1

CREATE TABLE #DU_User1

(

[ID] [int] not NULL,

[Oid] [INT] Not NULL,

[Login] [nvarchar] () not NULL,

[RTX] [nvarchar] (4) Not NULL,

[Name] [nvarchar] (5) Not NULL,

[Password] [nvarchar] (max) Null

[State] [nvarchar] (8) Not NULL

);

--Insert a record into temporary table 1

Insert into #DU_User1 (ID,OID,[LOGIN],RTX, Name, [Password],state) VALUES (100,2, ' LS ', ' 0000 ', ' temporary ', ' 321 ', ' special ');

--query data from St_user, populate to newly generated temporary tables

SELECT * Into #DU_User2 the from St_user where ID

--Query and combine two temporary tables

SELECT * FROM #DU_User2 where ID

--Delete two temporary tables

drop table #DU_User1

drop table #DU_User2

--Create a temporary table

CREATE TABLE #t

(

[ID] [int] not NULL,

[Oid] [INT] Not NULL,

[Login] [nvarchar] () not NULL,

[RTX] [nvarchar] (4) Not NULL,

[Name] [nvarchar] (5) Not NULL,

[Password] [nvarchar] (max) Null

[State] [nvarchar] (8) Not NULL,

)

--Inserts a query result set (multiple data) into a temporary table

INSERT INTO #t select * from St_user

--cannot be inserted like this

--select * into #t the FROM dbo. St_user

--Add a column to the INT self-growing sub segment

ALTER TABLE #t add [myid] int not NULL IDENTITY (1,1)

--Add a column, default fill globally unique identity

ALTER TABLE #t add [myid1] uniqueidentifier not NULL default (NEWID ())

SELECT * from #t

drop table #t

--Add a growth column to the query result set

--when there is no primary key:

Select IDENTITY (int, 1,1) as ID, Name, [login],[Password] into #t from St_user

SELECT * from #t

--When a primary key is available:

Select (select SUM (1) from St_user where ID a.id) as myid,* from St_user an order by MyID

--Define Table variables

DECLARE @t table

(

ID int NOT NULL,

msg nvarchar (m) null

)

INSERT into @t values (1, ' 1 ')

INSERT into @t values (2, ' 2 ')

SELECT * FROM @t

Third, cycle

--while loops calculate 1 to 100 of the and

DECLARE @a int

DECLARE @ sum int

Set @a=1

SET @ Sum =0

While @a100

Begin

SET @ Sum +=@a

Set @a+=1

End

Print @ sum

Four, conditional statement

--if,else Conditional Branch

if (1+1=2)

Begin

print ' Yes '

End

Else

Begin

print ' wrong '

End

--when then Conditional branch

DECLARE @today int

declare @week nvarchar (3)

Set @today =3

Set @week = case

When @today =1 then ' Monday '

When @today =2 then ' Tuesday '

When @today =3 then ' Wednesday '

When @today =4 then ' Thursday '

When @today =5 then ' Friday '

When @today =6 then ' Saturday '

When @today =7 then ' Sunday '

Else ' value error '

End

Print @week

V. Cursors

DECLARE @ID int

DECLARE @Oid int

DECLARE @Login varchar (50)

--Define a cursor

Declare user_cur cursor FOR select Id,oid,[login] from St_user

--Open cursor

Open User_cur

While @ @fetch_status =0

Begin

--Read Cursors

FETCH NEXT from User_cur into @ID, @Oid, @Login

Print @ID

--print @Login

End

Close User_cur

--Destroying cursors

Deallocate user_cur

V. Cursors

DECLARE @ID int

DECLARE @Oid int

DECLARE @Login varchar (50)

--Define a cursor

Declare user_cur cursor FOR select Id,oid,[login] from St_user

--Open cursor

Open User_cur

While @ @fetch_status =0

Begin

--Read Cursors

FETCH NEXT from User_cur into @ID, @Oid, @Login

Print @ID

--print @Login

End

Close User_cur

--Destroying cursors

Deallocate user_cur

VI. triggers

Temporary table in trigger:

Inserted

Storing data after insert and update operations

Deleted

Data stored prior to delete and update operations

--Creating triggers

Create Trigger User_onupdate

On St_user

For Update

As

declare @msg nvarchar (50)

--@msg Record changes

Select @msg = N ' name from ' + Deleted. Name + N ' "modified to" ' + Inserted. Name + ' "' from inserted,deleted

--Insert the log table

insert INTO [LOG] (MSG) VALUES (@msg)

--Deleting triggers

Drop Trigger User_onupdate

Seven, stored procedures

--Create a stored procedure with output parameters

CREATE PROCEDURE Pr_sum

@a int,

@b int,

@ Sum int output

As

BEGIN

SET @ Sum =@a+@b

End

--Create return returns value stored procedure

CREATE PROCEDURE pr_sum2

@a int,

@b int

As

BEGIN

Return @a+@b

End

--Execute stored procedure to get output return value

DECLARE @mysum int

Execute pr_sum 1,2, @mysum output

Print @mysum

--Executes the stored procedure to obtain the return returned value

DECLARE @mysum2 int

Execute @mysum2 = pr_sum2 1,2

Print @mysum2

Viii. Custom Functions

Classification of functions:

1) Scalar value function

2 Table-valued function

A: Inline table-valued functions

B: Multi-statement table-valued functions

3) system function

--New scalar value function

Create function Func_sum1

(

@a int,

@b int

)

returns int

As

Begin

Return @a+@b

End

--New inline table-valued function

Create function Func_usertab_1

(

@myId int

)

Returns table

As

Return (SELECT * from St_user where Id@myid)

--New multi-statement table-valued function

Create function func_usertab_2

(

@myId int

)

Returns @t table

(

[ID] [int] not NULL,

[Oid] [INT] Not NULL,

[Login] [nvarchar] () not NULL,

[RTX] [nvarchar] (4) Not NULL,

[Name] [nvarchar] (5) Not NULL,

[Password] [nvarchar] (max) Null

[State] [nvarchar] (8) Not NULL

)

As

Begin

INSERT INTO @t select * St_user where Id@myid

Return

End

--calling table-valued functions

SELECT * FROM dbo. Func_usertab_1 (15)

--calling a scalar-valued function

DECLARE @s int

Set @s=dbo. FUNC_SUM1 (100,50)

Print @s

--removing scalar-valued functions

Drop function Func_sum1