"SQL Server" SQL Server programming language T-SQL stored procedures

When we first learned the database language is using some simple insert,select and so on syntax, but as we learn the depth of the database, we will find some simple grammar can not meet our requirements, such as the processing of some business logic, multi-Table Association, And even though a program or a simple SQL statement will do the same, the performance or efficiency will be low.

At this point we will use stored procedures in T-SQL, the stored procedure is like a method in C #, passing parameters, performing some operations, return the corresponding values.

We use SQLSERVER2008 's own AdventureWorks sample database to explain.

First we create a new stored procedure, the keyword is procedure, the sample code:

Procedure Proc_sales_salesreasonasbegin from     Sales.salesreason;  End     

　　We create a new Proc_sales_salesreason stored procedure that performs a query operation, and the generic stored procedure is named proc_+ name, which facilitates identification.

Execute the Stored procedure sample code:

Execute proc_sales_salesreasonprocedure  Proc_sales_salesreason    -- Delete stored procedure   

　　Modify the stored procedure by using the ALTER keyword.

The above is just a simple stored procedure, just like the method without parameters, here is the sample code with parameters:

CreateProcedureProc_sales_salesreason (@SalesReasonIDInt,@Namenvarchar50),  @ReasonType 50 @ModifiedDate datetime) asbegin insert into Sales.salesreason (Salesreasonid, Name, Reasontype, ModifiedDate) values ( @SalesReasonID,  @Name,  @ReasonType,  @ModifiedDate ); end             

　　This is a stored procedure that executes the inserted data and executes the sample code:

, 'text1 ',' as', '2011-12-12';        

　　The above is explained by some simple stored procedure usage, here just play a role, we can have time to study, to deal with some complex business logic.

I've made a small example below

　　Select: Query by Price range
Requirements: If two parameters are present, then the content of the specified range is queried;
If none exist, then query all
If the @pricefrom parameter is present, the query >[email protected]
@priceTo exists, then <[email protected]

CreateProcedureProc_selectproductswithpricerange (@PriceFromMoney,@priceToMoney)AsBeginIf@PriceFromIsNotNullBeginIf@priceToIsNotNullBeginSelect*FromDbo. Productswhere priceBetween@PriceFromand@priceTo;EndElseBeginSelect*FromDbo. Productswhere price>=@PriceFrom;EndEnd;ElseBeginIf @priceTo is not null begin select *< Span style= "color: #0000ff;" >from dbo. Products where price<=  @priceTo end else begin select *from dbo. Products end end end;   

　　To execute a stored procedure:

,null

　　

Original link: t-SQL stored procedure

"SQL Server" SQL Server programming language T-SQL stored procedures