• sql 存储过程小结


    存储过程创建:

    USE [***] (注:***为表名)
    GO
    /****** Object:  StoredProcedure [dbo].[PROC_TestAll]    Script Date: 09/05/2013 13:51:32 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[PROC_TestAll]
         @PId4user uniqueidentifier
          ,@Pname4user nvarchar(max)
          ,@PSubTime4user datetime
          ,@PPassword4user nvarchar(max)
          ,@PBlood_Value4user int
          ,@PIsEnable4user bit
          ,@PAccountType_Value4user int
          ,@PId4login uniqueidentifier
          ,@PSubTime4login datetime
          ,@PAccount4login nvarchar(max)
          ,@PIsValid4login bit
          ,@PUserId4login uniqueidentifier
          ,@PIsEnable4login bit
          ,@PAccountSrc_Value4login int
          ,@PIdhome4userapp uniqueidentifier
          ,@PIdbabywhere4userapp uniqueidentifier
          ,@PIdstory4userapp uniqueidentifier
          ,@PSubTime4userapp datetime
          ,@PAppIdhome4userapp uniqueidentifier
          ,@PAppIdbabywhere4userapp uniqueidentifier
          ,@PAppIdstory4userapp uniqueidentifier
          ,@PUserId4userapp uniqueidentifier
          ,@PUserAccountStatus_Value4userapp int
    AS
    BEGIN
        INSERT INTO [User](Id,Name,Password,SubTime,Blood_Value,IsEnable,AccountType_Value)
        VALUES(@PId4user,@Pname4user,@PPassword4user,@PSubTime4user,@PBlood_Value4user,@PIsEnable4user,@PAccountType_Value4user);
        INSERT INTO [LoginAccount](Id,SubTime,Account,IsValid,UserId,IsEnable,AccountSrc_Value)
        VALUES(@PId4login,@PSubTime4login,@PAccount4login,@PIsValid4login,@PUserId4login,@PIsEnable4login,@PAccountSrc_Value4login);
        INSERT INTO [UserApp](Id,SubTime,AppId,UserId,UserAccountStatus_Value)
        VALUES(@PIdhome4userapp,@PSubTime4userapp,@PAppIdhome4userapp,@PUserId4userapp,@PUserAccountStatus_Value4userapp);
        INSERT INTO [UserApp](Id,SubTime,AppId,UserId,UserAccountStatus_Value)
        VALUES(@PIdbabywhere4userapp,@PSubTime4userapp,@PAppIdbabywhere4userapp,@PUserId4userapp,@PUserAccountStatus_Value4userapp);
        INSERT INTO [UserApp](Id,SubTime,AppId,UserId,UserAccountStatus_Value)
        VALUES(@PIdstory4userapp,@PSubTime4userapp,@PAppIdstory4userapp,@PUserId4userapp,@PUserAccountStatus_Value4userapp)
    END

     调用存储过程:

    USE [***] (注:***为表名)
    GO
    DECLARE @n int,@num int,@halfnum int,@phonenum varchar(15);
    SET @n=0;
    SET @num=100000;
    SET @halfnum=50000;
    SET @phonenum='1300000000';
    --SET @num=5000000;

    DECLARE @Id4user uniqueidentifier,@Name4user nvarchar(max),@SubTime4user datetime,@Password4user nvarchar(max),@Blood_Value4user int,@IsEnable4user bit,@AccountType_Value4user int,@Id4login uniqueidentifier,@SubTime4login datetime,@Account4login nvarchar(max),@IsValid4login bit,@UserId4login uniqueidentifier,@IsEnable4login bit,@AccountSrc_Value4login int, @Idhome4userapp uniqueidentifier,@Idbabywhere4userapp uniqueidentifier,@Idstory4userapp uniqueidentifier,@SubTime4userapp datetime,@AppIdhome4userapp uniqueidentifier,@AppIdbabywhere4userapp uniqueidentifier,@AppIdstory4userapp uniqueidentifier,@UserId4userapp uniqueidentifier,@UserAccountStatus_Value4userapp int;

    SET @Name4user='uesr_testuser'
    SET @Password4user='000000';
    SET @Blood_Value4user=0;
    SET @IsEnable4user=0;
    SET @AccountType_Value4user=0;
    SET @IsValid4login=1;
    SET @IsEnable4login =1;
    SET @AccountSrc_Value4login =0;
    SET @UserAccountStatus_Value4userapp=0;
    SET @AppIdhome4userapp='3C2B2FAA-AC24-4BD3-A570-B9239A8CCDBA';
    SET @AppIdbabywhere4userapp='17813700-EBC0-4EDB-BA43-A10C588CB617';
    SET @AppIdstory4userapp='D008F472-B404-4785-8898-AA694A7136B9';

    WHILE @n<@num
    begin
    SET @Id4user =newid();
    SET @Id4login =newid();
    SET @Idhome4userapp =newid();
    SET @Idbabywhere4userapp =newid();
    SET @Idstory4userapp =newid();
    SET @SubTime4user=getdate();
    SET @SubTime4login=@SubTime4user;
    SET @UserId4login =@Id4user;
    SET @SubTime4userapp=@SubTime4user;
    SET @UserId4userapp =@Id4user;

    IF @n>=@halfnum
    begin
    SET @AccountType_Value4user=1;
    SET @IsEnable4user=1;
    SET @IsEnable4login =0;
    SET @Account4login=newid();
    end
    else SET @Account4login=@phonenum+cast(@n as varchar(8));

    EXEC [AMP.App].[dbo].[PROC_TestAll] @PId4user=@Id4user,@Pname4user=@Name4user,@PSubTime4user=@SubTime4user,@PPassword4user=@Password4user,@PBlood_Value4user=@Blood_Value4user,@PIsEnable4user=@IsEnable4user,@PAccountType_Value4user=@AccountType_Value4user,@PId4login=@Id4login,@PSubTime4login=@SubTime4login,@PAccount4login=@Account4login,@PIsValid4login=@IsValid4login,@PUserId4login=@UserId4login,@PIsEnable4login=@IsEnable4login,@PAccountSrc_Value4login=@AccountSrc_Value4login,@PIdhome4userapp=@Idhome4userapp,@PIdbabywhere4userapp=@Idbabywhere4userapp,@PIdstory4userapp=@Idstory4userapp,@PSubTime4userapp=@SubTime4userapp,@PAppIdhome4userapp=@AppIdhome4userapp,@PAppIdbabywhere4userapp=@AppIdbabywhere4userapp,@PAppIdstory4userapp=@AppIdstory4userapp,@PUserId4userapp=@UserId4userapp,@PUserAccountStatus_Value4userapp=@UserAccountStatus_Value4userapp;
    SET @n+=1;
    end

  • 相关阅读:
    【255】◀▶IEW-Unit20
    【254】◀▶IEW-Unit19
    【253】◀▶IEW-Unit18
    【252】◀▶IEW-Unit17
    [LeetCode] Restore IP Address
    1030
    (Relax 数论1.8)POJ 1284 Primitive Roots(欧拉函数的应用: 以n为模的本原根的个数phi(n-1))
    新知识的遗忘数度真是可怕
    【自由谈】城域网IPv6过渡技术——4v6场景技术总结(1)
    Graph Databases—The NOSQL Phenomenon阅读笔记
  • 原文地址:https://www.cnblogs.com/log-long/p/3303262.html
Copyright © 2020-2023  润新知