sql 语句变量赋值

[liuquanyu]

declare @name varchar(50)
set @name='10000'
select @name
SELECT ROW_NUMBER() OVER (ORDER BY AddDate desc)AS Row, O.Title,O.Source,O.ID,O.ImageUrl,O.NodeID,
O.MemberName,O.Content,O.Comments from model_Content O where membername in(@name) and nodeid>0 and ischecked='true'

@name 为后台传过来的值，请入如何才能传递过来多个条件比如 ‘XX1’，‘xx2’ where membername in(@name)

[中国风]

DECLARE @name VARCHAR(50);
SET @name = '''XX1'',''xx2''';

EXEC('
SELECT  ROW_NUMBER() OVER ( ORDER BY AddDate DESC ) AS Row ,
        O.Title ,
        O.Source ,
        O.ID ,
        O.ImageUrl ,
        O.NodeID ,
        O.MemberName ,
        O.Content ,
        O.Comments
FROM    model_Content O
WHERE   membername IN ( '+@name+' )
        AND nodeid > 0
        AND ischecked = ''true''');

[liuquanyu]

引用 1 楼 roy_88 的回复:

DECLARE @name VARCHAR(50);
SET @name = '''XX1'',''xx2''';

EXEC('
SELECT  ROW_NUMBER() OVER ( ORDER BY AddDate DESC ) AS Row ,
        O.Title ,
        O.Source ,
        O.ID ,
        O.ImageUrl ,
        O.NodeID ,
        O.MemberName ,
        O.Content ,
        O.Comments
FROM    model_Content O
WHERE   membername IN ( '+@name+' )
        AND nodeid > 0
        AND ischecked = ''true''');

ALTER procedure [dbo].[zhu] (@pagesize int, @pageindex int, @name varchar(500)) as begin with temptbl as ( SELECT ROW_NUMBER() OVER ( ORDER BY AddDate DESC ) AS Row , O.Title , O.Source , O.ID , O.ImageUrl , O.NodeID , O.MemberName , O.Content , O.Comments FROM model_Content O WHERE membername IN ( @name ) AND nodeid > 0 AND ischecked = 'true') SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize end 原句是这样的-- ，我对存储过程语法不熟悉，这里能改吗-- 还是换种写法-- @name 还是当然做个 一个值处理了

[中国风]

ALTER PROCEDURE [dbo].[zhu]
    (
      @pagesize INT ,
      @pageindex INT ,
      @name VARCHAR(500)
    )
AS
    BEGIN
	DECLARE @Sql NVARCHAR(max)
       SET @Sql=' WITH    temptbl
                  AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY AddDate DESC ) AS Row ,
                                O.Title ,
                                O.Source ,
                                O.ID ,
                                O.ImageUrl ,
                                O.NodeID ,
                                O.MemberName ,
                                O.Content ,
                                O.Comments
                       FROM     model_Content O
                       WHERE    membername IN ( '+@name+' )
                                AND nodeid > 0
                                AND ischecked = ''true''
                     )
            SELECT  *
            FROM    temptbl
            WHERE   Row BETWEEN ( @pageindex - 1 ) * @pagesize + 1
                        AND     ( @pageindex - 1 ) * @pagesize + @pagesize;'
		EXEC sys.sp_executesql @Sql,N'@pagesize INT , @pageindex INT',@pagesize,@pageindex
    END;

[中国风]

--字符串传参时记得加引号 格式： '''XX1'',''xx2'''

[liuquanyu]

感谢版主大大，