sql数据库执行存储过程

CREATE PROCEDURE myStroredProcedure AS
 
....
 
OR
 
CREATE PROCEDURE mYsTOREDpROCEDURE @{pARAMETER  Name} {data type} AS
 
eg:
CREATE  PROCEDURE  LatestTasks @Count int AS
SET ROWCOUNT @Count
SELECT TaskName AS LatestTasks,DataCreated
FROM Tasks
ORDER BY DateCreated DESC

----new 2018-07-03 
  insert into @tab_xmxkxx
    select  DISTINCT CONVERT(NVARCHAR(50),GETDATE(),112) AS jhrq,
    tab_sgxkdwxx.wsslbh as wsslbh,
    tab_xmxkxx.xmmc as xmmc,
    tab_sgxkdwxx.dwmc AS zbdwmc,
    CONVERT(NVARCHAR(50),tab_xmxkxx.sdrq,112) AS hfxkzrq,
    tab_xmxkxx.fzdwmc AS hfxkzbm

    from 
       (select bjbh,bdh,wsslbh,dwmc FROM [SgXk].[tab_zbb_xmxkxx_dwxx] AS sgxkdwxx where ISNULL(sgxkdwxx.wsslbh,'') <> ''  AND sgxkdwxx.dwlb = 'sg') tab_sgxkdwxx,
       (select bjbh,bdh,xmmc,fzdwmc,sdrq from sgxk.tab_zbb_xmxkxx xmxkxx where  xmxkxx.state='SdApproved' and xmxkxx.fzrq BETWEEN @startTime AND @endTime) tab_xmxkxx,
          @tab_htxx as tab_htxx
    where tab_sgxkdwxx.bjbh = tab_xmxkxx.bjbh  COLLATE Chinese_PRC_CS_AS and tab_sgxkdwxx.bdh = tab_xmxkxx.bdh   COLLATE Chinese_PRC_CS_AS
     and tab_sgxkdwxx.wsslbh = tab_htxx.wsslbh;

-----------------------------------------------------------------------------------------------------------------------------------

/*
-- old 2018-06-12
insert into @tab_xmxkxx
    SELECT DISTINCT CONVERT(NVARCHAR(50),GETDATE(),112) AS jhrq,
        sgxkdwxx.wsslbh AS wsslbh,
        xmxkxx.xmmc AS xmmc,
        sgxkdwxx.dwmc AS zbdwmc,
        CONVERT(NVARCHAR(50),xmxkxx.sdrq,112) AS hfxkzrq,
        xmxkxx.fzdwmc AS hfxkzbm FROM [SgXk].[tab_zbb_xmxkxx_dwxx] AS sgxkdwxx
       LEFT JOIN sgxk.tab_zbb_xmxkxx xmxkxx ON xmxkxx.bjbh COLLATE Chinese_PRC_CS_AS = sgxkdwxx.bjbh COLLATE Chinese_PRC_CS_AS AND sgxkdwxx.bdh COLLATE Chinese_PRC_CS_AS = xmxkxx.bdh COLLATE Chinese_PRC_CS_AS 
    WHERE ISNULL(sgxkdwxx.wsslbh,'') <> '' AND xmxkxx.state='SdApproved' AND sgxkdwxx.dwlb = 'sg' 

    AND sgxkdwxx.wsslbh IN (SELECT wsslbh FROM @tab_htxx)
*/    
-------------------------------------------------------------------------------------

--准备数据库
create database test_db

use test_db
--创建数据表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--准备数据
insert into books (book_name,book_price,book_auth)
alues
('孤剑段飞刀',99,'天客神宗'),
('天龙八部',25.6,'金庸'),
('雪山飞狐',32.7,'金庸'),
('平凡的世界',35.8,'路遥'),
('史记',54.8,'司马迁');

-- 创建存储过程

-- 1、无参数存储过程

if(exists(select * from sys.objects where name='getAllBooks'))
  drop proc getAllBooks
