SQL Server Stored Procedures

Long time no use of stored procedures, suddenly a use to find a lot of things to forget, wrote two. Easy access. Examples are very simple, convenient to view the structure, the use of parameters.

SELECT  * fromproduction.product;--1 Creating a stored procedure that returns a result setDROP PROCEDUREdbo. pr_product;CREATE PROCEDUREpr_product as     BEGIN         SELECT  *         fromproduction.product; END; --Executing stored procedures    EXECdbo. pr_product;--2 Creating a stored procedure that requires the input of a parameter    CREATE PROCEDUREPr_selectproduct@color VARCHAR( -)--define an input parameter     as         BEGIN            SELECT  *             fromproduction.productWHEREColor= @color;--requires the same color column as the input parameter        END; --Executing stored procedures        EXECDbo. Pr_selectproduct@color = 'Black' --varchar (+)--3 Creating a stored procedure that requires the input of two parameters        CREATE PROCEDUREpr_selectproducttwoparameters@safetyStockLevel INT ,            @recorderPoint INT         as             BEGIN                SELECT  *                 fromproduction.productWHERESafetystocklevel= @safetyStockLevel                         andReorderPoint> @recorderPoint            END; --Executing stored procedures            EXECDbo. Pr_selectproducttwoparameters@safetyStockLevel =  +,--int                @recorderPoint =  - --int                        EXECDbo. Pr_selectproducttwoparameters@safetyStockLevel =  -,--int                @recorderPoint =  - --int            --4 Creating a stored procedure with a return value            CREATE PROCEDUREPr_getproductid@makeFlag INT ,                @color VARCHAR(Ten) ,                @productID INTOUTPUT as                 BEGIN                    SELECT  @productID =ProductID fromproduction.productWHEREMakeFlag= @makeFlag                             andColor= @color                END; --Executing stored procedures                DECLARE @productID INT                EXECDbo. Pr_getproductid@makeFlag = 0,@color = 'Black',--varchar (TEN)                    @productID = @productIDOUTPUT--int--declares a variable to receive the return value after the stored procedure is executed                SELECT  @productID  asProductID;--5 Creating a stored procedure with default input parameters                CREATE PROCEDUREPr_selectproductname@name VARCHAR(Ten)= '%'                 as                     BEGIN                        SELECT  *                         fromproduction.productWHEREName like @name; END; --Executing stored procedures                    EXECDbo. Pr_selectproductname;--Default Parameters                    EXECDbo. Pr_selectproductname@name = 'B%' --varchar (10) manual input parameters

SQL Server Stored Procedures