INSERT INTO table_set values ('b'); INSERT INTO table_set values (); SELECT * FROM table_set; -- 'a','','a,c','b','b','' SELECT * FROM table_set where s='a,c' -- 'a,c' -------------------------------------------------- CREATE PROCEDURE proc_set (p char(1), s set ('a','b','c')) BEGIN if find_in_set(p,s)>0 then SELECT p; else SELECT 'Invalid argument'; end if; END call proc_set ('a','b,c,a'); -- 'a' call proc_set ('a','b,c'); -- 'Invalid argument' Solution: The SET data type has dual nature—it is both an integer (up to 64 bits) and a string. Each bit in SET corresponds to a string description. The string representation of a SET value consists of appropriate strings, concatenated by commas. Data manipulation is possible with both integer and string representations of SET. Internally, SET is stored as integer; the size depends on the number of SET values (from 1 to 8 SQL Server emulation of the SET data type should be based on bigint, the largest possible integer data type. To hold the string representation of bits in a SET value and to define all possible bits, create a table” as in the following example code: “lookup CREATE TABLE lookup_set( schemaname sysname not null, -- schema name tablename sysname not null, -- table name colname sysname not null, -- column name bitmask bigint not null, -- bitmask for value position int not null, -- position in list description varchar(512) not null, -- character description of value constraint pk_lookup_set
INSERT INTO table_set values ('b'); INSERT INTO table_set values (); SELECT * FROM table_set; -- 'a','','a,c','b','b','' SELECT * FROM table_set where s='a,c' -- 'a,c' -------------------------------------------------- CREATE PROCEDURE proc_set (p char(1), s set ('a','b','c')) BEGIN if find_in_set(p,s)>0 then SELECT p; else SELECT 'Invalid argument'; end if; END call proc_set ('a','b,c,a'); -- 'a' call proc_set ('a','b,c'); -- 'Invalid argument' Solution: The SET data type has dual nature—it is both an integer (up to 64 bits) and a string. Each bit in SET corresponds to a string description. The string representation of a SET value consists of appropriate strings, concatenated by commas. Data manipulation is possible with both integer and string representations of SET. Internally, SET is stored as integer; the size depends on the number of SET values (from 1 to 8 SQL Server emulation of the SET data type should be based on bigint, the largest possible integer data type. To hold the string representation of bits in a SET value and to define all possible bits, create a “lookup table” as in the following example code: CREATE TABLE lookup_set( schemaname sysname not null, -- schema name tablename sysname not null, -- table name colname sysname not null, -- column name bitmask bigint not null, -- bitmask for value position int not null, -- position in list description varchar(512) not null, -- character description of value constraint pk_lookup_set