EXEC Strangeness

Hi all,

I would be very grateful if anyone can detect the problem with the following code. I have indicated the offending line. Basically if I hardcode in @.BelegId, it runs with no problems.

Declare @.SQL VarChar(1000)
Declare @.TableName VarChar(1000)
Declare @.Result Varchar(50)
Declare @.BelegId int
Set @.BelegId=447
Set @.ObjName='s_jps.ap_gr'

if (object_id (N'tempdb..#tempt') is not null) drop table #tempt;
create table #TempT (result varchar(1000))
--SELECT @.SQL='DECLARE @.RESULT VARCHAR(1000) '

SELECT @.SQL = 'SELECT CONVERT(VARCHAR(1000),' + @.ObjName + ') FROM '
SELECT @.SQL = @.SQL + 's_jps '
***PROBLEMATIC LINE FOLLOWS: IF I HARDCODE BELEGID, NO PROBLEMS***
SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + @.BelegId
SELECT @.sql = @.sql + ' and s_se_art_id=5'
INSERT #TempT Exec ( @.SQL )
SELECT * FROM #TempT
GO

He's a quick fix without knowing anything else about your environment...

Change Declare @.BelegId int to Declare @.BelegId varchar(5)

SQLDiva

|||SELECT @.Sql= @.Sql + 'WHERE de_beleg_id=' + convert(varchar(10), @.BelegId)|||Please take a look at sp_executesql. It is safer to use sp_executesql than concatenating strings to execute in dynamic SQL. You need to protect the code against SQL injection attacks. And you should avoid dynamic SQL in this case if it is easier to just use a static SELECT statement specifying the table name. You probably don't need a temporary table also unless you are doing some complex processing.|||Thanks for the advice. I already looked into sp_executesql but had all sorts of problems with output parameters.

And I'm afraid that the unfortunate truth is that I do have to do some complex processing.