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
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 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