July 22, 2008 at 10:51 am
Hi All
I got stuck with a problem, i need to call a SP from a View
Say for example i got a SP called SP_XXX, i need to call this from a view called Vw_YYY
Like Create view Vw_YYY as
--- We have to call the SP here
i cant create a temp table inside a view to store the values from the SP and then select that temp table
Can any one suggest wethere this is possible???
Cheers
July 22, 2008 at 10:59 am
Not possible. A view is a single select statement.
Could you explain a bit more why you're trying to do this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 11:19 am
sounds like you want the results of sp_who in a view for example?
simply sp_helptext sp_XXX, and read the code....maybe you can simply lift the code, or maybe you can convert it to a function, so you can create a view of the function...it really depends...a lot of the sp_'s have cursors and otehr things that can make it difficult to do a view.
the code to call a stored proc and display it is already easily accomplished...so can you explain why you want to do this?
Lowell
July 22, 2008 at 11:27 am
Hi Gila
I found a work around, but iam facing error, the work around is
select * from OpenRowset ( 'SQLOLEDB','Server=(Local);TRUSTED_CONNECTION=YES;','Exec Temp.dbo.Software') as a
Temp.dbo.Software is the SP that returns me values
but when i tried to run this i am getting a error
Cannot process the object "Exec Temp.dbo.Software'". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Does this strike any bells
July 22, 2008 at 11:31 am
CrazyMan (7/22/2008)
Hi GilaI found a work around, but iam facing error, the work around is
select * from OpenRowset ( 'SQLOLEDB','Server=(Local);TRUSTED_CONNECTION=YES;','Exec Temp.dbo.Software') as a
Ouch. That's extreme. And a bad idea.
Please explain why you need the results of a proc in a view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 11:57 am
HI Gial
I am planning to cache this on Java, this is the requriment, Basically i need to check wether the record is there on a table or not, i can check this on the View and return the value with the status called True, but when the record is not there on the table then i need to display the value with status as False, i cant do this in View, can i??, so i used a SP and then calling that SP on this view, is there any other way to do this??
Cheers
July 22, 2008 at 11:59 am
Can't java call stored procedures?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 12:02 pm
it can call but it cant cache, it can only cache View, thats where the problem comes in
July 22, 2008 at 12:03 pm
Or, if you prefer (assuming no parameters)
CREATE VIEW IsRowThere AS
SELECT CASE WHEN Row_Count >0 THEN 'True' ELSE 'False' END As ItsThere
FROM
(SELECT COUNT(*) AS row_Count FROM Sometable Where SomeConditions) x
GO
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 12:03 pm
Since you're not passing any parameters to the proc/view, it should be possible with a view.
I don't have your tables, so here's a generic version:
create view RowExists
as
select (cast 1 as bit) as RE
from dbo.Table1
where exists
(select *
from dbo.Table1)
union
select top 1 (cast 0 as bit)
from sys.all_objects
where not exists
(select *
from dbo.Table1)
You might be able to use something like that to get the job done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 12:05 pm
Sorry i dint mention , i am passing 2 parameters to the table,
the View has to check on this 2 conditions and then return the 2 values and the status as a result
July 22, 2008 at 12:08 pm
Can java cache a call to a udf? If so, then you can convert one of those queries into a udf with the two parameters and do a select * from that
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 12:08 pm
As a side comment, from the original post on this thread, don't name your procs with "sp_" at the start of the name. Reduces database performance, because that makes it look in the master database first.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 12:08 pm
how can we do that???
July 22, 2008 at 12:11 pm
CrazyMan (7/22/2008)
how can we do that???
Do what?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply