Re: Using a parameter in a subquery

#1

In message <6F856C8E-4B8F-43D0-AC5C-39BC7B90BE50 (AT) microsoft (DOT) com>, Chad
Ehret <Chad (AT) Ehret (DOT) ?.microsoft.com.invalid> writes

Quote:

I'm working on DTS package to copy all data related to a particular
client from a production server (SQLServer 2000), to a development
server (also SQL Server 2000). I've set up the package on the
destination server. I'm trying to set up DTSDataPump tasks to copy
subsets of the client information to the development server using a
queries with a single parameter as the source. Where I run into
trouble is when I have to have that parameter as part of a
SUBQUERY...DTS apparently doesn't using parameters in subqueries...I
haven't been able to figure out a way around this yet.

Sample SQL:
SELECT *
FROM tblClaimActions
WHERE ClaimID IN (SELECT ClaimID FROM tblClaims WHERE ClientID = ?)

If I replace the "?" with a value (e.g. 153), it works fine for pulling
the Actions related to all claims for client 153.

There are several tables I have to do this sort of thing for, so I'd
like to be able to set up a global variable, map the query parameter(s)
to that variable in each task. This will allow me to change the target
ClientID in one place (the global variable) and transfer the only the
data I'm after.

There are a couple of options that may work-

Firstly you could re-write this particular query to use an inner join
instead which may allow you to use the parameter place holder.

Another option focuses on the fact that the design-time validation is
stricter than the run-time, so you can fool the designer into setting up
the parameter mapping on some simple SQL and then swap in the real code
later. This may or may not work in this situation, but the technique is
described in the later part of this article-
Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)

Finally you could just use the old method, of coding the parameter in
the SQL statement, but maintain the entire statement dynamically. See-
Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Using a parameter in a subquery

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Using a parameter in a subquery - 07-14-2004 , 04:19 PM