exec proc_yewu_xx ' or src_id=+ ''' +cast('0189827' as nvarchar(30))+'''','2003-11-11','2003-12-03'而我在存贮过程中用set @src_id=' or src_id=+ ''' +cast('0189827' as nvarchar(30))+''''是没有问题的。
set @src_id=' or src_id=+ ''' +cast('0189827' as nvarchar(30))+'''' 也有错啊,虽然语句可以,但结果有错。 要这样 set @src_id=' or src_id=''' +cast('0189827' as nvarchar(30))+''''exec proc_yewu_xx ' or src_id=''' +cast('0189827' as nvarchar(30))+''''OR: exec proc_yewu_xx @src_id
这是我的存贮过程 alter proc proc_yewu_xx @src_id varchar(4000), @start_time char(11), @end_time char(11) as declare @first_time varchar(20),@last_time varchar(20),@m int declare @number int,@sql nvarchar(4000),@order_number int declare @par nvarchar(4000),@p varchar(8000),@n int --set @src_id=' or src_id=+ ''' +cast('0189828' as nvarchar(30))+'''' --直接赋值没有错,如果传递过来时就不行 set @src_id=' where src_id=+ ''' +cast('0189848' as nvarchar(30))+''''+@src_id set @start_time=substring(@start_time,1,4)+substring(@start_time,6,2)+substring(@start_time,9,2) set @end_time=substring(@end_time,1,4)+substring(@end_time,6,2)+substring(@end_time,9,2) set @first_time=@start_time set @last_time=@end_time print @start_time print @last_time delete sms..tbl_yewu_xx while cast(@first_time as int) <= cast(@last_time as int) begin select @sql='select @n=count(name) from sms_data..sysobjects where name=@name and type=''U''' select @par='@n int out,@name varchar(4000)' select @p='send'+@first_time execute sp_executesql @sql,@par,@n out,@p if @n=0 ---'no extst' set @first_time=@first_time + 1 else ---'extst' begin set @sql='insert into sms.dbo.tbl_yewu_xx(dest_id,src_id,send_content,send_time,fee_code,send_result)' set @sql=@sql+' select src_id,dest_id,send_content,send_time,fee_code,send_result from sms_data.dbo.send'+@first_time + @src_id exec(@sql) set @first_time=@first_time + 1 end end
我想得到@src_id是很多的 or src_id=+ ''' +cast('0189828' as nvarchar(30))+''' or src_id=+ ''' +cast('0189829' as nvarchar(30))+''' 那样的字符串,然后赋给查询的条件,如果直接是 or src_id=0189829在查询时间大于10时会出现错误,就用了一转换。
1. set @src_id=' where src_id=----+ 把这个加号去了,就对了。 ''' +cast('0189848' as nvarchar(30))+''''+@src_id 2. 先注释这句: --exec(@sql) 加上: print @sql 把语print结果拿出来运行,你就知道有没有错,有错错在那儿了。
declare @d varchar(100) set @d = 'or src_id= ''' + cast('0189828' as nvarchar(30))+ ''''
那里没有问题,我可以运行,是真的。我现在是要 exec proc_yewu_xx ' or src_id=+ ''' +cast('0189827' as nvarchar(30))+'''','2003-11-11','2003-12-03'传递给随便一个存贮过程,用print能打印出or src_id=+ ''' +cast('0189827' as nvarchar(30))+''' 帮我想想这里。
to happyflystone(快乐的飞石) 我不是要在存贮过程中赋值,我是要在delphi中传递参数给存贮过程。
declare @src_id varchar(1000) set @src_id=' or src_id=+ ''' +cast('0189827' as nvarchar(30))+'''' print @src_id结果是: or src_id=+ '0189827'而不是你要的: or src_id='0189827'
--declare @received_text varchar(2000),@sql varchar(4000) --set @received_text='0189828*0189806*0189858*0189824*' create table tempa_tjp ( aa varchar(20)) delete tempa_tjp SELECT @sql = ' insert into tempa_tjp select ''' + REPLACE(REPLACE(SUBSTRING(@received_text, 0, LEN(@received_text) ), '*', ''' UNION ALL SELECT '''), '*', ''', ''') + '''' execute (@sql) --select * from tempa_tjpset @src_id=' where src_id=+ ''' +cast('0189' as nvarchar(30))+'''' declare #aa cursor for select * from tempa_tjp --临时表 open #aa fetch next from #aa into @string while @@fetch_status=0 begin set @src_id=@src_id + ' or src_id=+'''+cast(@string as nvarchar(30))+'''' fetch next from #aa into @string end fetch next from #aa into @string close #aa deallocate #aa drop table tempa_tjp 谢谢各位
set @src_id=' or src_id=+ ''' +cast('0189827' as nvarchar(30))+''''
也有错啊,虽然语句可以,但结果有错。
要这样
set @src_id=' or src_id=''' +cast('0189827' as nvarchar(30))+''''exec proc_yewu_xx ' or src_id=''' +cast('0189827' as nvarchar(30))+''''OR:
exec proc_yewu_xx @src_id
alter proc proc_yewu_xx
@src_id varchar(4000),
@start_time char(11),
@end_time char(11)
as
declare @first_time varchar(20),@last_time varchar(20),@m int
declare @number int,@sql nvarchar(4000),@order_number int
declare @par nvarchar(4000),@p varchar(8000),@n int
--set @src_id=' or src_id=+ ''' +cast('0189828' as nvarchar(30))+'''' --直接赋值没有错,如果传递过来时就不行
set @src_id=' where src_id=+ ''' +cast('0189848' as nvarchar(30))+''''+@src_id
set @start_time=substring(@start_time,1,4)+substring(@start_time,6,2)+substring(@start_time,9,2)
set @end_time=substring(@end_time,1,4)+substring(@end_time,6,2)+substring(@end_time,9,2)
set @first_time=@start_time
set @last_time=@end_time
print @start_time
print @last_time
delete sms..tbl_yewu_xx
while cast(@first_time as int) <= cast(@last_time as int)
begin
select @sql='select @n=count(name) from sms_data..sysobjects where name=@name and type=''U'''
select @par='@n int out,@name varchar(4000)'
select @p='send'+@first_time
execute sp_executesql @sql,@par,@n out,@p
if @n=0 ---'no extst'
set @first_time=@first_time + 1
else ---'extst'
begin
set @sql='insert into sms.dbo.tbl_yewu_xx(dest_id,src_id,send_content,send_time,fee_code,send_result)'
set @sql=@sql+' select src_id,dest_id,send_content,send_time,fee_code,send_result from sms_data.dbo.send'+@first_time + @src_id
exec(@sql)
set @first_time=@first_time + 1 end
end
or src_id=+ ''' +cast('0189829' as nvarchar(30))+''' 那样的字符串,然后赋给查询的条件,如果直接是 or src_id=0189829在查询时间大于10时会出现错误,就用了一转换。
set @src_id=' where src_id=----+ 把这个加号去了,就对了。
''' +cast('0189848' as nvarchar(30))+''''+@src_id 2.
先注释这句:
--exec(@sql)
加上:
print @sql
把语print结果拿出来运行,你就知道有没有错,有错错在那儿了。
set @d = 'or src_id= ''' + cast('0189828' as nvarchar(30))+ ''''
帮我想想这里。
set @src_id=' or src_id=+ ''' +cast('0189827' as nvarchar(30))+''''
print @src_id结果是:
or src_id=+ '0189827'而不是你要的:
or src_id='0189827'
where src_id=+ '0189848' or src_id=+'0189802'
20031001
20031203 (所影响的行数为 2866 行)
(所影响的行数为 2 行)
(所影响的行数为 2 行)
(所影响的行数为 4 行)等等
declare proc dd
@dd char(1000),
@i int
as
delcare @sql char(2000)
set @sql = 'select * from table where 1 =1 '+ @dd
exec(@sql)
return
go
调用:
在delphi 用一个串,你转成 “or src_id='0189827'”然后传给过程啊,
http://expert.csdn.net/Expert/topic/2423/2423109.xml?temp=.5001642
(大力是不会靠诉你这么做的.如果大力非这么说,我马上写信给MS,把他的MVP给免了)你把加号去了.先试试.
用print @sql 方法看看语句错在那儿.其它的没看到具体的传入语句.谁也讲不请.
--set @received_text='0189828*0189806*0189858*0189824*'
create table tempa_tjp
( aa varchar(20))
delete tempa_tjp
SELECT @sql = ' insert into tempa_tjp select ''' + REPLACE(REPLACE(SUBSTRING(@received_text, 0, LEN(@received_text) ), '*', ''' UNION ALL SELECT '''), '*', ''', ''') + ''''
execute (@sql)
--select * from tempa_tjpset @src_id=' where src_id=+ ''' +cast('0189' as nvarchar(30))+''''
declare #aa cursor for select * from tempa_tjp --临时表
open #aa
fetch next from #aa into @string
while @@fetch_status=0
begin
set @src_id=@src_id + ' or src_id=+'''+cast(@string as nvarchar(30))+''''
fetch next from #aa into @string
end
fetch next from #aa into @string close #aa
deallocate #aa
drop table tempa_tjp
谢谢各位