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

Can someone format the stored procedure?

dance2die
– dance2die

2009-03-16 14:44:27 +00:00
Commented Mar 16, 2009 at 14:44 — dance2die
– dance2die, Commented Mar 16, 2009 at 14:44

Frans Bouma · Accepted Answer · 2009-03-16 13:56:53Z

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.

answered Mar 16, 2009 at 13:56

Frans Bouma

8,3651 gold badge30 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cdonner · Accepted Answer · 2009-03-16 13:57:40Z

0

You are not assigning a value to @master.

answered Mar 16, 2009 at 13:57

cdonner

37.8k22 gold badges111 silver badges155 bronze badges

Comments

Glennular · Accepted Answer · 2009-03-16 14:15:00Z

0

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

SELECT * FROMTABLENAMEWHERE ACCT_NO =0

answered Mar 16, 2009 at 14:15

Glennular

18.3k9 gold badges61 silver badges77 bronze badges

Comments

dance2die · Accepted Answer · 2009-03-16 14:49:28Z

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

answered Mar 16, 2009 at 14:49

dance2die

37.2k39 gold badges137 silver badges200 bronze badges

Collectives™ on Stack Overflow

SQL 2000 Table Name as variable in stored procedure

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related