Strange and incorrect error when trying to exec a string

[Former Member]

I need to use exec to create a temp table because of parameterized information in the statement.  The data in @id_data comes from a remote location and I don't know what it will be on any particular run.

I hope someone can explain what is wrong with this code.  I've simplified it, mostly to avoid printing out my client's code, but this is giving the same error.  It seems to think I'm trying to call a procedure, rather than execute a "SELECT INTO" statement.  If I run the string by itself without using exec, it completes with no error.  I know there's extra concatenations but that isn't making any difference.  I've also tried having the assignment on a single line.

Here's the code that produces this problem:

create procedure p_foo

as

BEGIN

DECLARE

@id_data varchar(20),

@exec_string varchar(500)

select @id_data = '1, 2, 3, 4'

CREATE table tempdb..xref (gsid int, sys_id int, status varchar(9), book int)

CREATE table tempdb..rollup (code varchar(20), descr varchar(20), gsid int)

SELECT @exec_string = 'SELECT DISTINCT a.book, isnull(RIGHT(b.code,4),'''') as code, isnull(b.descr,'''') as descr ' +

  'INTO #info FROM tempdb..xref a, tempdb..rollup b WHERE a.gsid = b.gsid AND a.sys_id in ( ' + @id_data +

  ' ) AND upper(a.status) = ''ACTIVE'''

print @exec_string

exec @exec_string

END

The output with the error is:

SELECT DISTINCT a.book, isnull(RIGHT(b.code,4),'') as code, isnull(b.descr,'')

as descr INTO #info FROM tempdb..xref a, tempdb..rollup b WHERE a.gsid = b.gsid

AND a.sys_id in ( 1, 2, 3, 4 ) AND upper(a.status) = 'ACTIVE'

Msg 2812, Level 16, State 5:

Server 'DNYDS47500', Procedure 'p_foo', Line 20:

Stored procedure 'SELECT DISTINCT a.book, isnull(RIGHT(b.code,4),'') as code,

isnull(b.descr,'') as descr INTO #info FROM tempdb..xref a, tempdb..rollup b

WHERE a.gsid = b.gsid AND a.sys_id in ( 1, 2, 3, 4 ) AND upper(a.status) =

'ACTIVE'' not found. Specify owner.objectname or use sp_help to check whether

the object exists (sp_help may produce lots of output).

(return status = 0)

[former_member182259 · Accepted solution]

you want execute() vs. exec.

[Former Member · Accepted solution]

Thanks Jeff.  That worked.

It's strange because I've been using exec for other SQL SELECT statements, but this is the first SELECT INTO.  I'll go back and change them to execute(), just for correctness.

[Former Member · Accepted solution]

This is getting stranger.  When I first used execute(), it worked.  I got output.  Then suddenly the SELECT INTO statement stopped working.  It doesn't build the temporary table and I get no errors (until I try to access it).

I moved the temp tables that feed the table I want out of the proc and gave them some data:

CREATE table tempdb..xref (gsid int, sys_id int, status varchar(9), book int)

go

CREATE table tempdb..rollup (code varchar(20), descr varchar(20), gsid int)

go

insert into tempdb..xref values (1,1,'ACTIVE',1)

insert into tempdb..xref values (2,2,'ACTIVE',2)

insert into tempdb..xref values (3,3,'ACTIVE',3)

insert into tempdb..xref values (4,4,'ACTIVE',4)

insert into tempdb..rollup values ('one', 'one', 1)

insert into tempdb..rollup values ('two', 'two', 2)

insert into tempdb..rollup values ('three', 'three', 3)

insert into tempdb..rollup values ('four', 'four', 4)

go

The proc looks like this now:

drop procedure p_foo

go

create procedure p_foo

as

BEGIN

DECLARE

@id_data varchar(20),

@exec_string varchar(500)

select @id_data = '1, 2, 3, 4'

SELECT @exec_string = 'SELECT DISTINCT a.book, isnull(RIGHT(b.code,4),'''') as code, isnull(b.descr,'''') as descr ' +

  'INTO #info FROM tempdb..xref a, tempdb..rollup b WHERE a.gsid = b.gsid AND a.sys_id in ( ' + @id_data +

  ' ) AND upper(a.status) = ''ACTIVE'''

print @exec_string

execute(@exec_string)

select * from #info

SELECT DISTINCT a.book, isnull(RIGHT(b.code,4),'') as code, isnull(b.descr,'')

as descr INTO #info FROM tempdb..xref a, tempdb..rollup b WHERE a.gsid = b.gsid

AND a.sys_id in ( 1, 2, 3, 4 ) AND upper(a.status) = 'ACTIVE'

select * from #info

END

go

When I run this, I get:

Msg 208, Level 16, State 1:

Server 'DNYDS47500', Procedure 'p_foo', Line 28:

#info not found. Specify owner.objectname or use sp_help to check whether the

object exists (sp_help may produce lots of output).

When I comment out the first "select * from #info" line, I get

1	one	one
2	two	two
3	three	three
4	four	four

How do I fix this?

TIA.  ../Dan

[former_member182259 · Accepted solution]

Depending on the versions of ASE, there are restrictions with #temp tables and results between the two contexts.   You might want to look up execute immediate in the docs to see what it says in your version about temp tables, etc.

In your case, you might want to try:

create table #info (....)

execute (insert into #info select....)

BTW - ASE has long supported the notion of something like the following

declare @procname varchar(255)

select @procname = 'sp_help'

exec @procname 'sysobjects'

....which is why there was a bit of confusion between the proc exec primitive and the execute immediate function (which executes strings).  

[Former Member · Accepted solution]

OK, I'll try the create table followed by the execute.  It looks like it will work.  FWIW, here's the version info:

Adaptive Server Enterprise/15.7/EBF 24647 SMP SP134 /P/x86_64/Enterprise Linux/ase157sp133x/3925/64-bit/FBO/Sat May  2 10:31:07 2015

The strange thing is I only added parens in the exec call and it worked for a few runs and then stopped.  I can't see how the #info table would have still existed from previous runs.  Could there be scenarios where the execute would work or not work in subsequent runs?

[Former Member · Accepted solution]

Yay!  The CREATE followed by the executed insert is working.  Now to figure out why the next statement is giving wrong results, but I'm happy to be getting that far.

Thanks again Jeff.

./Dan