go
create proc getAllBooks
as
select * from books

exec getAllBooks

-- 修改存储过程
alter proc getAllBooks
as
select book_name from books


-- 重命名存储过程
sp_rename getAllBooks,getBookName

exec getBookName

-- 删除存储过程
drop proc getBookName


-- 创建带参数的存储过程

-- 2、带一个参数
if(exists(select * from sys.objects where name='searchBook'))
  drop proc searchBook
go
create proc searchBook(@bookID int)
as
select * from books where book_id=@bookID

exec searchBook 1

-- 3、带两个参数
if(exists(select * from sys.objects where name='twoParams'))
	drop proc twoParams
go
create proc twoParams(
	@bookID int,
	@book_auth varchar(20)
)
as
select * from books where book_id=@bookID and book_auth=@book_auth

exec twoParams 1,'天客神宗'

-- 4、创建有返回值的存储过程

if (exists (select * from sys.objects where name = 'getBookId'))
    drop proc getBookId
go
create proc getBookId(
    @bookAuth varchar(20),-- 输入参数,无默认值
    @bookId int output -- 输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth
    

-- 执行getBookId这个带返回值的存储过程
declare @id int -- 声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;-- as是给返回的列值起一个名字


-- 5、创建带有通配符的存储过程
if (exists (select * from sys.objects where name = 'charBooks'))
    drop proc charBooks
go
create proc charBooks(
    @bookAuth varchar(20)='金%',
    @bookName varchar(20)='%'
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
-- 执行存储过程charBooks
exec  charBooks    '天%','孤剑%';
exec sp_helptext 'charBooks'

-- 6、加密存储过程
if (object_id('books_encryption', 'P') is not null)
    drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
    select * from books;
    
-- 执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';-- 控制台会显示"对象 'books_encryption' 的文本已加密。"


-- 7、不缓存存储过程
-- with  recompile不缓存
if (object_id('book_temp', 'P') is not null)
    drop proc book_temp
go
create proc book_temp
with recompile
as
    select * from books;
go

exec book_temp;
exec sp_helptext 'book_temp';


-- 8、创建带游标参数的存储过程
if (object_id('book_cursor', 'P') is not null)
    drop proc book_cursor
go
create proc book_cursor
    @bookCursor cursor varying output
as
    set @bookCursor=cursor forward_only static for
    select book_id,book_name,book_auth from books
    open @bookCursor;
go
-- 调用book_cursor存储过程
declare @cur cursor,
        @bookID int,
        @bookName varchar(20),
        @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
    fetch next from @cur into @bookID,@bookName,@bookAuth;
    print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
            +' ,bookAuth: '+@bookAuth;
end
close @cur    -- 关闭游标
DEALLOCATE @cur; -- 释放游标



-- 9、创建分页存储过程
if (object_id('book_page', 'P') is not null)
    drop proc book_page
go
create proc book_page(
    @TableName varchar(50),            -- 表名
    @ReFieldsStr varchar(200) = '*',   -- 字段名(全部字段为*)
    @OrderString varchar(200),         -- 排序字段(必须!支持多字段不用加order by)
    @WhereString varchar(500) =N'',  -- 条件语句(不用加where)
    @PageSize int,                       -- 每页多少条记录
    @PageIndex int = 1 ,               -- 指定当前为第几页
    @TotalRecord int output            -- 返回总记录数
)
as
begin
     -- 处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;-- 总记录数语句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;-- 查询语句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    -- 第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;-- 返回总记录数
    --  END
    ---- 执行主语句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END
-- 调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;

--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;-- 总记录数。

在Transact-SQL下执行
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'master主数据密钥密码';

go
if exists (select * from sysdatabases where name='CrossLibraryTable1')
  drop database CrossLibraryTable1  /*检查有没有这个数据库，如果有就删除它。*/
go
create database CrossLibraryTable1
on primary
(
name='CrossLibraryTable1_data',  ------------ 养成好习惯,数据文件加_data
filename='F:\代码存放\数据库\CrossLibraryTable1_data.mdf',  ------------ 一定要是.mdf的文件,代表主数据文件
size=5mb, --默认数据库大小
maxsize=100mb, --最大容量
filegrowth=1mb --增长量
)
log on
(
name='CrossLibraryTable1_log',   ------------ 养成好习惯,日志文件加_log
filename='F:\代码存放\数据库\CrossLibraryTable1_log.ldf',  ------------ 一定要是.ldf的文件,代表日志文件
size=1mb, --默认数据库大小
filegrowth=10% --增长量
)

-- =============================================
 -- Author:                <Author,,Name>
 -- Create date: <Create Date,,>
 -- Description:        目前要备份的数据库名,备份文件路径均写在过程中,不需外部传入
 -- =============================================
 CREATE PROCEDURE UP_BackUPDataBase 
         @databasename nvarchar(50)=null,
         @strPath NVARCHAR(200)=null
 AS
 BEGIN        SET NOCOUNT ON;
         if(@databasename is null)
         begin
                 set @databasename = 'IS60DB'
         end
         if(@strPath is null)
         begin
                 set @strPath = convert(NVARCHAR(19),getdate(),120)
                 set @strPath = REPLACE(@strPath, ':' , '.')
                 set @strPath = 'E:\DBBAck\' + @databasename+@strPath + '.bak'
         end
         BACKUP DATABASE @databasename  TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
 END
 GO

//保存导入excel表格数据到数据库
        function saveImport() {
            //打开加载层
            var layIndex = layer.load();
            //请求保存导入数据的url
            $.post("SaveImport", function (Msg) {
                //关闭加载层
                layer.close(layIndex);
                //关闭模态框
                $("#modImportExaminee").modal("hide");
                //输出
                layer.alert(Msg, { icon: 0, title: "提示" });
                //刷新
                searchTabEmployee();
            });
        }

① 筛选表中的行
② 防止未经许可的用户访问敏感数据
③ 降低数据库的复杂程度
④ 将多个物理数据库抽象为一个逻辑数据库

　　　Create proc procedure_name  
　　　[ { @parameter data_type } [ varying ] [ = default ] [ OUTPUT ]     ] [ ,...n ]  
　　　　　　　　[ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]  [ FOR REPLICATION ]  
　　　AS 
　　　sql_statement [ ...n ]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

create    
procedures_student@ sex   
varchar（10）
as
select * from 学生信息  where 性别=@sex

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

EXEC [DatabaseName].[SchemaName].[ProcedureName]

IF(@score>60) 
    return 0 
ELSE 
    return 1 

create PROCEDURE [dbo].[spAAAForTest]
(                        
    @UserName   nvarchar(20) =null ,
    @LoginPwd nvarchar(60)  =null
)
AS
BEGIN
    select N'A' AS a
    , N'B' AS B
    , N'C' AS C
        ;

END

[(参数#1,…参数#1024)]
 　　[WITH
 　　{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
 　　]
 　　[FOR REPLICATION]

CREATE PROCEDURE CreateEmployeeTable
AS
BEGIN
    CREATE TABLE Employee (
        EmployeeID INT PRIMARY KEY,
        FirstName VARCHAR(50),
        LastName VARCHAR(50),
        Email VARCHAR(100)
    )
END

CREATE PROCEDURE|PROC <sproc name>
　　[<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,<parameter name> [schema.] <data type> [VARYING] [=<default value>] [OUT[PUT]] [READONLY]
　　[,...
　　...    
　　]]
　　[WITH 
　　RECOMPILE | ENCRYPTION | [EXECUTE AS { CALLER | SELF | OWNER | <'user name'>}]
　　AS
　　<code> | EXTERNAL NAME <assembly name>.<assembly class>.<method>

SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age > 18