Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121

    Unanswered: 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??

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure the view exists?

    And change the if statement...

    IF EXISTS(SELECT TOP 1 IJobN FROM MyView)
    BEGIN
    ......
    END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    That'll be Male, I've filled in bits and bobs of the profile

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I think "Twat" is the plural of "Twit"
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    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

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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:
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TallCowboy0614
    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

  12. #12
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Or put on some green and get over there before them.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  13. #13
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •