本文介绍了使用sp_executesql执行文本时出现问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
set @sqlText='Select [RowNo], [id], Desp,[Date], [Group],DebitAmt,CreditAmt,AccNo, AccType, Desp2,null as Age, RefNo1, Refno2,FGroupID,FPeriod,remark,AccountDescription
, Balance From @Temp'
exec sp_Excecutesql @sqlText;
当我执行此操作时会抛出错误：
@Temp不存在
这里我宣布@Temp表那样
exec sp_Excecutesql @sqlText;
when i excute this it throws error:
@Temp does not exist
Here i declare @Temp table like that
declare @Temp table(id int,desp varchar)
任何人都可以解决这个问题
我的程序是：
Can Anyone can solve this problem
My Procedure Is:
Create PROCEDURE [dbo].[pr_Report_RPGLPrint]
-- Add the parameters for the stored procedure here
@FsessionId int=1,
@Type varchar(20)='(All)',
@StartDate datetime='1900/01/01'
,@EndDate datetime='1900/01/01'
,@AccountId int=1,
@GroupID INT=0,
@startperiod INT=0,
@endperiod INT=0
AS
BEGIN
Set NoCount On;
Declare @Temp table(RowId int,RowNo bigint,Id int,Desp varchar(500),[Date] datetime,[Group] varchar(50),DebitAmt decimal(18,2),CreditAmt decimal(18,2),Balance decimal(18,2),
AccNo varchar(50), AccType varchar(50), Desp2 varchar(50), RefNo1 varchar(50), Refno2 varchar(50),FGroupID int,FPeriod real,remark varchar(500),AccountDescription varchar(500))
Declare @RowId int, @RowNo int, @Id int, @Desp varchar(100), @Date datetime=null, @Group varchar(50), @DebitAmt decimal(18,2), @CreditAmt decimal(18,2), @AccNo varchar(50)
, @AccType varchar(50), @Desp2 varchar(50), @RefNo1 varchar(50), @RefNo2 varchar(50), @FGroupID int, @FPeriod int, @remark varchar(500), @AccountDescription varchar(500)
, @Balance decimal(18,2), @Balance2 decimal(18,2), @CurrAccId varchar(50), @bNewAcc bit=0;
set @CurrAccId=0;
insert @temp
Select RowId =Row_Number() Over(Order By Id, [Date]), VTransactionGLReport.RowNo, Id, VTransactionGLReport.Desp
,case when [Date]='1900/01/01' then null else [Date] end [Date], [Group]
,DebitAmt = Case when DebitAmt=0 then null else DebitAmt end
,CreditAmt= Case when CreditAmt=0 then null else CreditAmt end
, Balance =Convert(decimal(18,2),0)
,AccNo, AccType, Desp2, RefNo1=Reference, Refno2=refno,FGroupID,FPeriod,remark,AccountDescription
from VTransactionGLReport where fsessionid=@FsessionId
-- Select * from fsession
Order By Id,[Date]
DECLARE tran_cursor CURSOR FOR
SELECT
RowId, RowNo, Id, Desp
,[Date], [Group]
,DebitAmt
,CreditAmt
,AccNo, AccType, Desp2, RefNo1, Refno2,FGroupID,FPeriod,remark,AccountDescription
, Balance
FROM @temp
Order By Id,[Date]
OPEN tran_cursor
FETCH NEXT FROM tran_cursor
INTO @RowId, @RowNo , @Id , @Desp , @Date , @Group , @DebitAmt , @CreditAmt,@AccNo , @AccType ,
@Desp2 , @RefNo1 , @RefNo2 , @FGroupID , @FPeriod , @remark , @AccountDescription , @Balance
WHILE @@FETCH_STATUS = 0
BEGIN
if(@CurrAccId<>@Id)
Begin
Set @Balance2=0;
Select @Balance2=IsNull(LastYBal,0) from gldata where id=@id
Set @CurrAccId=@Id;
--Insert Opening Balance Row
insert @temp values( @RowId-1, @RowNo , @Id , 'Balance B/F' , null , @Group
, case when @Balance2 >0 then @balance2 else null end
, case when @Balance2 <0 then Abs(@balance2) else null end,@Balance2
,@AccNo , null ,
null , null , null , @FGroupID , @FPeriod , @remark , @AccountDescription )
End
Set @Balance2 = @Balance2+(IsNull(@DebitAmt,0)-Abs(IsNull(@CreditAmt,0)));
Update @Temp set Balance=@Balance2 where id=@id and RowId=@RowId;
FETCH NEXT FROM tran_cursor
INTO @RowId, @RowNo , @Id , @Desp , @Date , @Group , @DebitAmt , @CreditAmt , @AccNo, @AccType ,
@Desp2 , @RefNo1 , @RefNo2 , @FGroupID , @FPeriod , @remark , @AccountDescription , @Balance
End
CLOSE tran_cursor;
DEALLOCATE tran_cursor;
if (@Type='(All)' and @StartDate='1900/01/01' and @AccountID=0 and @GroupId=0 and @StartPeriod=0)
Begin
Select RowNo, id, Desp
,[Date], [Group]
,DebitAmt
,CreditAmt
,AccNo, AccType, Desp2,null as Age, RefNo1, Refno2,FGroupID,FPeriod,remark,AccountDescription
, Balance from @Temp order by [id],Rowno,[date];
End
Else
Begin
declare @sqlText nvarchar(max), @sqlText1 nvarchar(max),@where nvarchar(max),@where2 nvarchar(max);
set @where='';
if(@Type<>'(All)' and @Type<>'')
begin
set @where=@where + (case when @where<>'' then ' and ' else ' ' end)+ ' AccType='+ char(39) + left(@Type,1) + char(39)
END
IF(@AccountId<>0)
BEGIN
set @where=@where + (case when @where<>'' then ' and ' else ' ' end) + ' T.FSessionId='+ Convert(varchar,@FSessionid)
END
IF(@AccountId<>0)
BEGIN
set @where=@where + (case when @where<>'' then ' and ' else ' ' end) + ' T.ID='+ Convert(varchar,@AccountId)
END
IF(@GroupID<>0)
BEGIN
set @where=@where + (case when @where<>'' then ' and ' else ' ' end) + ' FGroupID='+ Convert(varchar,@GroupID)
END
if(@startPeriod<>0 and @endperiod<>0)
begin
set @where=@where + (case when @where<>'' then ' and ' else ' ' end) + ' fPeriod>='+convert(varchar,@startperiod)
+ ' and fPeriod<='+convert(varchar,@endperiod)
END
if(@startdate<>'1900/01/01' and @enddate<>'1900/01/01')
begin
set @where=@where + (case when @where<>'' then ' and ' else ' ' end) + '( cast(Convert(varchar,date,112) as int)>='+convert(VARCHAR,@startdate,112)
+ ' and cast(Convert(varchar,date,112) as int)<='+convert(VARCHAR,@enddate,112)+') or [Date] is null'
end
if (@where<>'')
Begin
Set @where= ' Where ' + @Where + ' ';
End
set @sqlText='Select [RowNo], [id], Desp,[Date], [Group],DebitAmt,CreditAmt,AccNo, AccType, Desp2,null as Age, RefNo1, Refno2,FGroupID,FPeriod,remark,AccountDescription
, Balance From @Temp'-- + Convert(varchar,@where)+ ' order by [id],Rowno,[date]'
--Select * from @Temp
exec(@sqltext)
--pr_Report_RPGLPrint
End
End
在If Condition下工作正常，但在其他部分它会给出错误
Under If Condition it works fine,But In else part it will give error
推荐答案
CREATE TABLE #tmp (
ID INT
)
INSERT INTO #tmp VALUES(1)
DECLARE @sql VARCHAR(50) = 'SELECT * FROM #tmp'
EXEC(@sql)
DROP TABLE #tmp
这篇关于使用sp_executesql执行文本时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！