Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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?
0
cdemott33
Asked:
cdemott33
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
The columns name, price, stock should be returned from which table ?
0
 
Rajkumar GsSoftware EngineerCommented:
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
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

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
cdemott33Author Commented:
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.

0
 
SharathData EngineerCommented:
try like this.
CREATE PROCEDURE usp_SearchSKUNumber
(
	@SKUNumber	INT
)
AS
BEGIN

if exists(select 1 from table1 where SKUNumber = @SKUNumber) select name,price,stock from table1 where SKUNumber = @SKUNumber
else if exists(select 1 from table2 where SKUNumber = @SKUNumber) select name,price,stock from table2 where SKUNumber = @SKUNumber
else if exists(select 1 from table3 where SKUNumber = @SKUNumber) select name,price,stock from table3 where SKUNumber = @SKUNumber
else if exists(select 1 from table4 where SKUNumber = @SKUNumber) select name,price,stock from table4 where SKUNumber = @SKUNumber
else if exists(select 1 from table5 where SKUNumber = @SKUNumber) select name,price,stock from table5 where SKUNumber = @SKUNumber
else if exists(select 1 from table6 where SKUNumber = @SKUNumber) select name,price,stock from table6 where SKUNumber = @SKUNumber
else if exists(select 1 from table7 where SKUNumber = @SKUNumber) select name,price,stock from table7 where SKUNumber = @SKUNumber
else if exists(select 1 from table8 where SKUNumber = @SKUNumber) select name,price,stock from table8 where SKUNumber = @SKUNumber
END
GO

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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...
0
 
SharathData EngineerCommented:
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?
0
 
cdemott33Author Commented:
Thank you. The IF EXISTS seams to work well.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now