Mega Code Archive
Uses system tables to determine the datatype of a field
<![CDATA[
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
]]>