• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

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
0
barkome
Asked:
barkome
  • 4
1 Solution
 
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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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:
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
 
barkomeAuthor Commented:
This was the solution for my scenario.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now