Mega Code Archive
Remove data based upon criteria specified through arguments
<![CDATA[
7>
8>
9> CREATE TABLE stores(
10> stor_id char(4) NOT NULL,
11> stor_name varchar(40) NULL,
12> stor_address varchar(40) NULL,
13> city varchar(20) NULL,
14> state char(2) NULL,
15> zip char(5) NULL
16> )
17> GO
1> insert stores values('1','B','567 Ave.','Tustin', 'CA','92789')
2> insert stores values('2','N','577 St.', 'Los Gatos','CA','96745')
3> insert stores values('3','T','679 St.', 'Portland', 'OR','89076')
4> insert stores values('4','F','89 St.', 'Fremont', 'CA','90019')
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE PROCEDURE prDeleteData @chvTable VARCHAR(30),
4> @chvWhereField VARCHAR(30) = NULL,
5> @chvWhereFieldDataType VARCHAR(30) = 'CHAR',
6> @chvOperator VARCHAR(2) = '=',
7> @chvValue VARCHAR(30) = NULL
8> AS
9> DECLARE @chvSQL VARCHAR(255), @chvQuotes CHAR(1)
10> SELECT @chvSQL = 'DELETE ' + @chvTable
11> IF NOT @chvWhereField IS NULL
12> BEGIN
13> SELECT @chvSQL = @chvSQL + ' WHERE ' + @chvWhereField + ' ' + @chvOperator + ' '
14> SELECT @chvWhereFieldDataType = LOWER(RTRIM(@chvWhereFieldDataType))
15> SELECT @chvQuotes = CASE @chvWhereFieldDataType
16> WHEN 'char' THEN 'y'
17> WHEN 'datetime' THEN 'y'
18> WHEN 'datetimn' THEN 'y'
19> WHEN 'smalldatetime' THEN 'y'
20> WHEN 'text' THEN 'y'
21> WHEN 'varchar' THEN 'y'
22> ELSE 'n'
23> END
24> IF @chvQuotes = 'y'
25> SELECT @chvSQL = @chvSQL + '''' + @chvValue + ''''
26> ELSE
27> SELECT @chvSQL = @chvSQL + @chvValue
28> END
29> EXEC (@chvSQL)
30> GO
1>
2>
3> EXEC prDeleteData @chvTable = 'stores',
4> @chvWhereField = 'stor_name',
5> @chvWhereFieldDataType = 'CHAR',
6> @chvOperator = '=',
7> @chvValue = 'News & Brews'
8> GO
(0 rows affected)
1>
2>
3> drop PROCEDURE prDeleteData;
4> GO
1>
2> drop table stores;
3> GO
]]>