FreeTDS Development Group

["ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.com>]

> 
> 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