SQL Server 2008 Stored procedure sample _mssql2008

 
--A stored procedure 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
 
 
--functions that return 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
--
 
Invoke Method--
declare @count int
exec @count =myfunction 2
Print @count
 
--function that returns a table--
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

--Pre-stitching
Set @sql = ' select * FROM table where 1=1 '
if (@addDate's not NULL)
set @sql = @sql + ' and ad Ddate = ' + @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 code some redundancy
if (@addDate is not null) and (@name <> ")
select * FROM table where adddate = @addD Ate and name = @name
else if (@addDate is not null) and (@name = ")
select * FROM table where adddate = @addD Ate
else if (@addDate is null) and (@name <> ")
select * from table where and name = @name
Else i F (@addDate is null) and (@name = ' ")
select * FROM Table
--The second type is the
select * from table where (adddate = @addDate or @addDate is null) and (name = @name or @name = ')-
-The third is a
SELECT * from table where
add Date = 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-
 
-Assign value using SELECT statement
declare @user1 nvarchar (m)
SELECT @ user1= ' John '
print @user1
declare @user2 nvarchar
Select @user2 = Name from st_user where id=1
print @ User2
 
--Assign value using UPDATE statement
declare @user3 nvarchar
update st_user Set @user3 = Name where id=1
print @ User3
 

Second, table, temporary table, table variables

--Creates a temporary table 1 create TABLE #DU_User1 ([ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar] (N) Ull, [Rtx] [nvarchar] (4) NOT NULL, [Name] [nvarchar] (5) is not NULL, [Password] [nvarchar] (max) NULL, [
State] [nvarchar] (8) is not NULL); --Inserts a record into the 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 the newly generated temporary table select * into #DU_User2 from St_user where id<8-query and combine two temporary tables select * from #DU_User2 where I D<3 Union SELECT * FROM #DU_User1--deletes two temporary table drop table #DU_User1 drop table #DU_User2--Creates a temporary table create table #t ([
   ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar] (a) not NULL, [RTX] [nvarchar] (4) is not NULL, [Name] [nvarchar]  (5) Not NULL, [Password] [nvarchar] (max) NULL, [state] [nvarchar] (8) is not NULL,)--Inserts a query result set (multiple data) into a temporary table insert Into the #t select * FROM St_user-you cannot insert the--select * into #t the FROM dbo. St_useR--Add a column that is an int self-growing sub segment ALTER TABLE #t add [myid] int not NULL IDENTITY (1,1)--Adds a column that defaults to fill the globally unique identity ALTER TABLE #t add [myid1] un Iqueidentifier not NULL default (NEWID ()) SELECT * from #t drop table #t--Add to query result set from growth column-No primary key: Select IDENTITY (int, 1,1) as ID, Name, [login],[Password] into #t from St_user select * from #t--with PRIMARY key: Select (select SUM (1) from St_use R where id<= a.id) as myid,* from St_user an order by MyID--Defines a table variable declare @t table (ID int not NULL, MSG Nvarch AR () null) insert into @t values (1, ' 1 ') inserts into @t values (2, ' 2 ') SELECT * from @t

Third, cycle

--while loops compute 1 to 100 of and
declare @a int
declare @ sum int
set @a=1
set @ sum =0 while
@a<=100
begin
   SET @ sum +=@a
   set @a+=1
end
Print @ sum

Four, conditional statement

--if,else Conditional Branch
if (1+1=2)
begin
   print ' to '
end
else
begin
   print ' wrong '
end
 
--when then conditional branch
declare @today int
declare @week nvarchar (3)
set @today =3
Set @week = case When the
   @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
 
--Define a cursor declare user_cur for
select I D,oid,[login] from St_user-
-Open cursor
open user_cur while
@ @fetch_status =0
begin
--read cursor
   fetch Next from User_cur to @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
 
--Define a cursor declare user_cur for
SEL ECT Id,oid,[login] from St_user-
-open cursor open
user_cur while
@ @fetch_status =0
begin
--read cursor
   fetch NEXT from User_cur to @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

--Create TRIGGER
trigger User_onupdate on 
   st_user for Update as 
   declare @msg nvarchar (m)
   --@ MSG Record modification
   Select @msg = N ' name from ' + Deleted. Name + N ' "modified to" ' + Inserted. Name + ' "' from inserted,deleted--
   Insert Log table inserts into
   [log] (MSG) VALUES (@msg)-
   
-delete trigger
drop trigger User_onupdate

Seven, stored procedures

--Creating a stored procedure with output parameters create
PROCEDURE pr_sum
   @a int,
   @b int,
   @ Sum int output as
begin< C19/>set @ sum =@a+@b
End
 
--creating return returns value stored procedure create
PROCEDURE pr_sum2
   @a int,
   @b int
as< C26/>begin
   return @a+@b
End
   
--executes the stored procedure to obtain the output returned value
declare @mysum int
execute pr_sum 1,2,@ MySum output
Print @mysum
 
--Performs a stored procedure to get return returns 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