The implementation method of mssql Stored Procedure table name and field name is variable. For more information, see.
The implementation method of mssql Stored Procedure table name and field name is variable. For more information, see.
Direct error reporting without using dynamic statements
Incorrect
The Code is as follows:
Alter proc testpapers
As
Begin
Declare @ tems nvarchar (max), @ zidaun nvarchar (max)
Set @ tems = select * from @ tems order by @ zidaun
Exec (@ tems)
End
Exec testpapers
Message 156, Level 15, status 1, process testpapers, 1st rows
There is a syntax error near the keyword 'select.
Message 1087, Level 15, status 2, process testpapers, 1st rows
The table variable "@ tems" must be declared ".
First, you must use dynamic statements to set the table name or field as a variable.
Incorrect
The Code is as follows:
Alter proc testpapers
As
Begin
Declare @ tems nvarchar (max), @ zidaun nvarchar (max)
Set @ tems = 'select * from @ tems order by @ zidaun ';
Exec (@ tems)
End
Exec testpapers
Message 1087, Level 15, status 2, 1st rows
The table variable "@ tems" must be declared ".
Write the table name and field name to exec.
Correct
The Code is as follows:
Alter proc testpapers
As
Begin
Declare @ startRow nvarchar (max), @ tems nvarchar (max), @ zidaun nvarchar (max)
Set @ startRow = 'temp'
Set @ tems = 'select * from ';
Set @ zidaun = 'P _ id ';
Exec (@ tems + @ startRow + 'ORDER BY' + @ zidaun)
End
Exec testpapers
