1樓：東坡**站
一般分為十種情況，每種語法各不相同：
1、 建立語法
create proc | procedure pro_name
[ [=預設值] [output],
[=預設值] [output],
....]as
sql_statements
2、 建立不帶引數儲存過程
--建立儲存過程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
gocreate proc proc_get_student
asselect * from student;
--呼叫、執行儲存過程
exec proc_get_student;
3、 修改儲存過程
--修改儲存過程
alter proc proc_get_student
asselect * from student;
4、 帶參儲存過程
--帶參儲存過程
if (object_id('proc_find_stu', 'p') is not null)
drop proc proc_find_stu
gocreate proc proc_find_stu(@startid int, @endid int)
asselect * from student where id between @startid and @endid
goexec proc_find_stu 2, 4;
5、 帶萬用字元引數儲存過程
--帶萬用字元引數儲存過程
if (object_id('proc_findstudentbyname', 'p') is not null)
drop proc proc_findstudentbyname
gocreate proc proc_findstudentbyname(@name varchar(20) = '%j%', @nextname varchar(20) = '%')
asselect * from student where name like @name and name like @nextname;
goexec proc_findstudentbyname;
exec proc_findstudentbyname '%o%', 't%';
6、 帶輸出引數儲存過程
if (object_id('proc_getstudentrecord', 'p') is not null)
drop proc proc_getstudentrecord
gocreate proc proc_getstudentrecord(
@id int, --預設輸入引數
@name varchar(20) out, --輸出引數
@age varchar(20) output--輸入輸出引數)as
select @name = name, @age = age from student where id = @id and *** = @age;
go--
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getstudentrecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
7、 不快取儲存過程
--with recompile 不快取
if (object_id('proc_temp', 'p') is not null)
drop proc proc_temp
gocreate proc proc_temp
with recompile
asselect * from student;
goexec proc_temp;
8、 加密儲存過程
--加密with encryption
if (object_id('proc_temp_encryption', 'p') is not null)
drop proc proc_temp_encryption
gocreate proc proc_temp_encryption
with encryption
asselect * from student;
goexec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
9、 帶遊標引數儲存過程
if (object_id('proc_cursor', 'p') is not null)
drop proc proc_cursor
gocreate proc proc_cursor
@cur cursor varying output
asset @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go--呼叫
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--呼叫儲存過程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age:
' + convert(char, @age);
endclose @exec_cur;
deallocate @exec_cur;--刪除遊標
10、 分頁儲存過程
---儲存過程、row_number完成分頁
if (object_id('pro_page', 'p') is not null)
drop proc proc_cursor
gocreate proc pro_page
@startindex int,
@endindex int
asselect count(*) from product
;select * from (
select row_number() over(order by pid) as rowid, * from product
) temp
where temp.rowid between @startindex and @endindex
go--drop proc pro_page
exec pro_page 1, 4
----分頁儲存過程
if (object_id('pro_page', 'p') is not null)
drop proc pro_stu
gocreate procedure pro_stu(
@pageindex int,
@pagesize int)as
declare @startrow int, @endrow int
set @startrow = (@pageindex - 1) * @pagesize +1
set @endrow = @startrow + @pagesize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) twhere t.number between @startrow and @endrow;
exec pro_stu 2, 2;
希望採納！！
2樓：匿名使用者
看著語法沒有問題。建議看一下 p_course1表和course表字定義的是否一樣的。
如果直接這樣插入。要求倆表的表結構必須是一致的
sql 儲存過程建立表的問題，Sql 儲存過程建立表的問題！
救救大漢江山吧 把建立語句拼成字串 儲存過程中用exec去執行建立 殤忄路 額 什麼問題呢？你要建立儲存過程？create or replace procedure test pro p name in varchar2,out msg out varchar2 定義輸入引數p name 輸出引數o...
sql儲存過程處理陣列引數，sql 儲存過程 傳入兩組陣列引數
declare str varchar 500 declare insert varchar 8000 select insert insert into youtable values select str str1,1,1,1,1 str2,2,2,2,2 str3,3,3,3,5 str4,4...
sql 迴圈呼叫儲存過程，sql server 儲存過程如何對select語句的結果集進行迴圈操作
你的語句裡，randcardid都是預設值，所以是重複的。create table temp str char 19 declare randcardid char 19 exec proc randcardid randcardid output declare num int set num 0...