Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Call Stored procedure from view

    Hello all,
    does anyone know if it's possible to call a stored procedure from a view.

    Thnx,
    Patrick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Patrick

    Welcome to the forum
    Quote Originally Posted by patrickv
    does anyone know if it's possible to call a stored procedure from a view.
    Why yes I do. And it's not. Have a look at CREATE FUNCTION in BoL. Assuming your sproc returns a result set then check out the inline table valued functions entry.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Well, this is my problem. I must do an insert into a table. In our program I must call a view. It's not possible to call a stored procedure. This view calls an user defined function.
    This is the code, but a user defined function can't execute this. There is an error in this code or it's impossible that the user function can run this code.
    ****************
    CREATE FUNCTION dbo.SendMededelingPdfUsage(@userid as varchar(10))
    RETURNS INT
    AS
    BEGIN

    declare @gebruiker as varchar(10)
    declare @taal as varchar(2)
    --declare @userid as varchar(10)
    declare @sysdate as varchar(8)
    declare @systime as varchar(8)

    declare @nl01 as varchar(75)


    set @nl01 = 'Test'


    -- Cursors
    declare @csr_Gebruiker cursor

    SET @csr_Gebruiker = CURSOR FOR
    SELECT G.gebruiker, G.taal, W.userid
    FROM tbl_gebruikers G, tbl_ws W Where G.gebruiker=W.gebruiker and online='J' and userid in ('24DY996GPH')

    OPEN @csr_Gebruiker

    FETCH NEXT FROM @csr_Gebruiker INTO @gebruiker,@taal,@userid


    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    if @taal='NL'
    BEGIN
    Insert into tbl_mededeling Values(@gebruiker,@sysdate,@systime,'001',@nl01)
    END

    FETCH NEXT FROM @csr_Gebruiker INTO @gebruiker,@taal ,@userid
    END
    GO
    return 1
    END
    **************
    I always get an error on the second last END.
    Who sees the problem.

    Regards,
    Patrick

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did I miss something or could this just be:
    Code:
    INSERT INTO tbl_mededeling
    SELECT  G.gebruiker,
            '',
            '',
            '001',
            'test'
    FROM    tbl_gebruikers G
            INNER JOIN tbl_ws W ON G.gebruiker = W.gebruiker
    WHERE   online = 'J'
            AND userid = '24DY996GPH'
            AND G.taal = 'NL'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also -

    Code:
    ....
    GO
    return 1
    END
    Anything look a bit funny about the order?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW (finally) - just to take a step back - your application needs to manipulate (INSERT\ DELETE\ UPDATE) data but you can only call views? This is a really kludgey way to go about changing data in the database and is defo prone to bugs. How come you can't use sprocs?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2006
    Posts
    4
    You are not gone believe it. An error occured "syntax error near 'NL'.

    CREATE FUNCTION dbo.SendMededelingPdfUsage(@userid as varchar(10))
    RETURNS INT
    AS
    BEGIN

    INSERT INTO tbl_mededeling
    SELECT G.gebruiker,
    '',
    '',
    '001',
    'test'
    FROM tbl_gebruikers G
    INNER JOIN tbl_ws W ON G.gebruiker = W.gebruiker
    WHERE online = 'J'
    AND userid = '24DY996GPH'
    AND G.taal = 'NL'

    GO
    return 1
    END

    The reason why I can't call procs is that we have an old webshell program. All the updates, insert ... are written in C. All the ODBC connections and functions are managed there, but has no function to call a stored proc.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by patrickv
    You are not gone believe it. An error occured "syntax error near 'NL'.
    I do you know. Check out post #5
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2006
    Posts
    4
    It doesn't matter which order i take, I get always an error. In this case I get the syntax error, in an other case I get the error "invalid use of an insert within a function"

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by patrickv
    It doesn't matter which order i take, I get always an error.
    Yes but the order still matters - not all errors are the same. The order is:
    Code:
    return 1
    END
    GO
    Regarding the error - I didn't think you could run these sort of statements in a function. I checked BoL and it looked like you can. I just checked again more carefully and no - you can't - the bit I read referred to local table variables.
    Sorry.

    So - you need to do this directly to the tables or figure out how to access sprocs with your legacy app.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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