前些天学习了在Extjs的前台下,运用Linq to SQL语言从SQL数据库中取数据。

后来,觉得Linq to SQL的语句有点麻烦,所以学习了一下SQL数据库中的存储过程去完成相似的功能。

下面,把代码贴出来:

 

1、单表查询:

use STU
//STU为所用数据库名
go
create procedure STU_selectAll
//STU_selectAll为存储过程名
as
select
* from student

 

 

 

代码
1、多表查询:
use STU
//STU为所用数据库名
go
create procedure STU_select
//STU_selectAll为存储过程名
as
select S.stu_id,S.stu_name,C.cou_mark
from student S inner Join course C
//将S、C表连接起来
on S.stu_id = C.stu_id

 

 

 

/*带有参数的查询*/
use STU
go
create procedure STU_select
@inp_stu_id
int
as
select
* from student
where stu_id
= @inp_stu_id

/*exec STU_select 2*/ //为这个查询的‘执行语句’

 

 

代码
*插入语句*/
use STU
go
create procedure STU_insert
@inp_stu_id
int,
@inp_stu_name nvarchar(
10),
@inp_stu_depart nvarchar(
10),
@inp_stu_age
int
as
begin tran A
insert into student values(@inp_stu_id,@inp_stu_name,@inp_stu_depart,@inp_stu_age)
commit tran A
go
/*插入语句*/
/*exec STU_insert 7,'张成','计算机',22*/

 

 

 

代码
/*修改语句*/
use STU
go
create procedure STU_update
@inp_stu_id
int,
@inp_stu_name nvarchar(
10),
@inp_stu_depart nvarchar(
10),
@inp_stu_age
int
as
begin tran A
update student set stu_name
= @inp_stu_name, stu_depart = @inp_stu_depart, stu_age = @inp_stu_age
where stu_id
= @inp_stu_id
commit tran A
go
/*修改语句*/

 

 

 

/*删除语句*/
use STU
go
create procedure STU_delete
@inp_stu_id
int
as
begin tran A
delete from student
where stu_id
= @inp_stu_id
commit tran A
go
/*删除语句*/

 

 

 

代码
/*一个带有双循环、输出参数的存储过程*/
use FS
go
create procedure printA
@inp_i
int,
@inp_j
int,
@out_i
int output,
@out_j
int output
as
while @inp_i <3
begin
while @inp_j <2
begin
set @inp_j
= @inp_j + 1
set @out_j
= @inp_j
end
set @inp_i
= @inp_i + 1
set @out_i
= @inp_i
end
go


/*执行语句*/
declare @out_i
int,@out_j int
exec printA
1,1,@out_i output,@out_j output
print @out_i
print @out_j

 

 

 

代码
/*带动态参数的存储过程*/
use
try
go
create procedure insert_xx
@inp_url nvarchar(
16)
as
declare @tem_one nvarchar(
100)
declare @tem_four nvarchar(
30)
declare @tem_total nvarchar(
250)
if @inp_url is null
return
else
set @tem_one = N'insert into x select * from OPENROWSET(' + N'''' + N'MICROSOFT.JET.OLEDB.4.0' + N'''' + N',' + N'''' + N'Excel 5.0;HDR=YES;DATABASE=G:\'
set @tem_four = N'.xls' + N'''' + N',sheet1$);'
set @tem_total = @tem_one + @inp_url + @tem_four
EXECUTE sp_executesql @tem_total
/*带动态参数的存储过程*/

 

posted on 2010-08-06 17:01  guolebin7  阅读(509)  评论(0编辑  收藏  举报