?
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
Medium Priority
?
66 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 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