SQL带参数的存储过程

/*

         题目：查询指定的用户在指定的时间段内的下单信息，

         如果结束日期没有指的话，那么查询的是到今天为止的下单信息

*/

USE E_Market

GO

IF EXISTS(SELECT * FROM sysobjects WHERE name='usp_GetOrderInfo')

DROP PROC usp_GetOrderInfo

GO

CREATE PROC usp_GetOrderInfo

         @startDate datetime, 

         @endDate datetime=null,

         @userId varchar(20)=null 

AS

         IF  @endDate IS NULL

                   BEGIN

                            SET @endDate=GETDATE()

                   END

IF @userId IS NULL

                   BEGIN

                            SELECT C.CommodityName AS 商品名称,S.SortName AS 类别名称,

                            O.UserId AS 用户名,O.OrderTime AS 下单时间 FROM OrderInfo AS O

                            INNER JOIN CommodityInfo AS C ON O.CommodityId=C.CommodityId

                            INNER JOIN CommoditySort AS S ON C.SortId=S.SortId

                            WHERE O.OrderTime BETWEEN @startDate AND @endDate

                   END

         ELSE   --查询指定用户指定时间段内的所有订单信息

                   BEGIN

                            SELECT C.CommodityName AS 商品名称,S.SortName AS 类别名称,

                            O.UserId AS 用户名,O.OrderTime AS 下单时间 FROM OrderInfo AS O

                            INNER JOIN CommodityInfo AS C ON O.CommodityId=C.CommodityId

                            INNER JOIN CommoditySort AS S ON S.SortId=C.SortId

                            WHERE (O.OrderTime BETWEEN @startDate AND @endDate)

                            AND O.UserId=@userId

                   END

GO  --存储过程的结束