SQL 2000 Table Name as variable in stored procedure.

Question

Table Name : RM_master Fields : cust_no acct_no acct_code

Question is, I want to make the table RM_master as a variable in the parameters in the stored procedure?

This has no syntax error but when I execute this in the query analyzer by right clicking on the stored procedure name the variable table name (RM_master) is not identified Here is my stored procedure ;

CREATE PROCEDURE RMQUERY

  @cusnumber  nvarchar (255) = '' ,
  @acctnumber nvarchar (255) = '' ,
  @master nvarchar (255) = ''

AS

BEGIN

SET @CUSNUMBER = @CUSNUMBER DECLARE @RMRM AS NVARCHAR (255) SET @RMRM =n'SELECT * FROM' + @MASTER + 'WHERE ACCT_NO =' + @ACCTNUMBER

EXEC RMQUERY2 END

Answer 1

It's not recommended, as you simply are creating dynamic sql inside a stored proc. This opens up sql injection backdoors as you have no overview about what sql is created by the input: parameter values should never be used as query elements themselves, but only as values in a query (which can be dynamically created, though always should use parameters).

Though if you must, you should use the external stored proc sp_executesql and feed the sql to that proc.

Answer 2

You are not assigning a value to @master.

Answer 3

You might want to add Spaces before and after the table name otherwise it will look like this:

SELECT * FROMTABLENAMEWHERE ACCT_NO =0

Answer 4

You need a space between "FROM" and "WHERE" in your dynamic sql query

Instead of

SET @RMRM =n'SELECT * FROM' + @MASTER + 'WHERE ACCT_NO =' + @ACCTNUMBER

You should do

SET @RMRM = N'SELECT * FROM ' + @MASTER + N' WHERE ACCT_NO =' + @ACCTNUMBER

Answer 5

Thank you to all and Frans Bouma, however can you guide me how to to use the sp_executesql . thank you in advance, I really need a quick solution to meet a deadline. Wish you can help me out. God Bless