Thursday, March 22, 2012

Accessing a View from within a Stored Procedure

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @.MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @.MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!

Any offers kind people??It's a top kinda monday

top without order by is meaningless

Where's the DDL for the view?

And the actual sql statement or sproc?|||All the sorting and the like is done within the view itself. The only thing I need to know is if the view returns a recordset and if it does then proceed with the stored pro. A 'trimmed' down version of the Code is the following:

CREATE PROCEDURE SP_LastPDFCreated
AS
DECLARE @.MyLastPDFDate as DateTime, @.MyCurrentTime AS DateTime, @.MyJobN AS INT

SET @.MyJobN = ''
SET @.MyCurrentTime = GETDATE()

SELECT TOP 1 @.MyLastPDFDate = DatePDFCreated FROM dbo.TArcCert WHERE (DatePDFCreated IS NOT NULL) ORDER BY DatePDFCreated DESC

SELECT TOP 1 @.MyJobN = IJobN FROM MyView

IF @.MyJobN <> ''
BEGIN
IF DATEDIFF(n, @.MyLastPDFDate, @.MyCurrentTime) > 10
BEGIN
EXEC LimsLive.dbo.SP_TestXPSendMail
END
END
GO|||You sure the view exists?

And change the if statement...

IF EXISTS(SELECT TOP 1 IJobN FROM MyView)
BEGIN
.....
END|||God and it's only Monday!

Early entry for 'Twat of the week' award. I'd spelt my view incorrectly.

Apologies for wasting your time but thank you for your responses.

Sorry Matey|||and there's a term I haven't heard in a looooooooooooong time...

begs the question...since you didn't fill out the profile...

gender?|||That'll be Male, I've filled in bits and bobs of the profile|||I think "Twat" is the plural of "Twit" ;)|||I think "Twat" is the plural of "Twit" ;)Two points for the correct interpretation of English slang!

As I'm still a "Technical Wizard of Information Technology" with a number of UK friends, I've been informed in great detail about these things!

-PatP|||Two points for the correct interpretation of English slang!
if I can ever do that, It's an accident. I'm STILL trying to figure out what a couple gals (who CLAIMED to be speaking english) were saying in front of me in cockney at lunch one day in Croydon - something about crossing a road to see a toad or something...:scratching head:|||if I can ever do that, It's an accident. I'm STILL trying to figure out what a couple gals (who CLAIMED to be speaking english) were saying in front of me in cockney at lunch one day in Croydon - something about crossing a road to see a toad or something...:scratching head:Whenever you have two women talking to you about "crossing a road to see a toad", the best possible response I can imagine is to "play dumb" whether you have any clue or not!

-PatP|||Or put on some green and get over there before them.|||Hate to disagree folks, but I don't think Twat is a plural of anything. Rather a slang name for a part of a lady's anatomy!

No comments:

Post a Comment