the multi-part identifier "#tmpFullname.Item" could not be bound."

Question

The following code is supposed to take a string that may or may not be comma delimited and put it into a table (#tmpFullanme) that part works flawlessly. The second part is supposed return all the values that are LIKE / NOT LIKE with or without % symbols based on what is input. The error that I am getting is "the multi-part identifier "#tmpFullname.Item" could not be bound." The best guess I have is that it may be out of scope?

DROP PROCEDURE uspJudgments; 
GO 
CREATE PROCEDURE uspJudgments 
@fullName varchar(100), @SrchCriteria1 varchar(15), @SrchCriteria2 varchar(15), @qualifier varchar(10) 
AS 
BEGIN 

SELECT * 
INTO #tmpFullname 
FROM dbo.DelimitedSplit8K(@fullName, ',') 

DECLARE @Query NVarChar(1024) 
SET @Query = 'SELECT d.*' + ' FROM defendants_ALL d, #tmpFullname' + 
' WHERE d.combined_name' + ' ' + @qualifier + ' ' + '''' + @SrchCriteria1 + '''' + ' + ' + '''' + #tmpFullname.Item + '''' + ' + ' + '''' + @SrchCriteria2 + '''' 

END 

EXEC sp_executesql @Query 
PRINT(@Query) 

IF OBJECT_ID('#tmpFullname', 'U') IS NOT NULL 
DROP TABLE #tmpFullname 

EXEC uspJudgments @qualifier = 'LIKE', @fullName = 'johnson', @SrchCriteria1 = '%', @SrchCriteria2 = '%'

Cannot get to the PRINT output as "the multi-part identifier "#tmpFullname.Item" could not be bound." If I change #tmpFullname.Item to '#tmpFullname.Item it goes through and returns nothing but it shows that the query is correct minus the issue with that table.

SELECT d.* FROM defendants_ALL d, #tmpFullname WHERE d.combined_name LIKE '%' + '#tmpFullname.Item' + '%'

Please note that until I made this into a dynamic query so I can change the statement from LIKE to IN etc it worked very well.

From the error-message, it looks like it's O.K. with `#tmpFullname`, just not with `#tmpFullname.Item`. Try running `select * from tempdb.sys.columns where object_id = object_id('tempdb..#mytemptable');` (from [this StackOverflow answer](http://stackoverflow.com/a/756112/978917)) and printing the results; that will show you what column-names are in `#tmpFullname`. — ruakh, Oct 22 '12 at 23:20
I know what field names are in that table are Item and Itemnumber. I run SELECT * FROM #tmpFullname everytime I run the query. This works fine if its not dynamic sql. If I just run it without the dynamic LIKEs it works fine. — korrowan, Oct 22 '12 at 23:24
I know that that's what you expect; but SQL-Server doesn't seem to agree with you. (Is there some reason that you don't want to run this query?) — ruakh, Oct 22 '12 at 23:24
I ran the query and exactly what I expected was returned. Note that I ran that inside the stored procedure though. I created the table using the function and passed the values to the parameter and then checked. Is the issue that I am running that OUTSIDE of the dynamic SQL? — korrowan, Oct 22 '12 at 23:27
Re: "Note that I ran that inside the stored procedure though": Yup, that's what I had in mind. O.K., then, never mind, apparently this isn't the problem. :-P — ruakh, Oct 22 '12 at 23:40
Could you add two sets of input (with `in` and `like`) to the SP and the corresponding sql you want to execute dynamically? — Johan, Oct 29 '12 at 08:10
Maybe you can accept a solution. MUltiple are given and they all pinpoint to the same idea. — Mark Kremers, Oct 31 '12 at 16:43

Adam Wenger · Accepted Answer · 2012-10-29T21:08:54.527

5

I set up a full test to get the proper script to get you your desired results. I also have a SQL Fiddle showing how this works. Note You will want to run EXECUTE sp_executesql @Query inside the stored procedure

ALTER PROCEDURE uspJudgments @fullName varchar(100)
   , @SrchCriteria1 varchar(15)
   , @SrchCriteria2 varchar(15)
   , @qualifier varchar(10) 
AS 
BEGIN 

   --Simulates your split function
   SELECT *
   INTO #tmpFullName
   FROM
   (
     SELECT 'firstTest' AS Item
     UNION ALL SELECT 'secondTest'
     UNION ALL SELECT 'NotThere'
   ) AS t;

   DECLARE @Query NVARCHAR(1024);
   SELECT @Query = 'SELECT d.* '
      + ' FROM defendants_ALL d '
      + ' CROSS JOIN #tmpFullName AS t '
      + ' WHERE d.combined_name' + ' ' + @qualifier + ' '
      + '''' + @SrchCriteria1 + ''''
      + ' + ' + 't.Item' + ' + ' + '''' + @SrchCriteria2 + '''';

   EXECUTE sp_executesql @Query;

END

EXECUTE uspJudgments
@fullName = 'does not matter'
   , @SrchCriteria1 = '%'
   , @SrchCriteria2 = '%'
   , @qualifier = 'LIKE';

edited Oct 29 '12 at 21:08

answered Oct 22 '12 at 23:25

Adam Wenger

17,100
6
52
63

Had no luck with that either. It will not take the FROM clause I get: Msg 156, Level 15, State 1, Procedure uspJudgments, Line 59 Incorrect syntax near the keyword 'FROM'. – korrowan Oct 22 '12 at 23:42
@korrowan and you changed the SET to SELECT? – Adam Wenger Oct 22 '12 at 23:45
I did not do that but I just did and I am getting the same error "the multi-part identifier "#tmpFullname.Item" could not be bound." – korrowan Oct 23 '12 at 00:29
@korrowan I have updated my answer to provide you a more complete answer to the question. Note that you should be running the `EXECUTE sp_executesql @Query` inside the stored procedure. – Adam Wenger Oct 29 '12 at 21:10

score 1 · Answer 2 · answered Oct 23 '12 at 12:10

1

You have to use the tempdb prefix in this case

insert into tempdb..#TABLENAME

and

set @query = 'select * from tempdb..#TABLENAME'

answered Oct 23 '12 at 12:10

Mark Kremers

1,669
12
20

Same issue. The insert into line gets this message: Database name 'tempdb' ignored, referencing object in tempdb. And as far as the second I get the same error "The multi-part identifier "tempdb..#tmpFullname.Item" could not be bound.' – korrowan Oct 23 '12 at 12:50

score 1 · Answer 3 · answered Oct 29 '12 at 07:08

Well, After my last answer i have found several things.. When i look into your procedure you start with the "BEGIN", next you do a insert into the "#tmpFullName" table, your declare the "@Query" variable and create a select statement.

After that you do and "END" with logic after it. You do the "sp_executesql" after that you drop the temptable and you do and EXEC of the current procedure..

The Structure isn't all that readable, sorry to tell you. So maybe you go there first. Beside a strange structure you are using the "#tmpFullName.Item" in some dynamic SQL as a parameter, while it is declared inside the SQL query it self. So you have to do something like this :

SET @Query = 'SELECT d.*' + ' FROM defendants_ALL d, #tmpFullname' + 
  ' WHERE d.combined_name' + ' ' + @qualifier + ' ' + '''' + @SrchCriteria1 + '''' + ' + ' + ' #tmpFullname.Item ' + ' + ' + '''' + @SrchCriteria2 + ''''

Where the "#tmpFullName.Item" resides insde the code, not as a parameter. But then again, what are you trying to achieve here? To answer this completly we have to know what the other variables are. Your structure isn't telling me what are trying to achieve..

I really can't make any more out of it...

What I am doing is creating tmp tables from a function that takes a comma delimited string and puts each string into a row in a table. I then am trying to query everything in each of those tables against a table that has both first and last names in the same field. I then want to be able to use LIKE, NOT LIKE and the % signs etc to get the records based on what someone selects on the webpage. Qualifier is the LIKe/NOT LIKE / IN and SearchCriteria is the % sign. — korrowan, Oct 29 '12 at 11:53
That is what i thought! Just had to be sure. In your original dynamic query you are using the tmptable OUTSIDE the query, while the rest of the temptable is INSIDE the query. So when you would use my suggestion things should work, as far as i see it... — Mark Kremers, Oct 29 '12 at 12:20

the multi-part identifier "#tmpFullname.Item" could not be bound."

3 Answers3