Skip to Content.
Sympa Menu

freetds - Re: Using SQL Query Analyser to replicate functionality

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT speakeasy.org>
  • To: TDS Development Group <freetds AT franklin.oit.unc.edu>
  • Subject: Re: Using SQL Query Analyser to replicate functionality
  • Date: Tue, 08 Jan 2002 21:37:03 -0500


Hi James,

I think I know all the answers. To these questions, anyway. I really
should get out more.

James Cameron wrote:
> - what does the "exec sp_executesql N'" sequence mean?
> - why are the literal strings segregated from the command?

> begin tran
> exec sp_executesql N' update dbo.adp_tbl_oid
> set obj_num = obj_num + @U1
> where type_id = @U2
> select obj_num from dbo.adp_tbl_oid
> where type_id = @U3
> ', N'@U1 varchar(255),@U2 varchar(255),@U3 varchar(255)',
> @U1="1",@U2="77",@U3="77"
> commit tran

Man, is that ever ugly.

As Steve said, N'something' is just a way to define a literal string as
Unicode, not Ascii:

select 'something' returns a varchar
select N'something' returns an nvarchar

sp_executesql is a system stored procedure to execute dynamic SQL. I've
read about it; I don't use it and I don't see the point of it. On the
rare rare occasions I need dynamic SQL, I first take a long walk. If I
still think it's a good/necessary idea, I use
select @cmd = "my clever query"
execute (@variable)

If you think of everything inside the N'' string as a pseudo stored
procedure, you can see what they're doing with sp_executesql. The @U1,
@U2, and @U3 are parameters to the proc. Their types are provided by
the second argument to sp_executesql, and their values are provided by
the third argument string.

> - while not relevant to this example, if I don't see a "with (nolock)"
> clause in a query, does this prove it is not done?

To the extent that anything proves anything, yes.

I think the purpose of the begin/end tran is to let the select finish
before anyone else can update the @U2 rows. It's totally pointless if
@U2 and @U3 are different values, but I'm sure that's not the height of
mindlessness in this cockamamie scheme.

> Now, the complex transaction
>
> - is it possible to get FreeTDS to operate in this way in which multiple
> SQL commands are issued in a transaction and 'return' statements in the
> SQL prevent execution of following commands?

I don't see why FreeTDS would care about the SQL. What the server does
and when it returns isn't the concern of the pipe.

There are a couple of things that baffle me, though.

1. Tool and protocol notwithstanding, a RETURN can't appear in
freestanding SQL. It has to be in a stored procedure or trigger. I'd
expect a syntax error.

2. The return statements abandon the batch without commiting or rolling
back the transaction. That's a no-no. Of course, there might be some
supervisory code that checks @@trancount and commits/rollsback depending
on the error, and rl_wipbin2case has a chance to deal with part of it.
Or maybe there are some triggers-from-hell on table_act_entry and
table_time_bomb.

HTH. Give a holler if something's not clear.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page