简单的存储过程的使用，简单存储过程使用
创建存储过程：
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] ' ' --单引号当中直接添加筛选的条件内容
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的不同值就可以了 这只是个思路 具体实现你可以自己考虑考虑