Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    Lawrence, KS
    Posts
    14

    Unanswered: Selecting from stored procedures

    I would like to select a single value from a stored procedure in a select statemetn. I have a lookup table that I have defined a stored procedure for. Given the key it returns the value associated with that key. The body of the is as follows:

    CREATE PROCEDURE Help_Lookup_sp @key int AS
    SELECT
    @description = lookup.desc
    FROM
    lookup
    WHERE
    lookup.val_id = @key

    I have no problem executing this stored procedure giving it the proper parameter.

    Help_Lookup_sp 321

    returns
    'Invalid username'

    How would I execute this in a select statement that returns other data? I have not been able to get this to work.

    Any suggestions?

  2. #2
    Join Date
    Sep 2003
    Posts
    7
    CREATE PROCEDURE Help_Lookup_sp
    (
    @key int ,
    @description VARCHAR(200) output
    )
    AS
    SELECT
    @description = lookup.desc
    FROM
    lookup
    WHERE
    lookup.val_id = @key


    ------
    call it like

    declare @strdescription varchar(200)
    EXEC Help_Lookup_sp 23232, @strdescription OUTPUT
    ---

  3. #3
    Join Date
    Aug 2002
    Location
    Lawrence, KS
    Posts
    14
    How would I use the stored procedure in a select stmt? Say for instance, that I wanted to include the lookup value returned from the stored procedure as one of many fields in a select stmt or even in a view. Would I do an exec in the column selection part of my select stmt? Could you please give an example?

  4. #4
    Join Date
    Sep 2003
    Posts
    7
    Originally posted by tkofford
    How would I use the stored procedure in a select stmt? Say for instance, that I wanted to include the lookup value returned from the stored procedure as one of many fields in a select stmt or even in a view. Would I do an exec in the column selection part of my select stmt? Could you please give an example?
    yep as much as i know you can't.

    need to capture it in a variable and then use it in the select statement.

    can't do an EXEC stored procedure within one single select statement

Posting Permissions

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