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