×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I'm creating a stored procedure. I
2

I'm creating a stored procedure. I

I'm creating a stored procedure. I

(OP)
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.













RE: I'm creating a stored procedure. I

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.

RE: I'm creating a stored procedure. I

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: I'm creating a stored procedure. I

(OP)
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.

RE: I'm creating a stored procedure. I

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close