数组作为参数,循环条件
许晨辰 2013-06-24 02:58:26 EXEC GetGrants
@pKeyWord = NULL
,@pFundtype = null
,@pStartDate = NULL
,@pEndDate = NULL
,@pStartAmount = NULL
,@pEndAmount = NULL
,@pInstitutiontType = '1,2,3'
,@pOrderBy = 'StartDate'
,@pIsASC = 0
,@pPageSize= 20
,@pPageIndex = 1
,@oTotalRows=NULL
ALTER PROC [dbo].[GetGrants]
@pKeyWord varchar(200) = NULL -- Search the parameter in column ProjectTitle, Institution, participants, PrincipalInvestigator, ProjectID
,@pFundtype varchar(200) = NULL -- Search the parameter in column Fundtype
,@pStartDate varchar(200) = NULL -- Search the parameter in column StartDate
,@pEndDate varchar(200) = NULL -- Search the parameter in column StartDate
,@pStartAmount float = NULL -- Search the parameter in column Amount
,@pEndAmount float = NULL -- Search the parameter in column Amount
,@pInstitutiontType varchar(200) = NULL -- Search the parameter in column InstitutiontType
,@pOrderBy varchar(200) = NULL-- StartDate OR Amount. The default value is StartDate
,@pIsASC BIT = NULL -- order by a column asc OR desc. The default value is 0 (desc).
,@pPageSize int = 20
,@pPageIndex int = 1
,@oTotalRows INT = NULL OUTPUT
AS
SET NOCOUNT ON ---必须加上,否则页面会报错的
BEGIN
SELECT @pKeyWord = RTRIM(LTRIM(@pKeyWord))
,@pFundtype = RTRIM(LTRIM(@pFundtype))
,@pInstitutiontType = RTRIM(LTRIM(@pInstitutiontType))
DECLARE @SQL NVARCHAR(MAX)
SET @Sql=N'
SELECT Grantid,ROW_NUMBER() OVER(ORDER BY '
+CASE
WHEN @pOrderBy='StartDate' THEN 'StartDate '
ELSE 'Amount '
END
+CASE
WHEN @pIsASC=1 THEN 'ASC'
ELSE 'DESC'
END
+ ') AS RowNumber
INTO #IdGrantsList
FROM dbo.Grants
WHERE 1 = 1'
IF @pKeyWord IS NOT NULL AND LEN(@pKeyWord) = DATALENGTH(@pKeyWord)
SET @SQL=@SQL+'
AND (
ProjectTitle like ''%'' + @KeyWord + ''%'' OR ProjectTitleEN like ''%''+@KeyWord+''%''
OR Institution like ''%'' + @KeyWord + ''%''
OR participants like ''%'' + @KeyWord + ''%''
OR PrincipalInvestigator like ''%'' + @KeyWord + ''%''
OR ProjectID like ''%'' + @KeyWord + ''%''
)'
ELSE IF @pKeyWord IS NOT NULL AND LEN(@pKeyWord) < DATALENGTH(@pKeyWord)
SET @SQL=@SQL+'
AND (
ProjectTitle like ''%'' + @KeyWord + ''%''
OR Institution like ''%'' + @KeyWord + ''%''
OR participants like ''%'' + @KeyWord + ''%''
OR PrincipalInvestigator like ''%'' + @KeyWord + ''%''
OR ProjectID like ''%'' + @KeyWord + ''%''
)'
IF @pFundtype IS NOT NULL AND @pFundtype<>'others' AND LEN(@pFundtype) = DATALENGTH(@pFundtype)
SET @SQL=@SQL+'
AND (fundType like ''%'' + @Fundtype + ''%'' OR FundTypeen like ''%'' + @Fundtype + ''%'')'
IF @pFundtype IS NOT NULL AND @pFundtype<>'Others' AND LEN(@pFundtype) < DATALENGTH(@pFundtype)
SET @SQL=@SQL+'
AND (fundType like ''%'' + @Fundtype + ''%'')'
IF @pFundtype IS NOT NULL AND @pFundtype='others'
SET @SQL=@SQL+'fundtype not in (select distinct fundtype from fundtype)'
IF @pStartDate IS NOT NULL
SET @SQL=@SQL+'
AND StartDate >= @StartDate'
IF @pEndDate IS NOT NULL
SET @SQL=@SQL+'
AND StartDate <= @EndDate'
IF @pStartAmount IS NOT NULL
SET @SQL=@SQL+'
AND Amount >= @StartAmount'
IF @pEndAmount IS NOT NULL
SET @SQL=@SQL+'
AND Amount <= @EndAmount'
IF @pInstitutiontType IS NOT NULL
SET @SQL=@SQL+ //这个地方最终的条件应该是循环成这样的(SUBSTRING(tag,1,1)=1) or SUBSTRING(tag,2,1)=1) or SUBSTRING(tag,3,1)=1))
SET @SQL = @SQL + '
SET @TotalRows = @@ROWCOUNT
CREATE CLUSTERED INDEX #IX_IdGrantsList ON #IdGrantsList (RowNumber)
SELECT b.GrantID,b.FundTypeen,b.ProjectTitleen,b.PrincipalInvestigatorEN,b.ProjectTitle,b.PrincipalInvestigator,b.Institution,b.Institutionen,b.FundType,b.StartDate,b.EndDate,b.amount,b.PaperCount
FROM #IdGrantsList a
JOIN Grants b ON b.grantid = a.grantid
WHERE a.RowNumber BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize
ORDER BY a.RowNumber
'
PRINT @SQL
EXEC sp_executesql
@SQL
, N'@KeyWord varchar(200) = NULL -- Search the parameter in column ProjectTitle, Institution, participants, PrincipalInvestigator, ProjectID
,@Fundtype varchar(200) = NULL -- Search the parameter in column Fundtype
,@StartDate varchar(200) = NULL -- Search the parameter in column StartDate
,@EndDate varchar(200) = NULL -- Search the parameter in column StartDate
,@StartAmount float = NULL -- Search the parameter in column Amount
,@EndAmount float = NULL -- Search the parameter in column Amount
,@InstitutiontType varchar(200) = NULL -- Search the parameter in column InstitutiontType
,@OrderBy varchar(200) = NULL-- StartDate OR Amount. The default value is StartDate
,@IsASC BIT = NULL -- order by a column asc OR desc. The default value is 0 (desc).
,@PageSize int = 20
,@PageIndex int = 1
,@TotalRows INT OUTPUT'
,@KeyWord = @pKeyWord
,@Fundtype = @pFundtype
,@StartDate = @pStartDate
,@EndDate = @pEndDate
,@StartAmount = @pStartAmount
,@EndAmount = @pEndAmount
,@InstitutiontType = @pInstitutiontType
,@OrderBy = @pOrderBy
,@IsASC = @pIsASC
,@PageSize = @pPageSize
,@PageIndex = @pPageIndex
,@TotalRows = @oTotalRows OUTPUT
END
求大神指点!开发中遇到的困难!