MS SQLserver MS SQL Stored Procedure

CREATE TABLE testTable(

NAME NVARCHAR(50),

AGE INT)

GO

INSERT INTO testTable (NAME,AGE) VALUES ('코드블랙',15)

INSERT INTO testTable (NAME,AGE) VALUES ('홍길동',25)

INSERT INTO testTable (NAME,AGE) VALUES ('김복남',35)

INSERT INTO testTable  (NAME,AGE) VALUES ('이순신',52)

INSERT INTO testTable (NAME,AGE) VALUES ('둘리',26)

SELECT * 

FROM testTable

CREATE PROCEDURE TestSP 

-- Add the parameters for the stored procedure here

    @input       NVARCHAR(50) =null,

    @output     INT                 =0   OUT

AS

----------------------------------------------------

--sp_executesql을 실행할 변수 선언

----------------------------------------------------

DECLARE @Sql      NVARCHAR(MAX) = ''

DECLARE @Where  NVARCHAR(MAX) = ''

DECLARE @Param  NVARCHAR(MAX) = ''

----------------------------------------------------

--입력 이름이 들어오면 WHERE절 추가

----------------------------------------------------

IF ISNULL(@input,'') <> '' BEGIN

SET @Where = @Where + N'

    WHERE NAME = @input '

END

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET @Sql = @Sql + N'

    SELECT @output = AGE

    FROM   dbo.testTable '

SET @Sql = @Sql + @Where

SET @Param = N'

    @input  NVARCHAR(50),

    @output INT OUT '

EXEC SP_EXECUTESQL @Sql

                  ,@Param

                  ,@input  = @input

                  ,@output = @output