FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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, 

--jkl