FreeTDS Development Group

["Rogers, Tom" <tom.rogers AT ccur.com>]

Thanks...I am not a newbie to SPs but I do appreciate the depth of your
explanation.  I had the one 'output', it just didn't occur to me to add the
second, but it makes perfect sense.  The "syntax" of still seems very
strange, the double indirection nature and having to pass, and declare and
pass the vars twice is no doubt a necessary evil, but seems really strange
as I said.

Thomas Rogers
System Engineer
Concurrent Computer Corporation
> phone: 215.712.7422 x7261


-----Original Message-----
From: James K. Lowden [mailto:jklowden AT schemamania.org]
Sent: Monday, June 30, 2003 7:57 PM
To: FreeTDS Development Group
Subject: Re: [freetds] Using sp_executesql with Microsoft SQL Server
2000


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







_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds