uses system tables to determine the datatype of a field. : Utility Procedure « Procedure Function « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Procedure Function
3. Utility Procedure

3>
4>
5>     CREATE PROCEDURE prNeedsQuotes
6>         @chvTable VARCHAR(30),
7>         @chvField VARCHAR(30),
8>         @chvNeedsQuotes CHAR(1) OUTPUT
9>     AS
10>         DECLARE @chvDataType VARCHAR(30), @intUserType INT
11>     SELECT @chvDataType = LOWER(st.name), @intUserType = st.usertype
12>         FROM (sysObjects so INNER JOIN sysColumns sc ON so.id = sc.id)
13>             INNER JOIN sysTypes st ON sc.usertype = st.usertype
14>         WHERE so.type = 'U'
15>             AND so.name = @chvTable
16>             AND sc.name = @chvField
17>     IF @intUserType > 100
18>             BEGIN
19>                 SELECT @chvDataType = LOWER(st2.name)
20>                 FROM sysTypes st1 INNER JOIN sysTypes st2 ON st1.Type = st2.Type
21>                 WHERE st2.userType < 100
22>                   AND st2.userType NOT IN (18, 80)
23>                   AND st1.usertype = @intUserType
24>             END
25>     SELECT @chvNeedsQuotes =
26>             CASE @chvDataType
27>                 WHEN 'char' THEN 'y'
28>                 WHEN 'datetime' THEN 'y'
29>                 WHEN 'datetimn' THEN 'y'
30>                 WHEN 'smalldatetime' THEN 'y'
31>                 WHEN 'text' THEN 'y'
32>                 WHEN 'timestamp' THEN 'y'
33>                 WHEN 'varchar' THEN 'y'
34>                 ELSE 'n'
35>             END
36>     GO
1>
2>     drop PROCEDURE prNeedsQuotes ;
3>     GO