存储过程如下:在查询数据之前,必须要拼查询字符串,如果是调用存储过程传入的参数,是可以正常拼到查询字符串中,但在执行查询之前还要调用一个自定义函数"f_split_str_tab",并将结果拼到查询字符串中,如何实现呢??alter PROCEDURE test3
@User varchar(32),
@StartDate datetime,
@EndDate datetime,@AccountSubType1 varchar(32),
@AccountLevel varchar(32),
@OppWin varchar(32)
ASDeclare @sql varchar(2000)
declare @var varchar(200)
declare @r varchar(32)
select @var=AllowAccess from UserTable where UserCode=@Userset @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (select a from dbo.f_split_str_tab(@var,'|'))
exec @sqlreturn 1
GO
@User varchar(32),
@StartDate datetime,
@EndDate datetime,@AccountSubType1 varchar(32),
@AccountLevel varchar(32),
@OppWin varchar(32)
ASDeclare @sql varchar(2000)
declare @var varchar(200)
declare @r varchar(32)
select @var=AllowAccess from UserTable where UserCode=@Userset @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (select a from dbo.f_split_str_tab(@var,'|'))
exec @sqlreturn 1
GO
set @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (select a from dbo.f_split_str_tab('''+@var+''','|'))'
返回错误:
"对数据类型而言运算符无效。运算符为 boolean OR,类型为 varchar。"我试了,如果改成:
set @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (select a from dbo.f_split_str_tab('''+@var执行存储过程后,不能输出字符串,好像是@var并没有调用dbo.f_split_str_tab函数
set @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (f_split_str_tab(@var,'|'))'也可以在SP中先用Select f_split_str_tab(@var,'|')看一下函數輸出的結果!
select @users=a from dbo.f_split_str_tab('''+@var+''','|')set @sql = 'SELECT ae.AccountSubType1 ,ae.AccountLevel, ...FROM table2 o
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in ('+@users +')'
inner join sysdba.UserInfo ui on us.UserID=ui.UserID
WHERE o.ModifyDate>='''+CONVERt(varchar(32),@StartDate)+''' and o.ModifyDate<='''+CONVERt(varchar(32),@EndDate)+''' and ui.UserName in (select a from dbo.f_split_str_tab('''+@var+''',''|''))'這樣?
你可以在執行前把@sql Select出來,在執行時能不能成功。
不能直接用exec 调用varchar类型查询字符串吗?急死我了!!!!!