- SQL code
declare @Sql nvarchar(500)declare @SysOrderNo intdeclare @ShortName varchar(30),@Spec varchar(30),@Spec1 varchar(30),@Spec2 varchar(30),@Spec3 varchar(30),@Spec4 varchar(30)declare @DataBaseName varchar(30)Set @DataBaseName='MDFData'Set @SysOrderNo=5Set @Sql='Select @ShortName=ShortName,@Spec=Spec,@Spec1=Spec1,@Spec2=Spec2,@Spec3=Spec3,@Spec4=Spec4 From [email protected]+'.dbo.ORDERDETAIL '+' Where SysOrderNo='+cast(@SysOrderNo as varchar(10))Select @Sqlexec sp_executesql @Sql,[email protected] varchar(30) output,@Spec varchar(30) output,@Spec1 varchar(30) output,@Spec2 varchar(30) output,@Spec3 varchar(30) output,@Spec4 varchar(30) output',@ShortName output,@Spec output,@Spec1 output,@Spec2 output,@Spec3 output,@Spec4 outputSelect @ShortName,@Spec,@Spec1,@Spec2,@Spec3,@Spec4
[email protected],@Spec,@Spec1,@Spec2,@Spec3,@Spec4记录
问题在于：
1程序这里只能用exec(@Sql)的形式
2要取出返回值
单纯执行SQL语句返回【3】条记录，但是用exec sp_executesql却只有【1】条，帮助文档里说可以用游标，但是怎么弄都不对，求帮助
------解决方案--------------------
- SQL code
declare @Sql nvarchar(500)declare @SysOrderNo intdeclare @ShortName varchar(30),@Spec varchar(30),@Spec1 varchar(30),@Spec2 varchar(30),@Spec3 varchar(30),@Spec4 varchar(30)declare @DataBaseName varchar(30)Set @DataBaseName='MDFData'Set @SysOrderNo=5create table #T ( Row int identity(1,1) not null, ShortName varchar(30) null, Spec varchar(30) null, Spec1 varchar(30) null, Spec2 varchar(30) null, Spec3 varchar(30) null, Spec4 varchar(30) null, primary key (Row))DECLARE @Rows int, @Row int Set @Sql=' insert into #t Select ShortName,Spec,Spec1,Spec2,Spec3,Spec4 From [email protected]+'.dbo.ORDERDETAIL '+' Where SysOrderNo='+cast(@SysOrderNo as varchar(10))exec sp_executesql @Sqlset @Rows = @@ROWCOUNTset @Row = 1while (@Row<[email protected])begin Select @ShortName = ShortName,@Spec = Spec,@Spec1 = Spec1,@Spec2 = Spec2, @Spec3 = Spec3,@Spec4 = Spec4 from #T where Row = @Row select @ShortName,@Spec,@Spec1,@Spec2,@Spec3,@Spec4 set @Row = @Row + 1end