Simple use of stored procedures, simple use of stored procedures

Simple use of stored procedures, simple use of stored procedures

Create a stored procedure:

USE [ais20140425092531]
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) -- add query Condition Parameters
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 a temporary table, store the film, and name of the glue supplier
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 ais2014042692652.dbo. t_Supplier ts
)

Insert into # temp
(
-- ID -- this column value is auto-generated
Name,
FType
)
VALUES
(
'Hebei smart ',
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

--- Execute the Add Condition content

Exec [HBSH_MS_BillType_in_ofmine] ''-- directly add the filtering conditions in single quotes.

How to use SQL stored procedures

A. Simple process

The following stored procedure returns the names of all employees (with names and surnames), positions, and departments from the view. This stored procedure does not use any parameters.
Copy

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

The uspGetEmployees stored procedure can be executed in the following ways:
Copy

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

B. Simple process with Parameters

The following stored procedure only returns the specified employee (provide name and surname), its title and department name from the view. This stored procedure accepts values that exactly match the passed parameters.
Copy

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

The uspGetEmployees stored procedure can be executed in the following ways:
Copy

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'; ...... the remaining full text >>>

How to Write a simple stored procedure?

Yes
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
You can pass different values of @ Action. This is just an idea. You can consider it yourself.