• 存储过程传入datatable


    存储过程传入一般的参数都很简单,今天要说一下存储过程传入datatable 类型

     首先要自定义一个 表类型

    CREATE TYPE [dbo].[servicedatableType] AS TABLE
        (
        category int  NULL,
        class int  NULL,
        packname nvarchar(1000)  NULL,
        packid int  NULL ,
        serviceid int  null,
        servicename nvarchar(500)  null,
        serviceprice decimal(18,2) null,
        servicecomment  nvarchar(4000)  null,
        servicecategory int  null,
        sstate int null,
        iscarray int null
        )
    
    GO

    我们自定义了表类型以后 在存储过程中就可以 直接用了

    @servicollection servicedatableType readonly

    这里我们定义了一个 表结构的字段, 在 存储过程调用的时候直接传入 datatable 就行了。

    new SqlParameter("@servicollection",dt)

    这里再 介绍一个我自己写的例子, 需求是将传入的 databke 遍历  验证是否存在, 不存在则执行写入,此处遍历datable时 用了 游标

    create procedure  pr_InsertPackinfoandService(
    @category int,
    @packid int,
    @class int,
    @packname nvarchar(500),
    @price decimal(18,2),
    @comment nvarchar(4000),
    @status int,
    @chargestatus int,
    @conecssionprice decimal(18,2),
    @renewprice decimal(18,2),
    @statrtime datetime,
    @endtime datetime,
    @renewyers int,
    @renewtimes int,
    @buytimes int,
    @iscarry int,
    @servicollection servicedatableType readonly
    )
    as
    declare @isCount int
    declare @pspackedid int
    declare @pscategory int
    declare @psclass int
    declare @pkname nvarchar(100)
    declare @serviceid nvarchar(100)
    declare @servicename nvarchar(300)
    declare @pscomment nvarchar(200)
    declare @servicecate int
    declare @serviceprice decimal(18,2)
    declare @psstatus int
    declare @psiscarry int 
    begin
    
     set @isCount=(select COUNT(*) from t_packages_info where pi_category=@category and pi_class=@class  and pi_packageid=@packid  )
     if(@isCount=0) --判断套餐 是否存在
     begin
     --执行添加操作
     insert into t_packages_info (pi_category,pi_class,pi_packageid,pi_packname,pi_price,pi_comment,pi_status,pi_chargestatus,pi_ConcessionalPrice,pi_RenewPrice,pi_AvailableEndTime,pi_AvailableStartTime,pi_RenewYears,pi_RenewTimes,pi_BuyTimes,pi_IsCarray)
     values(@category,@class,@packid,@packname,@price,@comment,@status,@chargestatus,@conecssionprice,@renewprice,@endtime,@statrtime,@renewyers,@renewtimes,@buytimes,@iscarry)
     
     --执行添加服务
     
     declare cur_serList cursor scroll For 
        select category, class,packname,packid,servicename,serviceprice,servicecomment,servicecategory,sstate,iscarray from @servicollection
        fetch first from cur_serList into  @pscategory,@psclass,@pkname ,@serviceid,@servicename,@serviceprice,@pscomment,@servicecate,@psstatus,@psiscarry
       While @@FETCH_STATUS=0 
       if((select COUNT(*) from t_package_service where ps_serviceid=@serviceid and pi_class=@psclass and pi_category=@pscategory )=0)
       begin
       --执行添加操作
        insert into t_package_service(pi_category,pi_packageid,pi_class,pi_packname,ps_serviceid,ps_servicename,ps_serviceprice,ps_comment,ps_ServiceCategory,ps_State,ps_IsCarray)
        values(@pscategory,@pspackedid, @psclass,@pkname,@serviceid,@servicename,@serviceprice,@pscomment,@servicecate,@psstatus,@psiscarry)
       end
       
       fetch next from cur_serList into @pscategory,@psclass,@pkname ,@serviceid,@servicename,@serviceprice,@pscomment,@servicecate,@psstatus,@psiscarry
     end
    end
    Close cur_serList;----关闭游标
    deallocate cur_serList ------删除游标
  • 相关阅读:
    读书笔记:Visual Studio DSL工具特定领域开发指南
    OpenTest:教你在自动化脚本中增加选择文件的支持
    MetaModelEngine:域模型定义
    WPF:从WPF Diagram Designer Part 2学习面板、缩略图、框线选择和工具箱
    2010年8月blog汇总:敏捷个人和OpenExpressApp之建模支持
    2010年7月blog汇总:OpenTest、MetaModelEngine和敏捷个人
    101与金根回顾敏捷个人:(69)《幸运的秘密》
    MetaModelEngine:模型存储的概要说明
    故事:用户凭什么跟你走
    MDSF:软件工厂(Software factory)介绍
  • 原文地址:https://www.cnblogs.com/zplvpp520/p/6978254.html
Copyright © 2020-2023  润新知