下面这个写法怎么改正

[majianbing]

exec sp_executesql N'SELECT * FROM vod_news WHERE @S ', N'@S VARCHAR(50)', @S = ' id in (1,2,3,4)'

这个是用.NET里面的SqlParameter传递处理之后，在探查器里得到的结果。

string where = string.Format(" nws_id in ({0})", idList);
string sql = string.Intern("SELECT * FROM vod_news WHERE @S");
SqlParameter[] paras = new SqlParameter[1];
SqlParameter where_para = new SqlParameter("@S", SqlDbType.VarChar);
where_para.Value = where;
paras[0] = where_para;

[叶子]

运行的错误提示是什么？

[Shawn]

declare @S varchar(255)
set @S = ' id in (1,2,3,4)'
declare @sql varchar(8000)
set @sql = 'SELECT * FROM vod_news WHERE ' + @S

[Shawn]

EXEC(@sql)
--sp_executesql的原理不是拼成字符串后再执行，所以会报错

[dawugui]

[Quote=引用楼主 majianbing 的回复:]
exec sp_executesql N'SELECT * FROM vod_news WHERE @S ', N'@S VARCHAR(50)', @S = ' id in (1,2,3,4)'

这个是用.NET里面的SqlParameter传递处理之后，在探查器里得到的结果。

string where = string.Format(" nws_id in ({0})", idL……
[/Quote]
你组合得不对.
declare @s varchar(50)
set @s = '(1,2,3,4)'
exec('SELECT * FROM vod_news WHERE 字段 in '+ @s)

[majianbing]

可能大家都还没有明白我的这个意思。我把完整代码贴出来。
public NewsList GetNewsByIdList(string idList)
{
string where = string.Format(" nws_id in ({0})", idList);
string sql = string.Intern("SELECT * FROM vod_news WHERE @S");
SqlParameter[] paras = new SqlParameter[1];
SqlParameter where_para = new SqlParameter("@S", SqlDbType.VarChar);
where_para.Value = where;
paras[0] = where_para;

NewsList list = new NewsList();
News news = null;
SqlDataReader reader = null;
try
{
reader = DBHelper.ExecuteReader(sql.ToString(),paras);
while (reader.Read())
{
news = new News(reader);
list.Add(news);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (reader != null && !reader.IsClosed) reader.Close();
}
return list;
}
运行的时候提示说 “@s”附近有语法错误

[Shawn]

先拼好字符串，再执行。ExecuteReader和sq_executesql一样，不是把SQL先拼成字符串，再执行的。

sql = "EXEC('SELECT * FROM vod_news WHERE id in (1,2,3,4)')"
reader = DBHelper.ExecuteReader(sql.ToString(), paras);