How to pass variables to a stored procedure's body statement?

Question

I have a table books.

+-------+-----------+------------+
| serial| book_name | book_author|
+-------+-----------+------------+
| 1     | Star Wars |     1      |
| 2     | Java      |     2      |
| 3     | Alimals   |     3      |
| 4     | Star Wars |     1      |
+-------+-----------+------------+

I want to use stored procedure to SELECT book_name;

My stored procedure looks like this:

USE books;
DELIMITER //
CREATE PROCEDURE doselect(col VARCHAR(50), tab VARCHAR(50), cond INT(5))
BEGIN
    SET @x = NULL;
    SET @col = col;
    SET @tab = tab;
    SET @cond = cond;
    SET @x = (SELECT @col FROM @tab WHERE b_id = @cond);

END //
DELIMITER ;

Then I call it:

CALL doselect('book_name', 'books', 3)

But id doesn't work. It doesn't see the variable @tab. The mistake is:

Table books.@tab doesn't exists.

I also used this variant of SELECT condition:

SET @x = (SELECT 'col' FROM 'tab' WHERE b_id = 'cond');

The same mistake but now without @:

Table books.tab doesn't exists.

How to pass variables right way in such a case? How to make it see my variables?

Thank you.

I believe you have to use dynamic SQL. Here's an example http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure — Chris Gessler, May 17 '12 at 11:36

score 2 · Accepted Answer · answered May 17 '12 at 11:39

construct the string with your select and all your variables and then Exec(string) to run your select.

CREATE PROCEDURE spName
    @Var VARCHAR(50)
AS
  SET NOCOUNT ON;

  DECLARE @sql VARCHAR(8000)

  SET @sql = 'SELECT
      X, Y + '' - '' + Z as ''Some alias''
FROM ' + @Var + '..TableName '

      exec (@sql)
GO

How to pass variables to a stored procedure's body statement?

1 Answers1