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: "James K. Lowden" <jklowden AT schemamania.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Using sp_executesql with Microsoft SQL Server 2000
  • Date: Mon, 30 Jun 2003 19:57:21 -0400

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











Archive powered by MHonArc 2.6.24.

Top of Page