Proper Case T-SQL

by Amol 30. January 2013 04:57
CREATE FUNCTION dbo.udfProperCase
(
    @Input as varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN   
    DECLARE @Reset bit,
            @Proper varchar(8000),
            @Counter int,
            @FirstChar char(1)
               
    SELECT @Reset = 1, @Counter = 1, @Proper = ''
    
    WHILE (@Counter <= LEN(@Input))
    BEGIN
        SELECT  @FirstChar = SUBSTRING(@Input, @Counter, 1),
                @Proper = @Proper + CASE WHEN @Reset = 1 THEN UPPER(@FirstChar) ELSE LOWER(@FirstChar) END,
                @Reset = CASE WHEN @FirstChar LIKE '[a-zA-Z]' THEN 0 ELSE 1 END,
                @Counter = @Counter + 1
    END
    
    SELECT @Proper = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Proper)),'  ',' '+ CHAR(7)) , CHAR(7)+' ',''), CHAR(7),'')
    WHERE CHARINDEX('  ', @Proper) > 0 
    
    RETURN @Proper
    
END
GO

SELECT dbo.udfProperCase ('amol     RAJMANE')
GO

Tags:

SQL Server Scripts

Load Delimited CSV file using ACE Driver

by Amol 14. December 2012 10:20

ACE driver will read the comma-delimited file without any issues. But if you have different delimiter it doesn’t work as expected.

Earlier we used to use FORMAT=Delimited(|) as a switch in connection string to load different delimiter CSV file.

 

To read the file with different delimiter, you need to create Schema.ini file in the same directory as the SCV file. And add the below contents in it.

 

[SampleFile.csv]

Format=Delimited(|)

ColNameHeader=False

 

Once you ready with the Schema.ini file run the below command to read the CSV file using ACE driver

 

SELECT TOP *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\Temp\;HDR=No',

'SELECT * FROM [SampleFile.csv]')

 

Tags: , ,

General | SQL Server Scripts

Stored Proc for Trigger using XML

by borigirla 20. September 2012 15:18

Hi all,
 
I’ve been given a task of modifying a script that works with triggers, preferably using XML schema which I have absolutely no experience with and I’m completely lost. Any and all help would be greatly appreciated!  What we are trying to do is modify the script so that instead of inserting a record for each column that is modified, it inserts a record grouping all columns that are modified in one field. That way we won’t insert so many records into the audit table. Again, any and all help would be greatly appreciated as I am completely lost.
 
Here are the examples my supervisor provided
 
Examples line by line (comma delimited): 
"Field Name", "Old Value", "New Value" (line feed here) 
"Field Name", "Old Value", "New Value" (line feed here) 
"Field Name", "Old Value", "New Value" (line feed here) 


Example in XML: 
SELECT ( SELECT 
'1' AS OldValue, 
'2' AS Newvalue 
FOR 
XML PATH('Field1'), 
TYPE) 
, 
( SELECT 
'x' AS OldValue, 
'y' AS NewValue 
FOR 
XML PATH('Field2'), 
TYPE) 

FOR XML PATH(''), 
ROOT('Changes') 
GO 

--Output 
<Changes> 
  <Field1> 
    <OldValue>1</OldValue> 
    <Newvalue>2</Newvalue> 
  </Field1> 
  <Field2> 
    <OldValue>x</OldValue> 
    <NewValue>y</NewValue> 
  </Field2> 
</Changes> 

 
Below is the current script. Thank you!!!
 

 
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1),
TableName varchar(128),
PrimaryKeyField varchar(1000),
PrimaryKeyValue varchar(1000),
FieldName varchar(128),
OldValue varchar(1000),
NewValue varchar(1000),
UpdateDate datetime DEFAULT (GetDate()),
UserName varchar(128)
)
GO
 
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
 
SELECT @TABLE_NAME= MIN(table_Schema + '.' +TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
and TABLE_NAME in ('tblAddress','tblAnswers','tblCase','tblCensus','tblEvidence','tblGpsSync','tblIntervention','tblMeterBattery','tblPhoto','tblRecord','tblTx')
and TABLE_SCHEMA = 'Data'
 
WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql =
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
 
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
 
select @TableName = ''' + @TABLE_NAME+ '''
 
-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
 
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
 
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
 
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_SCHEMA + ''.'' + pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
 
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA + ''.'' + TABLE_NAME = @TableName and ORDINAL_POSITION = @field 
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + '' is null)''
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(table_Schema + '.' +TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE table_Schema + '.' + TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
AND TABLE_NAME != 'Audit'
and TABLE_NAME in ('tblAddress','tblAnswers','tblCase','tblCensus','tblEvidence','tblGpsSync','tblIntervention','tblMeterBattery','tblPhoto','tblRecord','tblTx')
and TABLE_SCHEMA = 'Data'
END

Tags: , , ,

SQL Server Scripts

Category

Recent Posts

Tag cloud