Re: EXECUTE 'tsqlstring' with parameters

From: Russ Ferrill (rferrill_at_paracominc.com)
Date: 08/25/04

  • Next message: Russ Ferrill: "Re: Passing a table or cursor into a stored procedure" 
    Date: Wed, 25 Aug 2004 16:11:20 GMT

    I've tried using exec by itself, exec sp_executesql with an embedded exec
    string and sp_executesql with parameter substitution. They all work fine
    until I need to execute a stored procedure that uses an OUTPUT parameter.
    Here's what I'm trying to do. I have a procedure that does a table lookup to
    determine which stored procedure to execute. Once the stored procedure is
    chosen, the parameters to this procedure are always the same. The challenge
    I am facing is that some of these parameters are OUTPUT parameters and I
    have been unsuccessful passing an OUTPUT parameter to a stored procedure
    unless I know the name of the procedure at compile time. If the name of the
    stored procedure is determined dynamically I have been unable to pass it an
    OUTPUT parameter. Do you have any ideas on how I might do that? All
    suggestions will be appreciated. Thanks.

    Russ
    "Darious" <news@areyoukidding.com> wrote in message
    news:4ccni011quqe32hrjl1nkqfupba5tdn52a@4ax.com...
    > Hiya,
    >
    > I've done this in the past, the best method I came up with was the
    > build the entire exec spname parm1, parm2 etc into the nvarchar and
    > then call sp_executesql with the whole string, e.g.
    >
    > declare @strsql as nvarchar(100)
    >
    > set @qt = ''''
    > set @strsql = 'EXEC ' + @spname + @parm1 + ', ' @parm2
    >
    > exec sp_executesql @strsql
    >
    > If your parameters are strings then you'll have to include the '
    > character in the sql string to do this use '''' I usually store this
    > in a char(1) called @qt, it makes the string cleaner.
    >
    > Perhaps not he cleanest approach, but it will work.
    >
    > Darious
    >
    >
    > On Tue, 24 Aug 2004 19:45:51 GMT, "Russ Ferrill"
    > <rferrill@paracominc.com> wrote:
    >
    > >I've looked at the discussion of the execute command in BOL but it says
    > >nothing about passing parameters. I would like to use:
    > >EXEC ( @spname @parm1, @parm2 OUTPUT ) where @spname is the name of the
    > >stored procedure, @parm1 is an input parameter and @parm2 is an output
    > >parameter. When I try this I get an error saying that I need to declare
    > >@parm1, which I have done prior to executing the statment. If I use the
    same
    > >command with the actual name of the stored procedure in place of @spname
    it
    > >works just fine, but I really need to be able to select the procedure
    > >dynamically. If there is a way to do this I would greatly appreciate some
    > >suggestions. Thanks very much.
    > >
    > >Russ
    > >"Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
    > >news:%23nPdbqgiEHA.1644@tk2msftngp13.phx.gbl...
    > >> > I'd like to be abe to dynamically build a tsql command to execute a
    > >stored
    > >> > procedure that takes parameters. Is this possible?
    > >>
    > >> YES :)
    > >>
    > >> --
    > >> Roji. P. Thomas
    > >> Net Asset Management
    > >> https://www.netassetmanagement.com
    > >>
    > >>
    > >> "Russ Ferrill" <rferrill@paracominc.com> wrote in message
    > >> news:IkKWc.12845$Ib1.6074@newssvr22.news.prodigy.com...
    > >> > I'd like to be abe to dynamically build a tsql command to execute a
    > >stored
    > >> > procedure that takes parameters. Is this possible?
    > >> >
    > >> >
    > >>
    > >>
    > >
    >

  • Next message: Russ Ferrill: "Re: Passing a table or cursor into a stored procedure"

    Relevant Pages

    • RE: SQL stored procedure executing twice
      ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
      (microsoft.public.access.modulesdaovba)
    • RE: SQL stored procedure executing twice
      ... caused the stored procedure to execute twice. ... from one parent record to another. ... Dim stDocName As String ... The table tempCount does not change until I execute the VBA code line: ...
      (microsoft.public.access.modulesdaovba)
    • Re: regex to find an stored proc name
      ... exec this a, b, c ... execute ... The one I gave you will do this, and will put the Stored Procedure names ... Hard work is a medication for which ...
      (microsoft.public.dotnet.languages.csharp)
    • Re: EXECUTE permission denied on object... uh?
      ... Right click on the stored procedure ... Make sure the user in your conn string is allowed to EXECUTE ... with this user account as the group that has EXECUTE ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
      (microsoft.public.dotnet.framework.aspnet)
    • Calling a stored procedure with many arguments
      ... put the name of the procedure in a string ... CallableStatement statement=sqlconn.getConnection.prepareCall("{call ... execute the CallableStatement ... How do I call a stored procedure with more ...
      (comp.lang.java.databases)