sp resultset in a view defenition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sp resultset in a view defenition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-07 : 09:25:26

chavdar writes "Do you know how I can use a stored procedure's resultset in a view?
Something like:

|create view vvv as
|select * from openquery(ME, 'exec db..stored_proc')

where ME is the local server defined as a linked one. This returns the following error on my system (ms sql 2000, sp1; winnt 4.0, sp6):

|Invalid object name '#t'.

The stored procedure definition is:

|create procedure stored_proc
|as
| create table #t (YRNO dec(2,0), PBEC dec(15,8))
| -- populating #t here
| select * from #t
|go

I don't want to use non-temporary or predefined tables to keep the results because of the concurrence.

Thanks,
- chavdar"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-07 : 09:42:37

Why can't you just call the SP directly? A view won't be able to access a temp table anyway, so I don't think there's another way of doing it.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-07 : 13:56:57

The reason this is failing is because the linked server creates a new connection to the database - the temp table is created on that connection - not that of the view.

You can get round the concurrence problem by adding spid and logon_time as an identifier to a permanent table.
then
select *
from tbl , master..sysprocesses s
where tbl.spid = @@spid
adn tbl.spid = s.spid
and tbl.logon_time = s.logon_time

would be interesting to see what happens with the openquery in the view in this case.
I would not expect it to work - I'm surprised it doesn't give a syntax error.
You will have a similar problem with this though as the openquery will still use a different spid.
Use exec db..stored_proc @@spid and use the parameter for the insert key into the table.

My immediate reaction though is that you should look at your design as sort of thing is likely to cause enormous problems in the future. I suspect you are going down a wrong path.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources