sql 查询传参数的问题

Question

select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=3 and PaperDegree ='3' and KnowledgeTypeId in(2,3,4)union all
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=4 and PaperDegree ='4' and KnowledgeTypeId in(2,3,4)union all
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=5 and PaperDegree ='5' 

我的查询语句是这样的， 我写的是一个存储过程
我传值进去的时候，例如 @QuestionTypeId varchar(50)，它的值是'3,4,5'，(也有可能是 '4,5,6,7 ')，也就是QuestionTypeId 的条件字段，根据 ','来分割，在存储过程里面应该怎么分割呢？我希望做到的是 如果传递的值是 '3,4' 那么 查询语句就是

select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=3 and PaperDegree ='3' and KnowledgeTypeId in(2,3,4)union all
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=4 and PaperDegree ='4' and KnowledgeTypeId in(2,3,4)


如果传递的值是'3,4,5'，那么查询语句就是
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=3 and PaperDegree ='3' and KnowledgeTypeId in(2,3,4)union all
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=4 and PaperDegree ='4' and KnowledgeTypeId in(2,3,4)union all
select top(10) PaperId,CreationTime,QuestionTypeId from TB_Paper where QuestionTypeId=5 and PaperDegree ='5' 

传递值具体是不确定的，有可能是 '3,4'，也有可能是'3,4,5,6'，也有可能是'3,4,5,6,7'

这样该怎么写呢？

Answer 1

如果在存储过程中使用,可以对传入的参数使用charindex和substring函数来配合分割参数,再执行随后的内容

Answer 2

把参数分割 用while语句  看下 sp_executesql   的用法

Answer 3

我觉得是不是该在调用存储过程之前就把参数确定下来呢？首先程序处理字符串应该比较简单而且比让sql去处理效率来的要高吧