有个sql拼接的问题，搞了几天了，大家帮帮忙

[cwaspnet]

问题是这样的
select * from tablename where colflag='flag' classid in ('classid_1','classid_2','classid_3') and coltime>'2009-12-18' order by coltime desc

现在要拼接一个类似上面这样的sql语句 写在一个存储过程中

CREATE PROCEDURE  proc_table
	@Flag varchar(255),
	@ClassIDs varchar(2500),
	@Days int=0
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Sql varchar(3000)
	if @Days=0
		begin
begin
			SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = ''' + @Flag + ''' AND ClassID in (' + @ClassIDs + ') ORDER BY coltime DESC'
		end
	else
                  begin 
                           declare @day datetime
			select @day = DATEADD(dd,-@Days,CONVERT(datetime,CONVERT(char(10),getdate(),120),20))
                           SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = ''' + @Flag + ''' AND ClassID in (' + @ClassIDs + ') and coltime>'''+@day+''' ORDER BY coltime DESC'
                  end
         Print @sql
	Exec(@sql)
end

这个sql语句的拼接有问题 会报个从字符串转换日期和/或时间时，转换失败

想过不拼接sql字符串 直接执行查询 将else里面的set @sql改为一个select语句 又出现了另外一个问题
这里的
@classids='''classid_1'',''classid_2'',''classid_3'''
select * from tablename where colflag=@flag and classid in (@classids) and coltime>@day order by coltime desc

这个查询在数据库中查不出来值 问题出在@classids这 我把@classids打印出来看了 值为 'classid_1','classid_2','classid_3' 没有什么问题 但是他当变量传进去查询的时候，把'classid_1','classid_2','classid_3'当成了一个字符串

麻烦高手帮忙解决下

[tangtang89522]

declare @classid varchar(20),@dates varchar(10)
set @fund='classid_1,classid_2,classid_3'
set @dates='2000-02-12'
select * 
from tablename 
where colflag='flag' and charindex(classid,@classid)>0 and coltime>@dates 
order by coltime desc 

这样应该 可以满足。。 可以直接写 sql 语句。。

[tangtang89522]

declare @classid varchar(20),@dates varchar(10)
set @fund='classid_1,classid_2,classid_3'
set @dates='2000-02-12'
select * 
from tablename 
where colflag='flag' and charindex(classid,@classid)>0 and coltime>@dates 
order by coltime desc 

[zjj1028]

我也想知道,嘻..,给点分吧

[htl258_Tony]

CREATE PROCEDURE  proc_table
    @Flag varchar(255),
    @ClassIDs varchar(2500),
    @Days int=0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql varchar(3000)
    if @Days=0
        begin
begin
            SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = ''' + @Flag + ''' AND ClassID in (' + @ClassIDs + ') ORDER BY coltime DESC'
        end
    else
                  begin 
                           declare @day datetime
            select @day = DATEADD(dd,-@Days,CONVERT(datetime,CONVERT(char(10),getdate(),120),20))
                           SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = ''' + @Flag + ''' AND ClassID in (' + @ClassIDs + ') and coltime>'''+convert(varchar,@day,23)+''' ORDER BY coltime DESC'
                  end
         Print @sql
    Exec(@sql)
end

[lzf616]

AND ClassID in (' + @ClassIDs + ') 能修改成这样不?

 "AND ClassID in (" + @ClassIDs +")"  

希望能帮到你

[vipper23]

alter PROCEDURE  proc_table
    @Flag varchar(255),
    @ClassIDs varchar(2500),
    @Days int=0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql varchar(3000)
    if @Days=0
        begin
 
            SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = ''' + @Flag + ''' AND ClassID in (' + ''''+@ClassIDs +''''+ ') ORDER BY coltime DESC'
        end
    else
                  begin 
                           declare @day varchar(100)
            select @day = convert(varchar(100),DATEADD(dd,-@Days,CONVERT(datetime,CONVERT(char(10),getdate(),120),20)),120)
            
                           SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = '+'''' + @Flag + ''''+' AND ClassID in (' +''''+ @ClassIDs + ''''+ ') '+' and coltime>' + ''''+@day +''''+' orDER BY coltime DESC'
                  end
         Print @sql
    Exec(@sql)
end

[cwaspnet]

[Quote=引用 1 楼 lzf616 的回复:]
AND ClassID in (' + @ClassIDs + ')  能修改成这样不?

SQL code
"AND ClassIDin ("+@ClassIDs+")"

希望能帮到你
[/Quote]
这个貌似改不了 如果是把这句话改成字符串加进去的话 在selet里面加个字符串进去 语法通不过的

[cwaspnet]

SET @Sql = 'SELECT * FROM tablename WHERE [Flag] = '+'''' + @Flag + ''''+' AND ClassID in (' +''''+ @ClassIDs + ''''+ ') '+' and coltime>' + ''''+@day +''''+' orDER BY coltime DESC'
这样也不行 从字符串转换日期和/或时间时，转换失败。还是@day这个问题
这个数据库是2008的 可以再2008的引擎上试下

[cwaspnet]

大家帮帮忙撒 还有人有其他办法没