Prev: Multiple sessions when using ADO
Next: SQL Command question
From: Pat on 2 Jun 2010 18:11
Hi Friends,
The point is that I'm creating a sql string and executing it with
sp_executestring command, how can I use GO command in my sql string
construction, let's say if I want ot use different databases then I
need to have something like
USE DB1
GO
USE DB2
GO
....

Thanks in advance,
Pat
From: Jeroen Mostert on 2 Jun 2010 18:30
On 2010-06-03 0:11, Pat wrote:
> The point is that I'm creating a sql string and executing it with
> sp_executestring command, how can I use GO command in my sql string

You can't. "GO" is not an SQL command, it's the batch terminator for
Management Studio and sqlcmd. You cannot execute multiple batches with a
single sp_executesql call.

> construction, let's say if I want ot use different databases then I
> need to have something like
> USE DB1
> GO
> USE DB2
> GO
> ...
>
You don't have to follow a USE statement with GO, just leave it out.

If you do need to end the batch and start a new one (for example, you want
to execute a CREATE PROCEDURE statement, which must be the first statement
in a batch) you'll have to use a new sp_executesql statement. You can split
your string on "GO" and execute the parts individually. Splitting strings in
T-SQL is a bit involved, though -- Google around and you'll find multiple
solutions, for example
http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor
(solutions that can split on something more complicated than single
characters are discussed a little bit in).

Alternatively, simply feed your commands to sqlcmd. Its purpose is to
execute batches of SQL this way, so it may be more appropriate than
sp_executesql (depending on what you're doing and where your statements are
coming from).

--
J.
 | 
Pages: 1
Prev: Multiple sessions when using ADO
Next: SQL Command question