USP_BUSINESSPROCESSSTATUS_SAVELABELTEMPLATE

            create proc dbo.USP_BUSINESSPROCESSSTATUS_SAVELABELTEMPLATE
                    @WORDTEMPLATETABLENAME nvarchar(255),
                    @WORDTEMPLATECOLUMNNAME nvarchar(255),
                    @WORDTEMPLATEID uniqueidentifier,
                    @STATUSID uniqueidentifier, 
                    @CHANGEAGENTID uniqueidentifier
            with execute as caller
            as
                set nocount on;
                declare @FILE varbinary(max);
                declare @SQL nvarchar(1024);

                declare @EXISTS bit;
                set @EXISTS = 0;

                set @SQL = 'if exists (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLENAME) set @DYN_EXISTS = 1;';
                exec sp_executesql @SQL, N'@TABLENAME nvarchar(255), @DYN_EXISTS bit output', @TABLENAME = @WORDTEMPLATETABLENAME, @DYN_EXISTS = @EXISTS output;
                if @EXISTS = 0 
                    begin
                        raiserror(N'%s is not a valid table name.', 16, 1, @WORDTEMPLATETABLENAME);
                        return 1;
                    end

                set @EXISTS = 0;
                set @SQL = 'if exists (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@TABLENAME and COLUMN_NAME=@COLUMNNAME) set @DYN_EXISTS = 1;';
                exec sp_executesql @SQL, N'@TABLENAME nvarchar(255), @COLUMNNAME nvarchar(255), @DYN_EXISTS bit output', @TABLENAME = @WORDTEMPLATETABLENAME, @COLUMNNAME = @WORDTEMPLATECOLUMNNAME, @DYN_EXISTS = @EXISTS output;
                if @EXISTS = 0 
                    begin
                        raiserror(N'%s is not a valid column name.', 16, 1, @WORDTEMPLATECOLUMNNAME);
                        return 1;
                    end

                set @SQL = 'select @DYN_FILE = [' + @WORDTEMPLATECOLUMNNAME + '] from ' + @WORDTEMPLATETABLENAME + ' where ID = @DYN_WORDTEMPLATEID;'
                exec sp_executesql @SQL, N'@DYN_WORDTEMPLATEID uniqueidentifier, @DYN_FILE varbinary(max) output',
                                            @DYN_WORDTEMPLATEID = @WORDTEMPLATEID,
                                            @DYN_FILE = @FILE output;                    

                update dbo.BUSINESSPROCESSSTATUS
                    set LABELTEMPLATE = @FILE, DATECHANGED = getdate(), CHANGEDBYID = @CHANGEAGENTID
                    where ID = @STATUSID;