Solved

Stored Proc using multiple values from temp table to find exact match in another table and return corresponding ID...

Posted on 2016-08-04
9
34 Views
Last Modified: 2016-08-14
Hello

I have a temp table (#Table_A) that stores the following data:

ID	STRING_VALUE        SCHEMA	                TABLE
5	%4567-9875%	         TEST	                TEST_TABLE
8	%15987563%	         TEST	                TEST_TABLE
11	%98675412358%     TEST	                TEST_TABLE
56	%00032568974%     TEST	                TEST_TABLE

Open in new window


I also have a stored procedure that does a loop through rows of another table (Table_B) for a key value. This is the syntax for executing the SP if hard coded:
EXEC @Rows = [TEST].[FindStringInTable] '%00032568974%', 'TEST', 'TEST_TABLE'  
if @Rows  = 1 return 56

Open in new window


Is there a way, that I can get the stored procedure to use multiple values from STRING_VALUE column of #Table_A and use them to determine if a match in table_B, and return the corresponding ID of #Table_A.

Below is the snipet of the flow. Blue arrows showing multiple values for the SP to use, and the red arrow showing what value should be returned once a match exist.

sp-input.JPG
0
Comment
Question by:barkome
  • 4
9 Comments
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 41743535
I would try this:

SELECT ID FROM #TableA a
WHERE exists (SELECT STRING_VALUE FROM TableB b 
where b.STRING_VALUE = a.STRING_VALUE)

Open in new window

0
 

Author Comment

by:barkome
ID: 41743574
Thanks, but not sure this will work in my case because table_b is not normalized:

The SP is currently executed from another parent SP, which is hard coded like this when created:

CREATE PROCEDURE [TEST].[Find_ID_TEST]
   @String varchar(50)
 , @Schema varchar (50)
 , @Table varchar (50)
AS
SET NOCOUNT ON

DECLARE @Rows int

EXEC @Rows = [TEST].[FindStringInTable]  '%4567-9875%', 'TEST', 'TEST_TABLE'   
if @Rows  = 1 return 5

EXEC @Rows = [TEST].[FindStringInTable]  '%15987563%', 'TEST', 'TEST_TABLE'    
if @Rows  = 1 return 8

EXEC @Rows = [TEST].[FindStringInTable]  '%98675412358%', 'TEST', 'TEST_TABLE'  
if @Rows  = 1 return 11

EXEC @Rows = [TEST].[FindStringInTable] '%00032568974%', 'TEST', 'TEST_TABLE'  
if @Rows  = 1 return 54

Open in new window


Which I have created a temp table
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41743765
It depends on how the SP FindStringInTable is created. Does it handle multiple string_values? Post the definition of the SP and lets modify to your need.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:barkome
ID: 41744157
Sure, SP FindStringInTable handles one string_value at a time, and when there is a match, the ID is returned as a return value for input to another process. Will like the SP to return multiple values if there is more than one string_value match, as I will be using the output to be stored in an Object variable within SSIS.

CREATE PROCEDURE [TEST].[FindStringInTable] @string VARCHAR(100), @schema sysname, @table sysname, @returnVal int = null output
AS

BEGIN TRY
   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' 
                  
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''' + @string + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   EXEC (@sqlCommand)
   return @@rowcount;
--   PRINT @sqlCommand
END TRY

BEGIN CATCH 
   PRINT 'There was an error. Check to make sure object exists.'
   PRINT error_message()
END CATCH

Open in new window

0
 

Accepted Solution

by:
barkome earned 0 total points
ID: 41749282
Solution:

  • Created temp table to store the values to look up
  • Created a cursor to loop through temp table
  • Passed values for each row in the loop required as an input into the stored procedure
  • Insert required values (ID) into table.

Tied everything into a store procedure, there could be a better way, but this happened to work for my scenario:

CREATE PROCEDURE [TEST].[FindStringID] 
AS
SET NOCOUNT ON
--created a staged table to store values (ID) for each session the procedure is initiated.
TRUNCATE TABLE [TEST].STORED_ID -- truncate STORED_ID's staging table in TEST.STORED_ID
--drop client ID temp table used to store available client records from Table A
IF OBJECT_ID('tempdb..#CLIENT_ID') IS NOT NULL DROP TABLE #CLIENT_ID
SELECT ROW_NUMBER() OVER (ORDER BY A.ID)ROW_NUM, A.* INTO #CLIENT_ID
FROM TABLE A 
WHERE A.ID <>''
--declare variables here
DECLARE @id INT, @stringToFind VARCHAR(50), @schema VARCHAR (50),@table VARCHAR (50), @Rows int
--declare cursor here
DECLARE ID_Cursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT ID, STRING_TO_FIND, SCHEMA , TABLE FROM #CLIENT_ID
OPEN ID_Cursor
FETCH NEXT FROM ID_Cursor INTO @id, @stringToFind ,@schema ,@table
WHILE @@FETCH_STATUS = 0 BEGIN
-- execute stored procedure to find string
	EXEC @Rows = [TEST].[USP_FindStringInTable] @stringToFind, @schema ,@table
-- select a return value for id for found strings anD insert into TEST.STORED_ID
INSERT INTO [TEST].STORED_ID 
 select @ID as STORED_ID  where @Rows = 1
    FETCH NEXT FROM ID_Cursor INTO @id, @stringToFind ,@schema ,@table

END

CLOSE ID_Cursor
DEALLOCATE ID_Cursor

Open in new window

0
 

Author Closing Comment

by:barkome
ID: 41755362
This was the solution for my scenario.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Temporarily disable SQL Replication 7 23
SQL Query Syntax Error 9 34
Query Missing Money orders... 6 73
Complex SQL 10 34
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now