数组作为参数，循环条件

[许晨辰]

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

求大神指点！开发中遇到的困难！

[最爱午夜]

SQL没有数组概念，可以创建一个函数去进行字符串切割，并把对应的一组数据以一行的形式插入到一个临时表。 比如： 数组1【0】对应临时表第一列，数组2【0】对应临时表第二列...... 分割函数： CREATE FUNCTION [dbo].[SUBSTRINGTEXT] ( @TEXT VARCHAR(MAX), @SPLITSYMBOL VARCHAR(100) ) RETURNS @RETURN_TAB TABLE(T_TEXT VARCHAR(MAX)) AS BEGIN DECLARE @I INT SET @I=1 DECLARE @ATEXT VARCHAR(MAX) DECLARE @BTEXT VARCHAR(MAX) DECLARE @LENGTH INT WHILE @I=1 BEGIN SET @LENGTH=CHARINDEX(@SPLITSYMBOL,@TEXT,1) IF (@LENGTH>0) BEGIN SET @ATEXT=SUBSTRING(@TEXT,1,@LENGTH-1) SET @BTEXT=SUBSTRING(@TEXT,@LENGTH+LEN(@SPLITSYMBOL),LEN(@TEXT)-@LENGTH) SET @TEXT=@BTEXT INSERT INTO @RETURN_TAB VALUES(@ATEXT) END ELSE BEGIN INSERT INTO @RETURN_TAB VALUES(@TEXT) SET @I=0 END END RETURN END

[许晨辰]

你应该没看懂我写的这段带吧