Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Return SP in a SELECT statement column

    I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.


    I want the following output:


    CatID | CatTitle | CatTree
    001 | News | exec sp_DisplayTree(@CatID)


    My code I tried doesn't work:


    SELECT
    C.CatID As CatID,
    C.CatTitle As CatTitle,
    CatTree = (exec sp_DisplayTree C.CatID)
    FROM
    Cats As C WITH (nolock)



    I cannot find a solution to my solution, please help...

  2. #2
    Join Date
    Jan 2005
    Location
    Australia
    Posts
    5
    I'm making the assumption that you need the sp string to be returned as a varchar, but try:

    SELECT C.CatID As CatID,
    C.CatTitle As CatTitle,
    CatTree = 'exec sp_DisplayTree(' + CAST(C.CatID AS varchar) + ')'
    FROM Cats As C WITH (nolock)

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    Thanks for your reply. Sorry, I didn't clarify in my example that I don't want the literal text "exec sp_DisplayTree(C.CatID)" to be displayed in the column. I need the sp to run for every column to return a dynamic result for every row.

    My sp_DisplayTree(C.CatID) returns the following single column:

    CatTree
    Home : Music : Artists


    I want the following output:

    CatID | CatTitle | CatTree
    009 | Artists | Home : Music : Artists

    Please help...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Change your stored procedure to a user-defined function, and then you can include it in your SELECT clause.
    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
  •