More Proof that I can be lazy

Likes: Having a stored procedure that attempts to do everything, as in the whole CRUD salad.
Dislikes: Having to create lots of separate stored procedures if I can the first option is feasible.
Thank God for dynamic queries for that.

/*

 *  Name                    : MY_LAZY_SP

 *  Purpose                 : multiple crud operations for group/division/department

 *

 *  History

 *  Date            Version  Modified By      Comments

 *  ===========     =======  ===========      ========

 *

 *  >> SELECT OPERATION

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

            exec dbo.MY_LAZY_SP

                @TABLE_affected     = 'GROUP', 

                @OPERATION          = 'select', 

                @REF_ID             = 0,

                @REF_VALUE          = '',

                @WILDCARD           = 'some wildcard'

 

  >> UPDATE OPERATION

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

    exec dbo.MY_LAZY_SP

            @TABLE_affected     = 'DIVISION', 

            @OPERATION          = 'update', 

            @REF_ID             = 2,

            @REF_VALUE          = 'my new value',

            @WILDCARD           = ''

           

           

  >> INSERT OPERATION

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

    exec dbo.MY_LAZY_SP

            @TABLE_affected     = 'DEPARTMENT', 

            @OPERATION          = 'insert', 

            @REF_ID             = 0,

            @REF_VALUE          = 'some new value',

            @WILDCARD           = ''         

*/

 

 

 

CREATE PROCEDURE dbo.MY_LAZY_SP

        @TABLE_affected     VARCHAR(25), --GROUP/DIVISION/DEPARTMENT

        @OPERATION          VARCHAR(20), --select, update, insert, delete

        @REF_ID             INT,

        @REF_VALUE          VARCHAR(256),

        @WILDCARD           VARCHAR(256)

AS

    BEGIN

 

        DECLARE @sql        VARCHAR(4000),

                @table_name VARCHAR(10)

       

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

        IF (@OPERATION = 'select')

            BEGIN

                    DECLARE @columnName VARCHAR(5)

 

                    IF (@TABLE_affected = 'GROUP')

                        SET @columnName = 'GRP'

                    ELSE IF (@TABLE_affected = 'DIVISION')

                        SET @columnName = 'DIV'  

                    ELSE IF (@TABLE_affected = 'DEPARTMENT')

                        SET @columnName = 'DEP'      

                       

                    SELECT @sql = ' SELECT '+@TABLE_affected+'_ID, '+@TABLE_affected+'_NAME '

                    SELECT @sql = @sql  +      'FROM RX_REF_'+@TABLE_affected+' '

                    SELECT @sql = @sql  +      ' WHERE '+@TABLE_affected+'_ID <> 1'

 

                    IF (@WILDCARD <> '')

                        BEGIN

                            SELECT @sql = @sql  +'  AND UPPER('+@TABLE_affected+'_NAME)
                               like ''%'
+ UPPER(@WILDCARD) +'%'' '

                        END   

                  

                    SELECT @sql = @sql + ' ORDER BY '+@TABLE_affected+'_NAME '

            END

       

       

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

        ELSE IF (@OPERATION = 'update')

            BEGIN

                    SELECT @sql =  ' DECLARE @countExistingUpdate INT '

                    SELECT @sql = @sql  +   ' SELECT @countExistingUpdate =
                        count('
+@TABLE_affected+'_NAME) from RX_REF_'+@TABLE_affected+' '

                    SELECT @sql = @sql  +   '    WHERE UPPER('+@TABLE_affected+'_NAME) =
                        UPPER('''
+@REF_VALUE+''') '

                    SELECT @sql = @sql  +   '    AND  '+@TABLE_affected+'_ID <>   '+
                        CONVERT(varchar(20),@REF_ID) +' '

                    SELECT @sql = @sql  +   ' IF (@countExistingUpdate = 1) '

                    SELECT @sql = @sql  +   ' BEGIN '

                    SELECT @sql = @sql  +   '    RAISERROR 100001 '''+@TABLE_affected+' name
                                                     already exists.'' '

                    SELECT @sql = @sql  +   ' END '     

                    SELECT @sql = @sql  +   ' ELSE '

                    SELECT @sql = @sql  +   ' BEGIN '

                    SELECT @sql = @sql  +   '  UPDATE RX_REF_'+@TABLE_affected+' '

                    SELECT @sql = @sql  +   '   SET    '+@TABLE_affected+'_NAME =
                                                 '''
+@REF_VALUE+''' '

                    SELECT @sql = @sql  +   '    WHERE  '+@TABLE_affected+'_ID =   '+
                                                   CONVERT(varchar(20),@REF_ID) +' '

                    SELECT @sql = @sql  +   ' END '     

            END

        

        

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

        ELSE IF (@OPERATION = 'insert')

            BEGIN

                    SELECT @sql =           '  INSERT INTO RX_REF_'+@TABLE_affected+' ('+@TABLE_affected+'_NAME)'

                    SELECT @sql = @sql  +   '   VALUES ('''+@REF_VALUE+''')'

                   

                   

                    SELECT @sql =  ' DECLARE @countExistingInsert INT '

                    SELECT @sql = @sql  +   ' SELECT @countExistingInsert =
                      count('
+@TABLE_affected+'_NAME) from RX_REF_'+@TABLE_affected+' '

                    SELECT @sql = @sql  +   '    WHERE UPPER('+@TABLE_affected+'_NAME) =
                       UPPER('''
+@REF_VALUE+''') '

 

                    SELECT @sql = @sql  +   ' IF (@countExistingInsert = 1) '

                    SELECT @sql = @sql  +   ' BEGIN '

                    SELECT @sql = @sql  +   '    RAISERROR 100001 '''+@TABLE_affected+' name
                           already exists.'' '

                    SELECT @sql = @sql  +   ' END '     

                    SELECT @sql = @sql  +   ' ELSE '

                    SELECT @sql = @sql  +   ' BEGIN '

                    SELECT @sql = @sql  +   '  INSERT INTO RX_REF_'+@TABLE_affected+'
                                                 ('
+@TABLE_affected+'_NAME)'

                    SELECT @sql = @sql  +   '   VALUES ('''+@REF_VALUE+''')'

                    SELECT @sql = @sql  +   ' END '  

            END

 

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

        ELSE IF (@OPERATION = 'delete')

            BEGIN

                       

                    SELECT @sql =           '  DELETE FROM RX_REF_'+@TABLE_affected+' '

                    SELECT @sql = @sql  +   '    WHERE  '+@TABLE_affected+'_ID =   '+
                                         CONVERT(varchar(20),@REF_ID) +' '

            END       

        exec (@sql)

    END

Published 07-17-2009 10:55 PM by avcajipe

Comments

# re: More Proof that I can be lazy

Friday, July 17, 2009 10:29 PM by bonskijr

in the update/insert blocks, there's no way other columns can be updated/inserted except for <Target_Table>_NAME column, unless they're the only ones that needs updated/inserted. But of course this is just a sample, you might have colum params for your sp, but then again it might get unwieldy over time.

I have a suggestion not worry about the CRUD sps, they can be easily scripted out of the tables (example using SMSToolPack which is free) you can't get any lazier than that.

And be careful of sql injections for your params, and replace the '''+@ref_value+''' statement with QUOTENAME(@ref_value, '''') to deal with multiple string delimeters and is alot safer.

cheers

# re: More Proof that I can be lazy

Saturday, July 18, 2009 1:09 AM by avcajipe

is QUOTENAME for sql server only or does sybase ase also have that as well? thanks for your comments.:)