0

I am not sure how to get the syntax of this right.

So I have a cursor that loops through a table User.Persona and captures the Id column. So each Id is stored into the variable cur_id with every iteration of the cursor. And each Persona Id will correspond to a Persona Id Table. For example Persona ID 1 has a Persona1 table, Persona Id 2 has a Persona2 table.

So in that cursor, I want to call the table SELECT....FROM @Persona# WHERE...

How do I do this?

I've tried storing the string of the table name in a variable first:

DECLARE cur_table VARCHAR(25);

And inside the cursor: 

SELECT CONCAT('persona', @cur_id) INTO @cur_table
SELECT .... FROM **@cur_table** WHERE...

However, the FROM @cur_table throws an error and I cannot reference my string that holds the current table name.

Is there a way for me to reference the changing table names within my cursor?

Thanks!

truffle
  • 455
  • 1
  • 9
  • 17
  • you can't use a variable like that. you'd have to build a string of query, then exec it. e.g. `@sql = concat("SELECT ... FROM ", @cur_table, " blah blah blah "); exec @sql;` – Marc B Jul 18 '16 at 20:04
  • Why are you using a cursor? Are you new to sql? – Drew Jul 18 '16 at 20:57
  • 1
    Marc is basically right, but for mysql, `exec @sql;` has to be `prepare stmt from @sql; execute stmt;` – Solarflare Jul 18 '16 at 21:04
  • regardless, don't use cursors – Drew Jul 18 '16 at 21:49
  • And normalise your schema – Strawberry Jul 18 '16 at 21:52
  • What's wrong with cursors? Do you use while loops instead to iterate through columns of a table? – truffle Jul 18 '16 at 21:52
  • It is not a stretch of the imagination that cursors can be 100 times slower. When you get done doing it with cursors, publish it all up with the schema. And if it falls into the category of what I think it will, I am sure I can do it a ton faster. – Drew Jul 19 '16 at 00:16
  • Don't worry about the doom and gloom re: cursors in SQL; sometimes they are needed, just like `goto` is sometimes needed in other languages. Cursors just shouldn't be your first/only choice to do something, that's all. – TylerH Sep 08 '22 at 13:49

0 Answers0