Help constructing Sql Statement/Stored Procedure
I'm not sure if this is possible but here what I'd like to do.
I need to write a stored procedure that takes in a single value (a SKU number). I have 8 tables in my database that all have a column called ItemSKU. I'd like to check all eight tables in one procedure and see if I can find a match to that single SKU number.
Is there a way I can check each table for a match and, if a match is found, stop the search and return the columns (name, price, stock) that I want?
I need to write a stored procedure that takes in a single value (a SKU number). I have 8 tables in my database that all have a column called ItemSKU. I'd like to check all eight tables in one procedure and see if I can find a match to that single SKU number.
Is there a way I can check each table for a match and, if a match is found, stop the search and return the columns (name, price, stock) that I want?
Rajkumar Gs
The columns name, price, stock should be returned from which table ?
I am not sure whether you are looking for this
This stored procedure will search all the eight tables connected based on ItemSKU column and search in Table1 for the column [SKU number] for the value that is passed to the Stored Procedure.
If you want only the first matching record, use TOP, otherwise remove.
Hope you got some logic from this query
This stored procedure will search all the eight tables connected based on ItemSKU column and search in Table1 for the column [SKU number] for the value that is passed to the Stored Procedure.
If you want only the first matching record, use TOP, otherwise remove.
Hope you got some logic from this query
CREATE PROCEDURE usp_SearchSKUNumber
(
@SKUNumber INT
)
AS
BEGIN
SELECT TOP 1 T1.name, T1.price, T1.stock
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.ItemSKU = T2.ItemSKU
INNER JOIN TABLE3 T3 ON T1.ItemSKU = T3.ItemSKU
INNER JOIN TABLE4 T4 ON T1.ItemSKU = T4.ItemSKU
INNER JOIN TABLE5 T5 ON T1.ItemSKU = T5.ItemSKU
INNER JOIN TABLE6 T6 ON T1.ItemSKU = T6.ItemSKU
INNER JOIN TABLE7 T7 ON T1.ItemSKU = T7.ItemSKU
INNER JOIN TABLE8 T8 ON T1.ItemSKU = T8.ItemSKU
WHERE T1.[SKU number] = @SKUNumber
END
GO
use this in your sp:
select top 1 * from (
select 1 priority, * from table1 where skuid=@skuid
union
select 2 priority, * from table2 where skuid=@skuid
...
select 8 priority, * from table8 where skuid=@skuid
) x order by priority
select top 1 * from (
select 1 priority, * from table1 where skuid=@skuid
union
select 2 priority, * from table2 where skuid=@skuid
...
select 8 priority, * from table8 where skuid=@skuid
) x order by priority
ASKER
Let me see if I can explain myself better. I'm trying to do something like this.
@Para_SKU = "1234"
Check Table1
SELECT name, price, stock FROM table1 Where skuid = @Para_SKU
Did it find a record (True/False)? FALSE
It's FALSE, so we check Table 2
SELECT name, price, stock FROM table2 Where skuid = @Para_SKU
Did it find a match (True/False)? FALSE
It's FALSE so we check Table 3
SELECT name, price, stock FROM table3 Where skuid = @Para_SKU
Did it find a match (True/False)? TRUE
Because it's TRUE, RETURN the data found. Do not continue.
That's what I'm trying to do. Once it finds a record in one of the tables the script stops and returns the matched column data it found.
@Para_SKU = "1234"
Check Table1
SELECT name, price, stock FROM table1 Where skuid = @Para_SKU
Did it find a record (True/False)? FALSE
It's FALSE, so we check Table 2
SELECT name, price, stock FROM table2 Where skuid = @Para_SKU
Did it find a match (True/False)? FALSE
It's FALSE so we check Table 3
SELECT name, price, stock FROM table3 Where skuid = @Para_SKU
Did it find a match (True/False)? TRUE
Because it's TRUE, RETURN the data found. Do not continue.
That's what I'm trying to do. Once it finds a record in one of the tables the script stops and returns the matched column data it found.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this would be better... did you try this? instead of checking and running lots of uqery?
for example if we find it on last table, above solution will run 16 query!!!! where as this one is only one...
select top 1 * from (
select 1 priority, * from table1 where skuid=@skuid
union
select 2 priority, * from table2 where skuid=@skuid
union
...
union
select 8 priority, * from table8 where skuid=@skuid
) x order by priority
result will be the same...
for example if we find it on last table, above solution will run 16 query!!!! where as this one is only one...
select top 1 * from (
select 1 priority, * from table1 where skuid=@skuid
union
select 2 priority, * from table2 where skuid=@skuid
union
...
union
select 8 priority, * from table8 where skuid=@skuid
) x order by priority
result will be the same...
HainKurt - I think there are pros and cons with two approaches. If the result is in first table, with UNION we are doing lot of unnecessary UNIONs. thoughts?
ASKER
Thank you. The IF EXISTS seams to work well.