Dynamic SQL Statement with Temporary Tables

[YaBabyYa]

Hello,

I am using SQL 2000 to test the query.

I have a Temporary Table: #Temp_Word_List
And a second table: #t

I am dynamically executing a select query on #Temp_Word_list
The select query is implemented as an Insert into a Temporary Table #t and then I try to read the temporary table #t.

The query is failing to retrieve the result from #Temp_Word_list into #t and always returns 0

I checked the #Temp_word_List in the query analyzer output and it has the right values but the output of #t always shows 0






Code Snippet as follows:

set @curr_char = UPPER(substring(@unique_word_string , @Len+1, 1))
delete from #t
set @temp_query_string_part_1 = ' from #Temp_word_list where KeywordID = '
set @temp_query_string_part_2 = @curr_KeywordID
set @temp_query_string_part_1 = @temp_query_string_part_1 + @temp_query_string_part_2
set @temp_query_string = 'Insert into #t Select ' + @curr_char + @temp_query_string_part_1
Print ' String is ' + @temp_query_string

Exec ( @temp_query_string )
Select @curr_char_rank = IsNull(num_sample,0) from #t
Select * from #t





*****************************************
OUTPUT QUERY As Showing in Print output:
*****************************************

Insert into #t Select T from #Temp_word_list where KeywordID = 2



******************************************
#Temp_word_list
******************************************

Column Name ----------- Column Type
--------------------------
KeywordID ---------- int
T ----------------- float
--------------------------

1 Record: KeywordID = 2 T = 0.5


******************************************
# t
******************************************

1 Column and 1 Row = num_sample
Type = float
Value = 0

Value should be 0.5
******************************************

 

[bborissov]

What is the structure of #t table?
Did you get any errors?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[YaBabyYa]

One additional piece of information

The original dynamic query is comprised of two parts.

'Insert into #t Select ' + @curr_char + @temp_query_string_part_1


Select ' + @curr_char + @temp_query_string_part_1

Output: Select T from #Temp_word_list where KeywordID = 2

This query returns the correct output

the problem is that the Insert into #t seems to fail

 

[YaBabyYa]

the Structure of the #t table is:

Column Name: num_sample
Column Type: Float

Number of rows = 1


Table Definition Code:

create table #t (num_sample int)

 

[YaBabyYa]

I don't get any exceptions from the debugger. Just the value is coming back as 0 .. that is the error.

 

[monksnake]

If you are creating a temp table with 1 row and 1 column, why not just put that into a regular variable??

<.

 

[bborissov]

That works for me:

CREATE TABLE #T (num_sample float)
CREATE TABLE #Temp_word_list (KeywordID int, T float)
INSERT INTO  #Temp_word_list VALUES (2,0.5)

DECLARE @test varchar(200)
SET @test = 'Insert into #t Select T from #Temp_word_list where KeywordID = 2'
EXEC (@test)
SELECT * from #t
DROP TABLE #T
DROP TABLE #Temp_word_list

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[YaBabyYa]

I have solved the problem. The table #t was declared as an INT column. My code required a float as a result the decimal input was being cast to 0.

Thanks.