proc.sql

简单的sql语句 数据与汇总。 高手多多指点。

use northwind
go

create proc dbo.overdueorders
as
select * from dbo.orders
where requireddate < getdate() and shippeddate is null
go

exec overdueorders
go

select getdate()
go

exec sp_help overdueorders
go

exec sp_helptext overdueorders
go

exec sp_depends overdueorders
go

create proc employeecustomer
as
select
upper(substring(lastname,1,4)+substring(firstname,1,1)),
'Northwind Traders',rtrim(firstname)+' ' + lastname,
 'employee',address,city,region,postalcode,country,
('[phone] '+' x' + extension),null
from employees
where hiredate <getdate()
go

insert into customers
exec employeecustomer
go

select rtrim(name) from b
go

select name from b
go

create proc TT
@name nvarchar(8),
@age tinyint=20,
@city nvarchar(10)=null
as
go

exec  TT
@name = 'HPT',
@age =23,
@city ='CH'
go

exec TT 'hk',24,'CH'
go

create proc math
@m1 smallint,
@m2 smallint,
@data smallint output
as
set @data=@m1*@m2
go

declare @final  smallint
exec math 5,6,@final output
select 'The result is:',@final
go

exec sp_recompile math
go

exec math with recompile
go

exec xp_cmdshell
go

use northwind
go
alter proc getorders
@customerid nchar(10)
as 
select orderid,customerid from orders
where customerid=@customerid
return(@@rowcount)
go

exec getorders 'vinet'