Direct error reporting without using dynamic statements
Incorrect
Copy codeThe 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
Copy codeThe 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
Copy codeThe 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