I'm creating a stored procedure. I 2

[ghbeers]

I'm creating a stored procedure. I have declared some @tables, and some @variables to handle variable years that are entered by the user. eg. @FAYEAR1 = <2013>, @FAYEAR2 = <2014>, etc. These years will be used for variable file suite table name that are TA_YYYY.

Then I have temp table
DECLARE @STUS_TO_USE TABLE
(
CRNT_STU VARCHAR(10),
STU_APROG VARCHAR(25)
)
and I insert the desired data from a select statement. Data is 100% correct.

I declare @TABLE_NAME And assign it the temp table name '@STUS_TO_USE'

I then declare and SET @SELSTRING = 'SELECT DISTINCT SU.CRNT_STU FROM ' + @TABLE_NAME + ' SU
JOIN TA_' + @FAYEAR1 + ' TA ON SU.CRNT_STU = SUBSTRING(TA.TA_' + @FAYEAR1 + '_ID,1,7)
WHERE (dbo.TA_AW_ID(TA.TA_' + @FAYEAR1 + '_ID, ' + @FAYEAR1 + ') LIKE ''G%X%''
OR (dbo.TA_AW_ID(TA.TA_'+@FAYEAR1 + '_ID, ' + @FAYEAR1 + ') LIKE ''L%X%'' ) AND TA.TA_TERM_AMOUNT > 0)'

Printing out the string, I get the correct select statement:
SELECT DISTINCT SU.CRNT_STU FROM @STUS_TO_USE SU
JOIN TA_2013 TA ON SU.CRNT_STU = SUBSTRING(TA.TA_2013_ID,1,7)
WHERE (dbo.TA_AW_ID(TA.TA_2013_ID, 2013) LIKE 'G%X%'
OR (dbo.TA_AW_ID(TA.TA_2013_ID, 2013) LIKE 'L%X%' ) AND TA.TA_TERM_AMOUNT > 0)

However when I execute @SELSTRING I get the error
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@STUS_TO_USE".

When I replace @SELSTRING with its printed out string (above), the selection works correctly.

Why is it not seeing my declared table @STUS_TO_USE?

Thank you.

********
After I sent this, I tried creating the table #STUS_TO_USE, and now get the error
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The column in the @table in which I am trying to insert the results from the dynamic string select is in the same format as the column in #STUS_TO_USE. I don't know what this message is saying.

 

[GHolden]

Hi, looks like your current database collation is different to the collation of the fields in the table you're joining to. So your temp table is created with the default current collation, but the one you're comparing to was created using a different one. You'll need to specify the collation on the temp table varchars when you create it to match the collation on the joined table.

There are two ways to write error-free programs; only the third one works.

 

[fredericofonseca]

try

create table STUS_TO_USE
(
CRNT_STU VARCHAR(10) collate database_default,
STU_APROG VARCHAR(25) collate database_default
)

On a different note why is one of your databases using Latin1_General_BIN - I´ve rarely seen any case where a whole database should be setup as such. Completely messes up sort orders and comparisons.

And the second one is that use of functions on the where clause. Again these should be avoided

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ghbeers]

Thank you both for getting back to me. I will check into the problems you see. And I can replace the use of the function, which is one of the 3-part key. Because of the values I'm looking for, I can use a substring instead.

Thank you again.

 

[ghbeers]

THANK YOU Frederico Fonseca for giving me the solution. It put me back in business. My insert and dynamic query is working beautifully. Thank you again.

Thank you to the first responder for also pointing out the real problem. Since there are many of the details of SQL I've not run across yet, I didn't know the solution. I was glad to get the solution from Frederico.