Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-13 : 13:05:55
|
| Bryan writes "I've put a serious dent in my brain and am hoping you can help me out...(I'm running SQL SERVER 2000 SP2)While looping through a cursor, I need to reference a table name in a SELECT statement using a variable. If I try to reference a table name by variable directly in the SELECT statement, I get the following error: Must declare the variable '@parenttable'. I understand that to accomplish this I can build a string and then use an approach like the following: DECLARE @ParentSQL varchar(200)SET @ParentSQL = 'SELECT ' + @NewKey + ' FROM ' + @parenttable + ' WHERE ' + @parentkey + ' = ' + @oldid DECLARE ParentCursor CURSOR FORSelect @ParentSQLOPEN ParentCursor This works fine in most instances but the problem is that I need to reference a field value with a variable which is stored in @oldid and it is of type binary(8) and there appears to be a concatenation issue when trying to append a binary data type to a string. I've tried using CAST and CONVERT without success. I get the following error when executing the example above: Invalid operator for data type. Operator equals add, type equals varchar. This makes sense to me but I don't know what I can use as a work-around. If I need to do a simple SELECT statement while referencing a binary type I have no problems using a direct statement like the following as a test: DECLARE @mybinary binary(8)SET @mybinary = 0x000000000000414ASelect Bid_Id From Bid Where Bid_Id = @mybinary The above works fine. It's when I have to reference a table by variable name AND reference a field value by variable name when the field is of a binary data type in the same SELECT statement. I have had no problems using ALTER TABLE statements using Exec ( 'ALTER TABLE ' + ...) with string concatenation while referencing table names with variables. Can you help???" |
|
|
|
|
|
Referencing a table name by variable and binary field value by variable