0

I am using #temp table in two separate stored procedures. In one case, it is working ok while in other case it gives error ‘Invalid object name #temp’.

First case:

SELECT SubsID,
       SubsName,
       AbbrName
INTO   #TEMP
FROM   SubsList
WHERE  CAST (SubsID AS VARCHAR(10)) LIKE '%' + @intRight + '%'
        OR SubsName LIKE '%' + @intRight + '%'

  It is working ok.

In order to make this SQL more dynamic, I am using QUOTENAME and made following changes: 1.I made datatype of @ColName, @sourceName, @intField and @txtField as sysname. 2.In fact, I removed param @tableName as sysname (value: #temp was passed to it) as it gave error in Select and Drop SQL. 3.I am passing one field name in @ColName. It gave error when I passed all three field names. 4.I declare @cmd nvarchar(max) and assigned SQL to it for execution. 

SET @cmd = N'Select ' + QUOTENAME(@ColName) + 
           N' INTO #temp from ' + QUOTENAME(@sourceName) + 
           N' where CAST(' + QUOTENAME(@intField) + N' AS VARCHAR(10)) like ''%' + @strVal + 
           N'%'' or ' + QUOTENAME(@txtField) + ' like ''%' + @strVal + N'%''' --working
EXEC sp_executesql @cmd;

SELECT *
FROM   #temp;

DROP TABLE #temp;

I changed EXEC sp_executesql @cmd to EXEC(@cmd) but error remained.

I get error of invalid object name, but if I change #temp to ##temp, this error is not there.

My first question: What could be reason of this error in case of using #temp? Second question: How I can make array of field names and pass it to stored procedure?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Sushil Pugalia
  • 39
  • 4
  • 14
  • You should pass `@strVal` as a parameter to `sp_executesql` rather than simply concatenating it into the string that is executed to avoid SQL injection. – Martin Smith Jul 16 '12 at 14:29

1 Answers1

2

The temp table is out of scope after exec is done.'exec' or 'sp_executesql' are run in thier own scope. Thus anything created like temp tables and variables are destroyed or out of scope as soon as execution is over .Think these like stored proc.

To fix the issue.Create the temp table in main code.. then insert into it using dynmic sql and then read it in main code.

Gulli Meel
  • 891
  • 4
  • 6
  • Please visit the following link where EXEC and sp_executesql is explained in very nice way. Even examples are good to understand: http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong – Sushil Pugalia Jul 19 '12 at 02:59