Problems with stitching strings in SQL Server database stored procedures

　　When writing complex stored procedures in a SQL Server database, it is common practice to stitch strings and finally use the Exec sp_executesql ' stitched string ' to query the results.

Look at the code first:

1 -- =============================================2 --author:xxx3 --Create date:2014-09-194 --Description: Get student list information5 -- =============================================6 ALTER PROCEDURE [dbo].[sp_getstudentlist]7     @StudentId INT   --primary Key ID8  as9 BEGINTen      One     SETNOCOUNT on; A      -     DECLARE @SqlSelectResult NVARCHAR(MAX)= "';  -     SET @SqlSelectResult = 'SELECT * from Student as S'; the      -     IF(ISNULL(@StudentId,0)> 0) -     BEGIN -         SET @SqlSelectResult = @SqlSelectResult + 'WHERE s.classid >' + @StudentId; +     END -      +     PRINT(@SqlSelectResult); A      at     EXECsp_executesql@SqlSelectResult; -      -     SETNOCOUNTOFF; - END

Then call the stored procedure: EXEC sp_getstudentlist 1. The results are as follows:

Failed to run.

careful analysis of cause discovery: The stored procedure parameter @StudentId type int (shape), and the custom variable @sqlselectresult is the nvarchar (MAX) string type.

In 23 rows, EXEC sp_executesql @SqlSelectResult; When the concatenation string is executed, an error occurs, and the compiler attempts to convert the string type to int type failed.

This means:when stitching strings in SQL Server, all variables must be string type to correctly splice or error.

Workaround 1: Convert a variable of a non-string type to a string type.

Modify the 18 lines of code to:

        convert (nvarchar), @StudentId);

Workaround 2: Define the parameter as a string type when the stored procedure starts to define

               ALTER PROCEDURE [dbo]. [Sp_getstudentlist]              @StudentId INT   --primary key ID               As
               ......

Problems with stitching strings in SQL Server database stored procedures