on 07-18-2016 9:22 PM
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)
you want execute() vs. exec.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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).
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?
User | Count |
---|---|
74 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.