Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: Calling Stored Procedures in a Select Statement

    I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ah, in a word, No.

    BUT!

    Use a passthru query and do

    EXEC procedurename;
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to try joining into the result set from your stored procedure, this is generally frowned upon (when allowed at all) because a procedure can conceivable return more than one result set, and could even modify data during execution that is also reference by your SQL statement. Confusion results.

    If possible, rewrite your stored procedure as a view or as a user-defined table function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    5
    I figured it out. I had to do was create the function below:

    CREATE FUNCTION dbo.udfMonthlyIncome
    (
    @salary int
    ,@frequency int
    )
    RETURNS int AS
    BEGIN
    Return
    (
    SELECT CASE @frequency
    WHEN 1 /*'Bi-Weekly'*/ THEN (@salary * 26) / 12
    WHEN 2 /*'Daily'*/ THEN @salary * 20
    WHEN 3 /*'Hourly'*/ THEN (@salary * 40 * 52) / 12
    WHEN 4 /*'Monthly'*/ THEN @salary
    WHEN 5 /*'Quarterly'*/ THEN @salary / 3
    WHEN 6 /*'Semi-Annual'*/ THEN @salary / 6
    WHEN 7 /*'Semi-Monthly'*/ THEN @salary * 2
    WHEN 8 /*'Weekly'*/ THEN (@salary * 52) / 12
    WHEN 9 /*'Annual'*/ THEN @salary / 12
    ELSE 0
    END as Income
    )
    END

    Then I can call the function like this:

    Select dblSalary, dbo.udfMonthlyIncome(dblSalary,lngFrequency) as MonthlyIncome
    from tblIncome

    Thanks for the help guys

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Way to go!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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