Problem in Execute Text using sp_executesql

Question

4.00/5 (1 vote)

See more:

SQL

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;

when i excute this it throws error:

@Temp does not exist

Here i declare @Temp table like that

SQL

declare @Temp table(id int,desp varchar)

Can Anyone can solve this problem

My Procedure Is:

SQL

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

Under If Condition it works fine,But In else part it will give error

Posted 25-Feb-14 18:04pm

RahulRana723

Updated 25-Feb-14 18:18pm

Tejas Vaishnav

v4

Add a Solution

Comments

Krunal Rohit 26-Feb-14 0:10am

Paste your entire procedure here. Use Improve Question. :)
-KR

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Answer 1 · 2014-02-25T18:33:00

Solution 1

When you are using exec('your query') it will execute that current statement written in that bracket, it is not aware about your previous rows or any declaration.
And you are using your temporary table (@temp) in your select statement which you have declared in your store procedure, if you need to use it you need to include it declaration in your execute statement.

for your help you can use #table it is also temparary table like @table, but it creating phical table, you need to take care for it like this, also please drop your table at the end of your store procedure...

SQL

CREATE TABLE #tmp (
    ID INT
)

INSERT INTO #tmp VALUES(1)

DECLARE @sql VARCHAR(50) = 'SELECT * FROM #tmp'

EXEC(@sql)

DROP TABLE #tmp

Posted 25-Feb-14 18:33pm

Tejas Vaishnav

Updated 25-Feb-14 18:34pm

v2

Comments

RahulRana723 26-Feb-14 0:39am

yes you are right.
but what happen when application will multi user

Tejas Vaishnav 26-Feb-14 0:45am

its all conflict will taken care by store procedure, it will allocate sap rate space to each user, so no one get data conflict, even one person is doping a table, it will not affect other running sql session, so don't worries

RahulRana723 26-Feb-14 0:52am

when two user running the same store procedure at same time then it will give error
Temp table already exist

Tejas Vaishnav 26-Feb-14 1:15am

Store procedure will allocate, separate table space to each session, so if more then one user is calling the same procedure at the same time it will not affect the other. so please don't worry about it.

Problem in Execute Text using sp_executesql

1 solution

Solution 1

Add your solution here

Preview 0

Problem in Execute Text using sp_executesql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us