#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    6
    Rep Power
    0

    Execute stored proceedure from SP


    Hi all i am attempting to Execute a stored procedure (SP2) From within a stored procedure (SP1). This works fine if i assign a value to the variable in SP2 but when i use the query an Internal SQL Server Error occurs.

    Below is SP2 and then the execute line from SP1

    CREATE PROCEDURE SP2
    @IP_ID VARCHAR(30),
    @Network VARCHAR(30),
    @CountRatio INT OUTPUT
    AS


    SET @CountRatio = (SELECT COUNT(Ratio) FROM RatioReport WHERE IP_ID= @IP_ID and Network= @Network and Ratio=0)



    RETURN @CountRatio
    GO

    The Execute line in SP1 is


    EXEC SP2 @IP_ID,@Network,@CountRatio OUTPUT

    I am stumped as to why it will not return a value using the query, i have tested the query and that works fine, any ideas?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    6
    Rep Power
    0
    hi all

    i have worked out the problem, it is because in the SP2 the query:

    (SELECT COUNT(Ratio) FROM RatioReport WHERE IP_ID= @IP_ID and Network= @Network and Ratio=0)

    is on a view.

    I have transfered the data into a table and it works fine when i query the table but this is not a perminent option

    is it possible to query a view from within this stored procedure?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    45
    Rep Power
    11
    Just query the view as you would any other table

    e.g.

    select * from my_view
    where blah = @Blah

    etc
    <%Questions of Science%>

IMN logo majestic logo threadwatch logo seochat tools logo