Skip to Content.
Sympa Menu

freetds - RE: [freetds] Using sp_executesql with Microsoft SQL Server 2000

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Using sp_executesql with Microsoft SQL Server 2000
  • Date: Tue, 1 Jul 2003 14:34:08 +0200

>
> On Mon, 30 Jun 2003 18:27:04 -0400, "Rogers, Tom"
> <tom.rogers AT ccur.com>
> wrote:
> > I tried the following (like your example but with one less var):
> > declare @a smallint
> > exec sp_executesql N'select @a=2',
> > N'@a int output', @a
> > select @a
> >
> > expecting it to work...but it did not.
>
> Tom,
>
> I see your confusion. You have to imagine what sp_executesql
> is doing, and be very familiar with parameter passing, to
> understand why that wouldn't work.
>
> Recall that for any stored procedure, you can pass parameters
> by name or position. For a simple procedure
>
> create proc a @a int as select @a as '@a'
>
> you could call it as:
>
> execute a 1
> or
> execute a @a=1
>
> In either case, you're passing a constant (1) to the procedure.
>
> A separate and distinct rule is that output parameters need
> "out[put]" twice to work. Modify things slightly
>
> create proc a @a int output as
> select @a=9
>
> if you call it as:
>
> execute a 1
> or
> execute a @a=1
>
> you're still passing a constant. The server won't attempt to
> write to it, though, even though the procedure declares the
> parameter for output, because it's being passed by value. To
> pass it by reference, you need the "output" keyword. Pretty
> much everyone tries this at least once:
>
> execute a @a=1 output
>
> and gets a complaint about not being able to write to a
> constant. The constant it's talking about is that 1, passed
> by value. (The @a is the name of the parameter.) The server
> is trying to do something like
>
> select 1=9
>
> which obviously won't do at all. Instead, you have to give
> it a variable to write to, the *caller's* variable, by reference:
>
> declare @b int
> execute a @b output -- or
> execute a @a=@b output
>
> passing @b by reference, either way.
>
> Now we see the logic inherent in the system. sp_executesql
> forms a stored procedure from its parameters, and then
> executes it. Your trial:
>
> > declare @a smallint
> > exec sp_executesql N'select @a=2',
> > N'@a int output', @a
> > select @a
>
> in effect passed @a by position and by value:
>
> 1. create proc X @a int output as
> select @a=2
> 2. declare @a smallint
> 3. execute X @a
>
> whereas what you wanted was:
>
> execute X @a output
>
> as in your successful test. :-) Note you have two @a
> variables -- the procedure's and the caller's -- and you have
> passed the caller's, by position.
>
> I don't know if any of this is any real use to you or the
> project, but I hope at least I've alleviated your confusion.
>
> Regards,
>

I think that you can use RPC directly and not pass names...

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page