I gang

TSQL stored procedure

I need a stored procedure (T-SQL, MSSQL 2005) that returns a list of availible addresses from a database.

buy_orders_pallets (table 1) (contains what clients want to buy)

bid int <- passed as parameter to SP

pid int

amount int

isdry bit

customer_avi (table 2) (contains what clients want to sell)

pid int <- pallet id

aid int <- address id (This is what I want returned)

amount int

isdry bit

Here is the two tables, I want a SP that takes 1 parameter (bid) as input. use this bid to get rows from table 1

Then return all address id (aid) from table 2 where

ALL of the following criteria is met.

1. all pid from table 1 is inside table 2 (matching the aid)

2. amount is equal or greater in table 2 than in table 1

3. isdry is equal

Example data below would on input bid= 1 return values 1 and 4

Table 1

bid pid amount isdry

1 1 5 true

1 2 7 false

1 5 2 true

Table 2

pid amount isdry aid

1 10 true 1

2 7 false 1

5 2 true 1

1 150 true 2

5 20 true 2

1 110 true 3

2 70 true 3

5 21 true 3

1 15 true 4

2 75 false 4

5 21 true 4

Evner: Databehandling

Se mere: sql stored procedure return boolean, return boolean stored procedure sql, tsql stored procedure return table, tsql return table stored procedure, tsql return boolean, sql true, tsql stored procedure, tsql return true, sql stored procedure boolean input, sql return true false stored procedure, tsql stored proc returns boolean, sql return bool stored procedure, sql boolean stored procedure, tutorial tsql stored procedure, tsql stored procedure boolean, sql stored procedure return rows, sql return true false, sql return true, sql stored procedure boolean, tsql stored procedure return bool, stored procedure return table, tsql return bool, SQL STORED PROCEDURE, tsql return, sql stored procedure return true

Om arbejdsgiveren:
( 26 bedømmelser ) Rimbo, China

Projekt ID: #294594