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

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
barkomeAsked:
Who is Participating?
 
barkomeConnect With a Mentor Author Commented:
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
 
Phil DavidsonCommented:
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
 
barkomeAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
SharathData EngineerCommented:
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
 
barkomeAuthor Commented:
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
 
barkomeAuthor Commented:
This was the solution for my scenario.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.