IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type IN ('U')) DROP TABLE [dbo].[User] GO CREATE TABLE [dbo].[User] ( [id] int NOT NULL, [name] nchar(10) COLLATE Chinese_PRC_CI_AS NULL, [did] int NULL ) GO ALTER TABLE [dbo].[User] SET (LOCK_ESCALATION = TABLE) GO -- ---------------------------- -- Records of User -- ---------------------------- INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'1', N'one ', NULL) GO INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'2', N'tow ', NULL) GO INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'3', N'three ', NULL) GO INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'4', N'faure ', N'1') GO INSERT INTO [dbo].[User] ([id], [name], [did]) VALUES (N'5', N'five ', N'1') GO -- ---------------------------- -- Primary Key structure for table User -- ---------------------------- ALTER TABLE [dbo].[User] ADD CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 exec sp_rename student, stuInfo;--修改表、索引、列的名称 exec sp_renamedb myTempDB, myDB;--更改数据库名称 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 exec sp_helpdb;--数据库帮助,查询数据库信息 exec sp_helpdb master;
create proc | procedure pro_name [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ] as SQL_statements
if(exists(select * from sys.objects where name='proc_get_user')) drop proc proc_get_user go create proc proc_get_user as --CreateUser:一棵小白杨 select * from TestC.dbo.[User];
exec proc_get_User
--带参存储过程 if (object_id('proc_find_user', 'P') is not null) drop proc proc_find_user go create proc proc_find_user(@startId int, @endId int) as select * from TestC.dbo.[User] where id between @startId and @endId go
exec proc_find_user 2, 4;
--带通配符参数存储过程 if (object_id('proc_findUserByName', 'P') is not null) drop proc proc_findUserByName go create proc proc_findUserByName(@name varchar(20) = '%t%', @nextName varchar(20) = '%') as select * from TestC.dbo.[User] where name like @name and name like @nextName; go
exec proc_findUserByName; exec proc_findUserByName '%o%', 't%';
if (object_id('proc_getUserRecord', 'P') is not null) drop proc proc_getUserRecord go create proc proc_getUserRecord( @id int, --默认输入参数 @name varchar(20) out, --输出参数 @d_id int output--输入输出参数 ) as select @name = name,@d_id =did from TestC.dbo.[User] where id = @id and did = @d_id; go
-- declare @id int, @name varchar(20), @temp varchar(20); set @id = 4; set @temp = 1; exec proc_getUserRecord @id, @name out, @temp output; select @name, @temp; print @name + '#' + @temp;
--WITH RECOMPILE 不缓存 if (object_id('proc_temp', 'P') is not null) drop proc proc_temp go create proc proc_temp with recompile as select * from TestC.dbo.[User] ; go
exec proc_temp;
--加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null) drop proc proc_temp_encryption go create proc proc_temp_encryption with encryption as select * from TestC.dbo.[User]; go
if (object_id('proc_cursor', 'P') is not null) drop proc proc_cursor go create proc proc_cursor @cur cursor varying output as set @cur = cursor forward_only static for select id, name from TestC.dbo.[User]; open @cur; go
declare @exec_cur cursor; declare @id int, @name varchar(20); exec proc_cursor @cur = @exec_cur output;--调用存储过程 fetch next from @exec_cur into @id, @name; while (@@fetch_status = 0) begin fetch next from @exec_cur into @id, @name; print 'id: ' + convert(varchar, @id) + ', name: ' + @name ; end close @exec_cur; deallocate @exec_cur;--删除游标
SQL-Server存储过程基础