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