freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: Using SQL Query Analyser to replicate functionality
- 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
-
Using SQL Query Analyser to replicate functionality,
James Cameron, 01/08/2002
- <Possible follow-up(s)>
- Re: Using SQL Query Analyser to replicate functionality, Michael Peppler, 01/08/2002
- Re: Using SQL Query Analyser to replicate functionality, Steve Langasek, 01/08/2002
- Re: Using SQL Query Analyser to replicate functionality, James K. Lowden, 01/08/2002
Archive powered by MHonArc 2.6.24.