How to pass parameter value for a string that is inside Variable?

Question

Can some one please help me how can I pass a Parameter value into a string that is inside a Variable

Ex:

DECLARE @STR VARCHAR(MAX) = ''

DECLARE @ID INT

SET @STR = 'SELECT * FROM STUDENT WHERE STUDENT_ID=@ID'

Here I want to pass @ID as a parameter to Variable @STR

Answer 1

Use sp_executesql to value to the parameter this will also avoid sql injection. Try this

DECLARE @STR NVARCHAR(MAX) = ''

DECLARE @ID INT

SET @STR = 'SELECT * FROM STUDENT WHERE STUDENT_ID=@ID'

exec sp_executesql @str,'@ID INT',@ID