Dynamic Stored Procedure : SQL Server 2005

ALTER PROCEDURE mstPelangganIJOINmstPelangganKendaraan_SELECT

      @COLUMN                 NVARCHAR(255)

      ,@FIELD                 NVARCHAR(50)

      ,@CRITERIA        NVARCHAR(50)

AS

BEGIN TRY

      SET NOCOUNT ON;

     

      /* PROCEDURE BODY */

      DECLARE @SQLSTRING                  NVARCHAR(4000)

      DECLARE @PARAMDEFINITION      NVARCHAR(4000)

      SET @SQLSTRING = 'SELECT ' + @COLUMN + '

                        FROM

                            mstPelanggan            AS A INNER JOIN

                            mstPelangganKendaraan   AS B ON

                            a.ID = b.pelangganID '

      IF @CRITERIA = ''

            BEGIN

                  SET @SQLSTRING = @SQLSTRING

            END

      ELSE

            BEGIN

                  SET @SQLSTRING = @SQLSTRING + ' WHERE ' + @FIELD + ' LIKE ''%' + @CRITERIA + '%'' '

            END

      -- =================================================================

      -- PREPARE FOR EXECUTE

      -- =================================================================

      SET @PARAMDEFINITION  = '

            @COLUMN                 NVARCHAR(50)

            ,@FIELD                 NVARCHAR(50)

            ,@CRITERIA        NVARCHAR(50)'

      EXECUTE sp_executesql @SQLSTRING,@PARAMDEFINITION,

            @COLUMN

            ,@FIELD

            ,@CRITERIA;

END TRY

BEGIN CATCH

      SELECT ERROR_NUMBER()   AS ErrNumber

      ,ERROR_SEVERITY()       AS ErrSeverity

      ,ERROR_STATE()          AS ErrorState

      ,ERROR_PROCEDURE()      AS ErrorProcedure

      ,ERROR_LINE()           AS ErrorLine

      ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH