Need help with SP please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with SP please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-02-14 : 12:59:31

[code]I need to filter out certain columns to change only to Time datatype. How can I check this filter condition in the Loop.
Please see the desire results below.

Thanks in advance.

-- Requirements:
Change ProcessTime DATETIME to ProcessTime Time -- this is Advantage datatype.

--run this 1.
IF OBJECT_ID('dbo.MyTest', 'u') IS NOT NULL
DROP TABLE dbo.MyTest
GO

CREATE TABLE dbo.MyTest
(
CustId INT IDENTITY (1, 1) NOT NULL,
LoginId VARCHAR(50) NULL,
FullName CHAR(20) NULL,
NMLSId INT NOT NULL,
DoseStatus SMALLINT NULL,
MaxDos NUMERIC(19, 4) NULL,
AmountPaid MONEY NULL,
StopDt SMALLDATETIME NULL,
ProcessTime DATETIME NULL,
UpdateDt DATETIME NULL,
ProcessND BIT NULL,
MyNote VARCHAR(MAX) NULL,
YourComment VARBINARY(MAX) NULL
)
go

-- 2 execute sp below.

EXECute dbo.usp_ConvertADSDataType @TableName = 'MyTest'
go

IF NULLIF(object_id('dbo.usp_ConvertADSDataType'), 0) > 0
DROP procedure dbo.usp_ConvertADSDataType
GO

CREATE PROCEDURE dbo.usp_ConvertADSDataType
(
@TableName VARCHAR(60) = NULL
)
AS
/******************************************************************************************
**
** History:
** Date Analyst Reason.
** xx/xx/xx xxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.
**
**
********************************************************************************************/
SET NOCOUNT ON
DECLARE @cmd VARCHAR(8000),
@ord_pos INT

IF OBJECT_ID('Tempdb..##TableName', 'u') IS NOT NULL
DROP TABLE ##TableName

CREATE TABLE ##TableName
(
TableName VARCHAR(60) NULL
)

INSERT ##TableName (TableName)
SELECT CAST(TABLE_NAME AS VARCHAR(60)) AS 'TableName'
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
AND TABLE_NAME NOT IN ('dtproperties', 'sysdiagrams')
AND RTRIM(TABLE_NAME) = ISNULL(@TableName, RTRIM(TABLE_NAME) )
ORDER BY TableName ASC

SET @TableName = (SELECT MIN(TableName)
FROM ##TableName)

WHILE (@TableName IS NOT NULL)
BEGIN
IF (@TableName IS NULL)
BEGIN
PRINT 'Get out the here.'
BREAK
END

PRINT ' '
PRINT 'CREATE TABLE ' + @TableName
PRINT '('

SET @ord_pos = 1

WHILE (@ord_pos < (SELECT MAX(ordinal_position) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @tablename) + 1)
BEGIN
SET @cmd = (SELECT SPACE(2) + SUBSTRING(t1.column_name,1,60) + SPACE(3) +
CASE
WHEN ( DATA_TYPE = 'INT' AND COLUMNPROPERTY(OBJECT_ID(table_name), column_name, 'ISIDENTITY') = 1) THEN 'AutoInc'
WHEN ( DATA_TYPE = 'INT' ) THEN 'Integer'
WHEN ( DATA_TYPE = 'SMALLINT' ) THEN 'Short'
WHEN ( DATA_TYPE = 'BIT' ) THEN 'Logical'
WHEN ( DATA_TYPE = 'Float' ) THEN 'Float'
WHEN ( DATA_TYPE = 'DATETIME') THEN 'Timestamp'
WHEN ( DATA_TYPE = 'SMALLDATETIME' ) THEN 'DATE'
WHEN ( DATA_TYPE IN ('DECIMAL', 'NUMERIC') ) THEN 'Double'
WHEN ( DATA_TYPE = 'VARBINARY' AND CHARACTER_MAXIMUM_LENGTH = -1 ) THEN 'Blob'
WHEN ( DATA_TYPE = 'VARCHAR' AND CHARACTER_MAXIMUM_LENGTH = -1 ) THEN 'Memo'
WHEN ( DATA_TYPE = 'MONEY' ) THEN 'CurDouble'
WHEN data_type IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR') THEN 'Char(' + RTRIM(character_maximum_length) + ')'
END + SPACE(0) +
CASE
WHEN Is_Nullable = 'Yes' THEN ''
ELSE ' NOT NULL'
END +
CASE
WHEN (LEN(column_default) > 0) THEN SPACE(2) + 'DEFAULT ' + t1.column_default
ELSE ''
END +
CASE
WHEN (@ord_pos + 1) < ( (SELECT MAX(ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @tablename) + 1) THEN ','
ELSE ''
END
FROM INFORMATION_SCHEMA.COLUMNS AS t1
WHERE (t1.table_name = @tablename)
AND (t1.ordinal_position = @ord_pos) )
PRINT @cmd
SET @ord_pos = (@ord_pos + 1)
END
PRINT ')'

SET @TableName = (SELECT MIN(TableName)
FROM ##TableName
WHERE TableName > @TableName)
END
GO

-------------------------------------------------------------------------------------------------

--
-- Result want 3.

CREATE TABLE MyTest
(
CustId AutoInc NOT NULL,
LoginId Char(50),
FullName Char(20),
NMLSId Integer NOT NULL,
DoseStatus Short,
MaxDos Double,
AmountPaid CurDouble,
StopDt DATE,
ProcessTime Time, -- Result want change from DATETIME to Time datatype only. Only certain columns need to filter out.
UpdateDt Timestamp,
ProcessND Logical,
MyNote Memo,
YourComment Blob
)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:41:41

so you'll have list of columns to be converted to time type beforehand? or is it like for columns that specify a set of criteria you want changes to be implemented?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-02-14 : 16:20:52

[code]Thanks for reply. I have a list of columns before hand.[/code]

quote:
Originally posted by visakh16

so you'll have list of columns to be converted to time type beforehand? or is it like for columns that specify a set of criteria you want changes to be implemented?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 16:38:43

then whats the purpose of these loops?
can you just use INFORMATION_SCHEMA.TABLES view to generate out datatype modification statements for these columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources