先贴代码:
Create Proc Proc1
@WhereStr NVarchar(200),
@RecordCount Int Output
As
Declare @TSql NVarchar(1000)
Select @Tsql=N'Select @RecordCount=Count(Id) From Table1 ' + @WhereStr
Exec Sp_ExecuteSql @TSql,N'@RecordCount Int Output',@RecordCount Output --到这里出错了,提示2008附近有语法错误
注:
@WhereStr值为' addtime>=''2008-12-15 00:00:00'' and d.addtime<=''2008-12-15 23:59:59'' '查询分析器直接运行:Exec Proc1 @WhereStr,0 确定无误
在程序段调用proc1出错
Create Proc Proc1
@WhereStr NVarchar(200),
@RecordCount Int Output
As
Declare @TSql NVarchar(1000)
Select @Tsql=N'Select @RecordCount=Count(Id) From Table1 ' + @WhereStr
Exec Sp_ExecuteSql @TSql,N'@RecordCount Int Output',@RecordCount Output --到这里出错了,提示2008附近有语法错误
注:
@WhereStr值为' addtime>=''2008-12-15 00:00:00'' and d.addtime<=''2008-12-15 23:59:59'' '查询分析器直接运行:Exec Proc1 @WhereStr,0 确定无误
在程序段调用proc1出错
@WhereStr值为' addtime>=''2008-12-15 00:00:00'' and addtime <=''2008-12-15 23:59:59'' '
变量监视的值放到查询分析器无误
出错提示:System.Data.SqlClient.SqlException: '2008' 附近有语法错误。da.Fill(dt);
Declare @TSql NVarchar(1000)
Select @Tsql=N'Select @RecordCount=Count(Id) From Table1 ' + @WhereStr
Exec Sp_ExecuteSql @TSql,N'@RecordCount Int Output',@RecordCount Output --到这里出错了,提示2008附近有语法错误
注:
@WhereStr值为' addtime>=''2008-12-15 00:00:00'' and d.addtime <=''2008-12-15 23:59:59'' '
------------
改成这个试试
Declare @TSql NVarchar(1000)
set @Tsql=N'Select @RecordCount=Count(Id) From Table1 @WhereStr '
Exec Sp_ExecuteSql @TSql,N'@WhereStr nvarchar(1000)'@WhereStr --到这里出错了,提示2008附近有语法错误
注:
@WhereStr值为' addtime>=''2008-12-15 00:00:00'' and d.addtime <=''2008-12-15 23:59:59'' '
---------
--如果你数据库里存的是Datetime类型
--改成这样试
addtime>=cast('2008-12-15 00:00:00' as datetime)' and d.addtime <= cast('2008-12-15 23:59:59' as datetime)
--写错了
addtime>=cast('2008-12-15 00:00:00' as datetime) and d.addtime <= cast('2008-12-15 23:59:59' as datetime)
' addtime>=cast(''2008-12-15 00:00:00'' as datetime) and addtime <= cast(''2008-12-15 23:59:59'' as datetime) '
和
' addtime btween ''2008-12-15 00:00:00'' as datetime and ''2008-12-15 23:59:59'' '
我都试过了,查询分析器还是可以正确执行,程序调用的时候就是会出错
addtime>=cast(''2008-12-15 00:00:00'' as datetime) and addtime <= cast(''2008-12-15 23:59:59'' as datetime)
''2008-12-15 00:00:00'' 和''2008-12-15 23:59:59'' 只能单引号吧
改成一个单引号看看
@RecordCount Int Output
楼主一定要通过这种方式来取得输入参数吗?
这面这种方式应该也能够实现楼主的功能吧,在内存表中直接读返回值
Create Proc Proc1
@WhereStr NVarchar(200)
As
Declare @TSql NVarchar(1000)
set @Tsql=N'Select @RecordCount=Count(Id) From Table1 ' + @WhereStr
Exec(@sql)
return
--addtime>=cast('2008-12-15 00:00:00' as datetime) and addtime <= cast('2008-12-15 23:59:59' as datetime)
where字串我是准备用多条件拼接查询条件的,一样的东西,怎么程序用参数传值就不能用呢?
N'insert into #ls Select Count(Id) From Table1 where 1=1 ' + @WhereStr
select * from #ls
不过还是没弄明白,为什么查询分析器用两个单引号可以用而程序传值不能用