A stored procedure is built in SQL Server, and if the required parameter is a mutable set how to handle it?

in SQL Server built-in stored procedures, If a parameter is required to handle a mutable collection

The original stored procedure, @objectIds as a variable parameter, such as 110,98,99

ALTER PROC [dbo]. [Proc_totalscore]

@categoryKey int,

@objectIds VARCHAR (MAX)

As

BEGIN

SELECT C.name,avg (E.score) score from dbo. Sys_com_comment_main m

INNER JOIN dbo.sys_com_coment_extend E on e.commentid=m.id

INNER JOIN dbo.sys_com_category C on E.namekey=c.namekey

WHERE [email protected] and m.datastatus<>99 and M.ischeck=0 and M.objectid in (@objectIds)

GROUP by C.name

END

Call 1 :EXEC [Proc_totalscore] 99902, ' a '

Call 2 :EXEC [Proc_totalscore] 99902, ' 110,97,87 '

A stored procedure is built in SQL Server, and if the required parameter is a mutable set how to handle it?

Search the Internet for a while most are soy sauce paste, there are netizens said in the way of XML, the specific code did not see

compromise, with SQL stitching to achieve the effect of variable parameters  

Let's start with an example of SQL stitching

DECLARE @sql NVARCHAR (MAX)

SET @sql = ' Select 1 MMD '

EXECUTE (@sql)

A compromise stored procedure

ALTER PROC [dbo]. [Proc_totalscore]

@categoryKey VARCHAR (100),

@objectIds VARCHAR (MAX)

As

BEGIN

DECLARE @sql NVARCHAR (MAX)

SET @sql = ' SELECT c.name,avg (e.score) score from dbo. Sys_com_comment_main m

INNER JOIN dbo.sys_com_coment_extend E on e.commentid=m.id

INNER JOIN dbo.sys_com_category C on E.namekey=c.namekey

WHERE m.categorykey= ' [email protected]+ ' and m.datastatus<>99 and M.ischeck=0 and M.objectid in (' [email protected] + ')

GROUP by C.name '

EXECUTE (@sql)

END

Final effect

A stored procedure is built in SQL Server, and if the required parameter is a mutable set how to handle it?