What am I missing here? - (Table Variable) [modified]

[Andy_L_J]

Maybe I have been at it too long today, but am I missing something simple here?

DECLARE @SomeLongName VarChar(100) = 'Some long text'    
DECLARE @temp TABLE(
    someCode VarChar(10)
    )
        
DECLARE @Sql VarChar(1000) = 
        'INSERT @temp SELECT MAX(SomeCode) ' +
        'FROM MyTable ' +
        'WHERE \[Name\] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''
        
EXEC (@Sql)

I get 'must declare the table variable @temp' when I try to run this? Please put me out of my misery... :-\ (edit: Small typo in Sql)

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

modified on Wednesday, October 13, 2010 3:52 AM

[Blue_Boy]

This way should works:

DECLARE @SomeLongName VARCHAR(100)
SET @SomeLongName = 'Some long text'
CREATE TABLE #temp
(
someCode VARCHAR(500)
)
DECLARE @Sql VARCHAR(1000)
SET @Sql = 'INSERT into #temp SELECT MAX(SomeCode) FROM MyTable WHERE [Name] LIKE ' +

CHAR(39) + LEFT(REPLACE(@SomeLongName, ' ', ''), 3) + ' %' + CHAR(39) + 
' select \* from #temp drop table #temp'

EXEC (@Sql)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Andy_L_J]

My sinvere thanks. :thumbsup:

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Blue_Boy]

You are welcome :)

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[Andy_L_J]

Now, what if i want to do this in a UDF? (Can't use temporary tables in a UDF :( )

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Mycroft Holmes]

I think you'll find you can't use dynamic sql in a UDF so the temp table is moot. As the boy was useful it is polite to up vote his answer [edit] I must have missed the vote [\edit].

Never underestimate the power of human stupidity RAH

[Corporal Agarn]

Because you are using the execute the temporary table needs to be in the database tempdb. I have even had to use CREATE TABLE ##Temp... to make an execute work.

[Andy_L_J]

This works fine in a SSMS query - it wont work in a UDF because of the dynamic sql. I think I may have to look at using a trigger or something...as I want to use the result to create a field when a new row is added to a table.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[www Developerof NET]

This one does execute.

DECLARE @SomeLongName VarChar(100) = 'Some long text'

DECLARE @Sql VarChar(1000) 
SET @Sql='DECLARE @temp TABLE( someCode VarChar(10) )' 
SET @Sql=@Sql+
        'INSERT @temp SELECT MAX(SomeCode) ' +
        'FROM MyTable ' +
        'WHERE \[Name\] LIKE''' + LEFT(REPLACE( @SomeLongName, ' ', ''), 3) + '%'''
           
EXEC (@Sql)

The problem here is the scope in which the sql statement executes. The @temp table variable declared is different from the @temp variable used in the inline query as the scope of both the queries are different. To make the query work we need to run the query in same scope ie declare the table variable using the same SQL satements as one used for the query.

When you fail to plan, you are planning to fail.