Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Executing SP inside SP dynamically

    I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

    Exec sp_executesql N’exec procedurename {parameterlist}’, N’{parameter declaration}’, Parametervalues


    Now I want to read a particular value that is being return be the procedure.
    NOTE: procedure is returning a resultset.

    Please help me.

    Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    In Books OnLine, look up the keywords OUTPUT variable and RETURN.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    Quote Originally Posted by gnanap
    I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.

    Exec sp_executesql N’exec procedurename {parameterlist}’, N’{parameter declaration}’, Parametervalues


    Now I want to read a particular value that is being return be the procedure.
    NOTE: procedure is returning a resultset.

    Please help me.

    Thanks!
    Try This

    DECLARE @sql nvarchar(2048)
    SET @sql = ' SET @count = ( SELECT COUNT(*) FROM table1 )'
    DECLARE @temp int
    EXEC sp_executesql @sql, N'@count int OUTPUT', @temp OUTPUT

    James

Posting Permissions

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