Jun 23, 2009

Dynamic field name and data type selection in sqlserver 2005/2008

DECLARE @ListXML VARCHAR(MAX),@TableName VARCHAR(50)
SET @ListXML=''
SET @TableName='T_SAMPLE'-- MENTION UR TABLE NAME HERE

SELECT @ListXML = (SELECT CASE WHEN DATA_TYPE IN('datetime','smalldatetime') THEN 'ISNULL(CONVERT(VARCHAR(10),'+CONVERT(VARCHAR,COLUMN_NAME)+',101),''''),'
WHEN DATA_TYPE IN('bit','int') THEN 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME)+' AS VARCHAR),''''),'
ELSE 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME) +' AS VARCHAR('+CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')),''''),' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN(@TableName) FOR XML PATH(''))

SELECT @ListXML

No comments: