Parsing T-SQL is not validating table schemas

I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.

It seems that the parser does not validate that a tables schema is missing.

This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.

CREATE PROCEDURE [dbo].[Address_Load]
@.AddressID [int]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.intError int

BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @.AddressID

IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END

-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @.intError = ERROR_NUMBER();

-- Log error here

RETURN @.intError;
END CATCH
END

The stored proc parses fine and gets saved to the database but when executing it I get the following

Msg 208, Level 16, State 1, Procedure Address_Load, Line 10

Invalid object name 'Address'.

Is there any way to change this so the parsing will generate an error and not allow this into the database?

Thanks,

Cory

The behavior is due to deferred name resolution/compilation of TSQL modules in SQL Server. You can look it up in BOL for more details. See link below for starters:

http://msdn2.microsoft.com/en-us/library/ms190686.aspx

|||

And when you decide you don't like the behavior, please go here and vote!

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It is good about 1% of the time to be like this. However, the problem is the 2% of the time when you are coding and mistype a table name and it still compiles, only to find out later when you are (hopefully) testing :)

|||Is there a way to turn off Deferred Name Resolution?|||

Nope. That is what this feedback that I mentioned:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490

It really ought to be a settting you can ask for, not soemthing that is on all of the time.

parametrised servername and catalog name

Hi,

I intend to use four-part table names to select data from a Linked Server into local tables. There will be stored procedures containing Insert-Select statements.

While I'm developing, I'm pointing to a development version of the remote server. In production, the remote server will be different. There will be many other situations, where I will need to link to one remote server or another. But I don't want to recompile my stored procs every time.

The question is: can I use variables for the first two parts of a four-part table name. Something like:

declare @.svr varchar(20)
declare @.dsn varchar(20)
select @.svr = 'Pervasive_Test', @.dsn = 'D_drive'
SELECT * from @.svr.@.dsn..remote_table

Would the above work ?

I have to go through the ODBC Provider. The only way my query works is when I also specify the DSN as "catalog" in the second position of the table name. I definitely do not want the name of the DSN to be hardwired into my stored procs.

Andrewyour example wont work, though you can use
sp_executesql (see BOL) to run commands with variable object names,

eg.

set @.statement = N'select user_id from users
where cc_number = '+''''+ltrim(rtrim(@.cc_number))+''''

insert into #master_id (user_id)
exec sp_executesql @.statement

though for the sort of thing you are doing, I usually create a batch file which uses a tool like SED to replace each token (defined earlier on object names) with the desired values , and then use the relevant modified script for each installation...just have the bat file receive the server/tablname as params

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
Yahya
Yahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>
|||That feature is called "In-line table valued functions" in SQL Server. User defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parametric Views

Can I create parameterized view, in other words like stored procedures where
by I can declare a variable and assign to it values and use it in my
criteria?
Thanks,
YahyaYahya
No, you can SELECT <column lists>from a view WHERE col=@.par ( I 'm sure you
knew it)
"Yahya" <x@.x.x> wrote in message
news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...
> Can I create parameterized view, in other words like stored procedures
where
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>|||That feature is called "In-line table valued functions" in SQL Server. User
defined functions are
documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yahya" <x@.x.x> wrote in message news:OOFwHt%23FFHA.2692@.TK2MSFTNGP10.phx.gbl...een">
> Can I create parameterized view, in other words like stored procedures whe
re
> by I can declare a variable and assign to it values and use it in my
> criteria?
> Thanks,
> Yahya
>

Parameters in Stored Procedures problem

I have 2 stored procedures, both are listed below. The
first Stored Procedure takes 8 parameters and runs and
update query using those parameters. This procedure
takes about 3 minutes to run. The second procedure
doesn't take any parameters, but declares the same 8
variables at the start of the procedure and sets them to
same valuse that I used when I ran the first procedure
and this one takes less than a second to run.
Just a little more information here is the size of the
tables:
aggQuotaAtt 135194
txoQuota: 135194
mstrsCustomer: 19741
So my question is why does the same procedure take
radically different when I use parameters.
Here is the first procedure which takes 3 minutes:
ALTER PROCEDURE [dbo].[Paul'sJunk]
@.PrdID varchar(18),
@.ShipToID int,
@.PoolYearPeriodID int,
@.AorID int,
@.Aor0ID int,
@.Aor1ID int,
@.Aor2ID int,
@.Aor3ID int
as
print getDate()
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
and here is the second procedure. This is the one that
takes less than a second:
ALTER PROCEDURE [dbo].[Paul'sJunk2]
as
DECLARE @.PrdID varchar(18)
DECLARE @.ShipToID int
DECLARE @.PoolYearPeriodID int
DECLARE @.AorID int
DECLARE @.Aor0ID int
DECLARE @.Aor1ID int
DECLARE @.Aor2ID int
DECLARE @.Aor3ID int
SET @.PrdID = '20027-933'
SET @.ShipToID = 0
SET @.PoolYearPeriodID = 73
SET @.AorID = 1
SET @.Aor0ID = NULL
SET @.Aor1ID = NULL
SET @.Aor2ID = NULL
SET @.Aor3ID = NULL
UPDATE aggQuotaAtt SET
OrderQty = txoQuota.OrderQty,
Revenue = txoQuota.Revenue,
HistoricalPercentage = txoQuota.HistoricalPercentage,
CurrentPercentage = txoQuota.CurrentPercentage,
ModifiedDate = GetDate(),
AorID = mstrsCustomer.AorID,
Aor0ID = mstrsCustomer.Aor0ID,
Aor1ID = mstrsCustomer.Aor1ID,
Aor2ID = mstrsCustomer.Aor2ID,
Aor3ID = mstrsCustomer.Aor3ID,
Aor4ID = mstrsCustomer.Aor4ID
FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
QuotaBase.ShipToID = txoQuota.ShipToID
AND QuotaBase.PrdID = txoQuota.PrdID
AND QuotaBase.PoolYearPeriodID = txoQuota.PoolYearPeriodID
INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
mstrsCustomer.ShipToID = txoQuota.ShipToID
WHERE txoQuota.PrdID = @.PrdID
AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
AND (txoQuota.ShipToID = @.ShipToID OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
txoQuota.ShipToID IN (SELECT ShipToID
FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))Jory asked effectively the same question yesterday in the .programming
group - the same responses apply.
"Paul Cavacas" <pcavacas@.oceanspray.com> wrote in message
news:08f301c36327$a1e66090$a601280a@.phx.gbl...
> I have 2 stored procedures, both are listed below. The
> first Stored Procedure takes 8 parameters and runs and
> update query using those parameters. This procedure
> takes about 3 minutes to run. The second procedure
> doesn't take any parameters, but declares the same 8
> variables at the start of the procedure and sets them to
> same valuse that I used when I ran the first procedure
> and this one takes less than a second to run.
> Just a little more information here is the size of the
> tables:
> aggQuotaAtt 135194
> txoQuota: 135194
> mstrsCustomer: 19741
> So my question is why does the same procedure take
> radically different when I use parameters.
> Here is the first procedure which takes 3 minutes:
> ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
> as
> print getDate()
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
> and here is the second procedure. This is the one that
> takes less than a second:
> ALTER PROCEDURE [dbo].[Paul'sJunk2]
> as
> DECLARE @.PrdID varchar(18)
> DECLARE @.ShipToID int
> DECLARE @.PoolYearPeriodID int
> DECLARE @.AorID int
> DECLARE @.Aor0ID int
> DECLARE @.Aor1ID int
> DECLARE @.Aor2ID int
> DECLARE @.Aor3ID int
> SET @.PrdID = '20027-933'
> SET @.ShipToID = 0
> SET @.PoolYearPeriodID = 73
> SET @.AorID = 1
> SET @.Aor0ID = NULL
> SET @.Aor1ID = NULL
> SET @.Aor2ID = NULL
> SET @.Aor3ID = NULL
> UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage => txoQuota.HistoricalPercentage,
> CurrentPercentage => txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
> FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
> QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID => txoQuota.PoolYearPeriodID
> INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
> mstrsCustomer.ShipToID = txoQuota.ShipToID
> WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
> FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))|||can't say about the whole thing, but
what are the differences in the execution plan?
i have concern the select queries:
SELECT ShipToID FROM WaveAgg.dbo.mstrsCustomer WHERE
Aor0ID = @.Aor0ID
etc, where @.AorxID value is NULL,
this query should be a simple index lookup
but i have situations where the NULL value in the where
clause resulting in a table scan
>--Original Message--
> I have 2 stored procedures, both are listed below. The
>first Stored Procedure takes 8 parameters and runs and
>update query using those parameters. This procedure
>takes about 3 minutes to run. The second procedure
>doesn't take any parameters, but declares the same 8
>variables at the start of the procedure and sets them to
>same valuse that I used when I ran the first procedure
>and this one takes less than a second to run.
> Just a little more information here is the size of the
>tables:
>aggQuotaAtt 135194
>txoQuota: 135194
>mstrsCustomer: 19741
> So my question is why does the same procedure take
>radically different when I use parameters.
>Here is the first procedure which takes 3 minutes:
>ALTER PROCEDURE [dbo].[Paul'sJunk]
> @.PrdID varchar(18),
> @.ShipToID int,
> @.PoolYearPeriodID int,
> @.AorID int,
> @.Aor0ID int,
> @.Aor1ID int,
> @.Aor2ID int,
> @.Aor3ID int
>as
>print getDate()
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>
>
>
>and here is the second procedure. This is the one that
>takes less than a second:
>ALTER PROCEDURE [dbo].[Paul'sJunk2]
>as
>DECLARE @.PrdID varchar(18)
>DECLARE @.ShipToID int
>DECLARE @.PoolYearPeriodID int
>DECLARE @.AorID int
>DECLARE @.Aor0ID int
>DECLARE @.Aor1ID int
>DECLARE @.Aor2ID int
>DECLARE @.Aor3ID int
>SET @.PrdID = '20027-933'
>SET @.ShipToID = 0
>SET @.PoolYearPeriodID = 73
>SET @.AorID = 1
>SET @.Aor0ID = NULL
>SET @.Aor1ID = NULL
>SET @.Aor2ID = NULL
>SET @.Aor3ID = NULL
>UPDATE aggQuotaAtt SET
> OrderQty = txoQuota.OrderQty,
> Revenue = txoQuota.Revenue,
> HistoricalPercentage =>txoQuota.HistoricalPercentage,
> CurrentPercentage =>txoQuota.CurrentPercentage,
> ModifiedDate = GetDate(),
> AorID = mstrsCustomer.AorID,
> Aor0ID = mstrsCustomer.Aor0ID,
> Aor1ID = mstrsCustomer.Aor1ID,
> Aor2ID = mstrsCustomer.Aor2ID,
> Aor3ID = mstrsCustomer.Aor3ID,
> Aor4ID = mstrsCustomer.Aor4ID
>FROM aggQuotaAtt QuotaBase INNER JOIN txoQuota ON
>QuotaBase.ShipToID = txoQuota.ShipToID
> AND QuotaBase.PrdID = txoQuota.PrdID
> AND QuotaBase.PoolYearPeriodID =>txoQuota.PoolYearPeriodID
>INNER JOIN WaveAgg.dbo.mstrsCustomer mstrsCustomer ON
>mstrsCustomer.ShipToID = txoQuota.ShipToID
>WHERE txoQuota.PrdID = @.PrdID
> AND txoQuota.PoolYearPeriodID = @.PoolYearPeriodID
> AND (txoQuota.ShipToID = @.ShipToID OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE AorID = @.AorID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor0ID = @.Aor0ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor1ID = @.Aor1ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor2ID = @.Aor2ID) OR
> txoQuota.ShipToID IN (SELECT ShipToID
>FROM WaveAgg.dbo.mstrsCustomer WHERE Aor3ID = @.Aor3ID))
>.
>

parameters in sql server 2000

Hi!

I need how to pass variables struct witt n fields and n records
to procedures in transac sql in one bbdd sql server 2000,
this parameters are in/out.

Thanks.Sounds like an array:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--