存储过程的使用

数据库创建存储过程：

ALTER PROCEDURE [dbo].[Proc_GetSysNews]
@StartDate datetime=null,
@NewsType int=1,
@Obtained VARCHAR,
@TopNumber int=5
AS
 BEGIN

SET NOCOUNT ON;

IF @StartDate IS NULL SET @StartDate=getdate()

select top (@TopNumber) NewsId,Title,ImgUrl--,NewsType, Author, DeptId, ImgUrl, CreateDate,
        --CreateUser, UpdateDate, UpdateUser, ReleaseDate, DeadlineDate, TopReleaseDate, TopDeadlineDate,
        --Obtained, Sort, Summary, TopFlag, CreateOrgName --,Content
        ,(CASE WHEN
        TopFlag =1 AND
CONVERT(varchar(100), GETDATE(), 23) BETWEEN TopReleaseDate and TopDeadlineDate
THEN
1
ELSE
0 END) inTopFlag
from SYS_News
WHERE
ReleaseDate<=@StartDate
        AND DeadlineDate>=@StartDate
        AND NewsType = @NewsType
        AND Obtained = @Obtained
        AND DeleteFlag = 0
ORDER BY Obtained,
        inTopFlag DESC,
        Sort DESC,
        ReleaseDate DESC,
        NewsId
END

<!--xml文件调用存储过程-->
<select id="getSysInfo" statementType="CALLABLE" resultType="com.pacific.rsp.model.po.SysInfo" >

 <![CDATA[
  {call Proc_GetSysNews
  (
     #{SystemDate, mode=IN, jdbcType=DATE}
    ,#{newsType, mode=IN, jdbcType=INTEGER}
    ,#{obtained, mode=IN, jdbcType=VARCHAR}
    ,#{topNumber, mode=IN, jdbcType=INTEGER}
  )}
]]>

</select>
数据库中执行存储过程：
EXEC Proc_GetSysNews @NewsType =1,@Obtained='1',@TopNumber=5,@StartDate='2019-01-01'    如果指定 字段名称，则字段顺序可以改变。

EXEC Proc_GetSysNews '2019-01-01',1,'1',1    如果不指定字段名称，则顺序需要与声明的顺序保持一致，否则执行会出错。