簡單的預存程序的使用，簡單預存程序使用

簡單的預存程序的使用，簡單預存程序使用

建立預存程序：

USE [AIS20140417092531]
GO
/****** Object:  StoredProcedure [dbo].[HBSH_MS_BillType_in_ofmine]    Script Date: 09/01/2014 14:34:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[HBSH_MS_BillType_in_ofmine] 
-- Add the parameters for the stored procedure here
@type varchar(20) --添加查詢條件參數
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


    -- Insert statements for procedure here
    --建立暫存資料表，存放膜，膠供應商名稱
    CREATE TABLE #temp(
   
    ID INT IDENTITY(1,1),
    Name VARCHAR(50),
    FType INT 
    
    )
    
    INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    NAME,
    FType
   
   
    )
    
    (
    SELECT ts.FName,200
     FROM t_Supplier ts
    )
    
    INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    NAME,FType
    )
    (
    SELECT ts.FName,200 FROM AIS20140417092652.dbo.t_Supplier ts
    )
    
    INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    Name,
    FType
    )
    VALUES
    (
    '河北智能',
    200
    )
    
    INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    Name,
    FType
    )
    
    (
SELECT to1.FName,201 FROM t_Organization to1
    )

INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    Name,
    FType
    )
    
    (
SELECT to1.FName,201 FROM AIS20140417092652.dbo.t_Organization to1
    )
    
    INSERT INTO #temp
    (
    -- ID -- this column value is auto-generated
    Name,
    FType
    )
    
    (
SELECT hmc.fname,hmc.ftype FROM AcctCommerce.dbo.HBSH_MS_CustomName hmc
    )

SELECT t.Name,t.FType FROM #temp t  where t.ftype=@type GROUP BY t.Name,t.FType
 
END

---執行添加條件內容

exec [HBSH_MS_BillType_in_ofmine]    '  '                        --單引號當中直接添加篩選的條件內容

SQL的預存程序怎使用

A. 使用簡單過程

以下預存程序將從視圖中返回所有僱員（提供姓和名）、職務以及部門名稱。此預存程序不使用任何參數。
複製

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
GO

uspGetEmployees 預存程序可通過以下方式執行：
複製

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. 使用帶有參數的簡單過程

下面的預存程序只從視圖中返回指定的僱員（提供名和姓）及其職務和部門名稱。此預存程序接受與傳遞的參數精確匹配的值。
複製

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS

SET NOCOUNT ON;
SELECT FirstName, LastName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
WHERE FirstName = @FirstName AND LastName = @LastName;
GO

uspGetEmployees 預存程序可通過以下方式執行：
複製

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';......餘下全文>>
 
預存程序怎寫簡單？

可以的
CREATE procedure InSertUser
@Username varchar(50),
@UserPwd varchar(50),
@UserDate datetime=getdate,
@Action varchar(50)
as
if(@Action='Create')
BEGIN
INSERT INTO Users
(UserName, UserPwd, UserDate)
VALUES
(
@Username,
@UserPwd,
@UserDate
)
END
else if(@Action='Update')
BEGIN
update xxx set aaa=''
END

GO
通過傳遞@Action的不同值就可以了 這隻是個思路 具體實現你可以自己考慮考慮