0

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
naide
  • 293
  • 3
  • 14

4 Answers4

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.

Frans Bouma
  • 8,259
  • 1
  • 27
  • 28
0

You are not assigning a value to @master.

cdonner
  • 37,019
  • 22
  • 105
  • 153
0

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

SELECT * FROMTABLENAMEWHERE ACCT_NO =0
Glennular
  • 17,827
  • 9
  • 58
  • 77
0

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
dance2die
  • 35,807
  • 39
  • 131
  • 194