transact-sql. 1. declare declare @discount float set @discount = 10 select * from customers where...
TRANSCRIPT
Transact-SQL
1. Declare
declare @discount float
set @discount = 10
select * from customers where discnt > @discount
2. Get values from the select result
declare @mindiscount float
select @mindiscount = min(discnt) from customers
3. Display the result (in 2, the output is disabled, nothing is displayed)
select @mindiscount as DISCOUNT
4. @@ROWCOUNT
Returns the number of rows affected by the last statement.
(When you do the programming, you need to know there are how many records in the query resutls)
select * from customersselect @@rowcount as RowCounter
insert customers values ('c019', 'Heller', 'Rebecca', 12)select @@rowcount as RowCounter
update customers set name = 'Jones'where cid = 'c020'If @@ROWCOUNT = 0 print 'Warning: No rows were updated'
5. Begin ... End / If ... Else / Continue / Break
declare @v1 int, @v2 intset @v1 = 0set @v2 = 100
while (@v1 < 10)begin if(@v2 % 2 = 0) set @v2 = @v2 + 1 else set @v2 = @v2 + 2 set @v1 = @v1 + 1end
select @v1 as v1, @v2 as v2
5. Begin ... End / If ... Else / Continue / Break (cont)
declare @v1 int, @v2 intset @v1 = 0set @v2 = 100
while (@v1 < 10)begin if(@v1 = 5) break else set @v2 = @v2 + 2 set @v1 = @v1 + 1end
select @v1 as v1, @v2 as v2
6. Case (alias)
select cname, category = case
when discnt < 8 then 'low'
when discnt between 8 and 10 then 'med'
else 'high'
end
from customers
7. Printdeclare @mystr varchar(32)
set @mystr = 'Hello'
print @mystr
//Also try select @mystr
8. GoSignals the end of a batch of Transact-SQL
statements to the Microsoft® SQL Server™ utilities
declare @mindiscount floatselect @mindiscount = min(discnt) from customersselect @mindiscount GOselect @mindiscount //@mindiscount is not
defined after 'GO'
9. Operators• +, - , *, /, %
• =, < , > , <=, >=, !=, !>, !<
10. Data types• int, smalling, char(n), varchar(n), money,
float
11.1 Stored procedure - create1. create proc get_customer @cid char(4)as select cname from customers where cid = @cidgo
2. Exec get_customer SELECT CID FROM CUSTOMERS ’
11.2 help/delete1. check the interfacesp_help get_customer
2. check the source codesp_helptext get_customer
3. delete drop proc get_customer
11.3 different return resultscreate proc myproc1as declare @v1 int set @v1 = 1return
create proc myproc2as declare @v1 int set @v1 = 1 select @v1return
11.4 return multiple datasets
create proc myproc3
as
select * from customers
select * from agents
return
11.5 multiple input parameters
create proc myproc4@v1 float,@v2 intas select * from customers where discnt < @v1 select * from agents where percentage < @v2return
exec myproc4 15,5
11.6 return values
create proc myproc5@discount float,@counter int outputas select @counter = count(*) from customers where discnt < @discountreturn
declare @mycounter intexec myproc5 15, @mycounter outputselect @mycounter
11.6 return values - cont
create proc myproc6@discount float,@counter int outputas select * from customers where discnt < @discount set @counter = @@rowcountreturn
declare @mycounter intexec myproc6 15, @mycounter outputselect @mycounter
11.6 return values - contcreate proc myproc7@counter1 int output,@counter2 int outputas select * from customers set @counter1 = @@rowcount select * from agents set @counter2 = @@rowcountreturn
declare @mycounter1 int, @mycounter2 intexec myproc7 @mycounter1 output, @mycounter2 outputselect @mycounter1, @mycounter2
12. UDF (user-defined funcation)
create function average_dollars(@customerid char(4))returns moneyasbegin declare @avg money select @avg = avg(dollars) from orders where cid = @customerid return @avgend
sp_help average_dollars
sp_helptext average_dollars
12. UDF - cont
declare @avg_sale float
set @avg_sale = zhoupf.average_dollars('c001')
select @avg_sale
*Inside UDF, don’t make any changes to tables.
*Difference between UDF and stored procedure.