VBA function to join strings

Building SQL in VBA? BAD programmer. BAD. me too.

'join strings using joiner. a can be list of string params, or a single collection, or a single array
Function Join(joiner As String, ParamArray a() As Variant) As String
    'examples
    'w = join(" and ","customer=12","color=red")
    'w=join(" and ",array("length=3","width=2"))
    'dim col as new collection: col.add("height=77")
    'col.add("material=steel")
    'w=join(" and ",col)

    Dim s As String
    Dim i As Integer
    Dim c As Variant

    If Not IsArray(a) Then
        s = ""
    ElseIf UBound(a) = 0 Then
        If TypeOf a(0) Is Collection Or IsArray(a(0)) Then
            For Each c In a(0)
                If Len(c) > 0 Then
                    If Len(s) > 0 Then s = s & joiner
                    s = s & c
                End If
            Next
        Else
            s = ""
        End If
    Else
        For i = LBound(a) To UBound(a)
            If TypeOf a(i) Is Collection Then
                For Each c In a(i)
                    If Len(c) > 0 Then
                        If Len(s) > 0 Then s = s & joiner
                        s = s & a(i)
                    End If
                Next
            ElseIf Len(a(i)) > 0 Then
                If Len(s) > 0 Then s = s & joiner
                s = s & a(i)
            End If
        Next i
    End If
    Join = s

End Function

Sub TestJoin()
    Dim s As String
    Dim t As String
    Dim arr As New Collection
    Dim a As Variant

    t = "apples,pears,oranges"

    a = Split(t, ",")

    s = Join(",", Split(t, ","))
    If s <> t Then
        MsgBox "the sword has not been mended"
    End If

    s = Join(" ", "hello", "access", "world")
    If s <> "hello access world" Then
        MsgBox "I'm sorry Dave I can't do that"
    End If

    arr.Add "hello"
    arr.Add "access"
    arr.Add "world"

    s = Join(" ", arr)
    If s <> "hello access world" Then
        MsgBox "i only wanted to say hello"
    End If

End Sub

sp_who you can filter

SELECT spid,
 sp.[status],
 loginame [Login],
 hostname, 
 blocked BlkBy,
 sd.name DBName, 
 cmd Command,
 cpu CPUTime,
 physical_io DiskIO,
 last_batch LastBatch,
 [program_name] ProgramName 
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
where hostname='BOARDROOM'
ORDER BY spid 


Get size of files using xp_cmdshell

Declare @Dir VARCHAR(256)
DECLARE @CMD VARCHAR(256)
SET @Dir = 'C:\myImageFolder\'
SET @CMD = 'DIR "'+@DIR+'" /A /S'

drop table #tmp 
 
 declare @media nvarchar(2048), @fn nvarchar(2048)
 DECLARE @CMD VARCHAR(256)

set @media = 'D:\Filestore\media'

SET @CMD = 'DIR "'+@media +'" /-C /S'

CREATE TABLE #tmp 
 (returnval NVARCHAR(500), rownum INT IDENTITY(1,1))

-- Populate Temp Table with the contents of the outfiles directory
 INSERT #tmp EXEC master..xp_cmdshell @cmd

alter table #tmp add filename nvarchar(255)
 alter table #tmp add size int
 alter table #tmp add DocumentId int

select convert(int,substring(returnval, 27,9)) size, substring(returnval, 37,999) name, * from #tmp
 where returnval like '__/__/____ __:__ %'
 --e.g. '17/11/2017 11:40 2645779 2017-11-17T114021.769Z001214 Band 4 Business Administrator Job Description_SM.pdf'

update #tmp set filename = substring(returnval, 37,999)
 , size = convert(int,substring(returnval, 27,9))
 where returnval like '__/__/____ __:__ %'

update #tmp set documentid=d.documentid from
 #tmp, documents d where d.documentpath like '%' + #tmp.filename


 select 
 filename, 'D:\MyStaffFile_Files\media\' + d.documentpath, s.assignmentid, size, d.documentid, createdby, createddate
 
 from #tmp inner join documents d on #tmp.documentid = d.documentid
 inner join sections s on d.sectionid=s.sectionid
 
 where size>1000000
 order by CreatedBy, CreatedDate, Size desc --documentid desc

 

What columns are used? Quick analysis of legacy SQL table

Got a table with lots of columns? Use this SQL to identify which have values.

My table name is manTransaction and I’m only interested in those of type = 2

declare @tn nvarchar(200)
declare @whereclause nvarchar(1024)
set @tn='manTransactionDetail' --table
set @whereclause = 'TransactionID in (select TransactionID from manTransaction where TransactionTypeID=8)' --particular records
declare @col nvarchar(200)
declare @typ int
declare @sql0 nvarchar(2048)
declare @sql nvarchar(2048)

set @sql0 = 'with a(v) as (select __COLNAME__ from __TABLENAME__ where __WHERECLAUSE__ )
 , d(v) as (select v from a group by v)
 insert into #ColumnStatus(ColName, DistinctValues) select ''__COLNAME__'', COUNT(*) from d'
set @sql0 = REPLACE(@sql0, '__TABLENAME__',@tn)
set @sql0 = REPLACE(@sql0, '__WHERECLAUSE__',@whereclause)

if OBJECT_ID('tempdb..#ColumnStatus') is not null drop table #ColumnStatus
create table #ColumnStatus(ColName nvarchar(200), DistinctValues int)
declare c cursor for
select name, system_type_id from sys.columns where object_id=object_id(@tn)
open c
fetch next from c into @col,@typ
set @sql = REPLACE (@sql0, '__COLNAME__', @col)
print @sql
exec(@sql) 
while @@FETCH_STATUS=0
begin
 fetch next from c into @col,@typ
 set @sql = REPLACE (@sql0, '__COLNAME__', @col)
 exec(@sql) 
end
close c
deallocate c
select ColName + ',' from #ColumnStatus where DistinctValues > 1

--select * from #ColumnStatus





 

 

sql isnumeric(2,000.00)=1 but isfloat(2,000.00) errors Error converting data type varchar to float.

select isnumeric('20,000.000') returns 1
select convert(float, '20,000.000') raises an error Error converting data type varchar to float.

You can’t write a isfloat() function, because try-catch is a side effect.

Here’s a snippet to walk thru your data to find duff values

declare c cursor for
select chillirecipedetailid, amount_or from tblAbRecipes
open c
declare @id int, @val nvarchar(100)
fetch next from c into @id, @val
while @@FETCH_STATUS=0
begin
 begin try
 declare @f float
 set @f = convert(float,@val)
 end try
 begin catch
 print 'duff value at ' + convert(nvarchar(20),@id) + ': ' + @val
 end catch;
 fetch next from c into @id, @val
end;
close c
deallocate c

select isnumeric('20,000.000')
select convert(float, '20,000.000')

 

How to check a file exists in transact sql using xp_FileExists

 

exec sp_configure 'show advanced options',1
go
reconfigure;
go
exec sp_configure 'xp_cmdshell', 1;
go
reconfigure;
go

drop function sp_FileExists

if object_id('sp_FileExists') is null
begin
 declare @sql nvarchar(2000)
 set @sql='
 create function dbo.sp_FileExists(@fn nvarchar(1000)) returns bit as
 begin
 declare @result int
 exec master.dbo.xp_fileexist @fn, @result output
 if @result = 1 return 1
 return 0 
 end
 '
 exec @sql
end

select DocumentId, 'd:\MyStaffFile_Files\media\' + DocumentPath 
,case when DocumentPath is null then 'no path'
 when dbo.sp_FileExists('d:\MyStaffFile_Files\media\' + DocumentPath) = 1 then 'exists'
 else 'does not exist'
 end
from Documents

How to do a complete path directory listing with transact sql, xp_dirtree and a recursive common table expression

--How to Use xp_dirtree to List All Files in a Folder http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html
--Recursive Directory Listing in Transact-SQL: https://www.brad-smith.info/blog/archives/70
--enable extended procs http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html

--enable extended procs (if you have permission! if not, you're stuffed)
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE



IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
 DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
 fileId int IDENTITY(1,1)
 ,fullPath nvarchar(2000)
 ,subdirectory nvarchar(max)
 ,depth int
 ,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree 'C:\Projects\MyStaffFile2\SomeTestData',0,1;

;with fileparents(fileid, subdirectory, parentid, isfile) as (
 select c.fileid, c.subdirectory, (select max(p.fileId) from #DirectoryTree p where p.depth=c.depth-1 and p.fileId<c.fileId) parentId , c.isfile
 from #DirectoryTree c
),DirListing (fileid,subdirectory, isfile) as(
 select fileid, subdirectory + '' subdirectory, isfile from fileparents where parentid is null
 union all
 select f.fileid, d.subdirectory +'\' + f.subdirectory subdirectory, f.isfile
 from DirListing d join fileparents f on f.parentid=d.fileid
)
select l.subdirectory, 'file not in documents table' from DirListing l
left outer join Documents d on l.subdirectory=d.DocumentPath where d.DocumentId is null
--do something useful with this listing

--turn extended procs back off
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE

Delete all tables from a SQL database using Transact SQL Cursors

If you have a SQL2016 database but only SQL 2008R2 SSMS then you can’t delete tables with extreme prejudice using the DEL key, you get an error something like “index out of range”.

However this SQL will do the same job.

Just remember, it’s your foot…

From an idea from Richard…

declare @sql nvarchar(2048)
declare @tn nvarchar(200)

declare tablecursor cursor
for
select name from sys.tables
open tablecursor
fetch next from tablecursor into @tn
while @@FETCH_STATUS=0
begin
print @tn
declare fkcursor cursor
for
SELECT
‘ALTER TABLE [‘ + OBJECT_SCHEMA_NAME(parent_object_id) +
‘].[‘ + OBJECT_NAME(parent_object_id) +
‘] DROP CONSTRAINT [‘ + name + ‘]’
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(@tn)
open fkcursor
fetch next from fkcursor into @sql
while @@fetch_status = 0
begin
print @sql
exec(@sql)
fetch next from fkcursor into @sql
end
close fkcursor;
deallocate fkcursor

set @sql = ‘drop table ‘ + @tn
exec(@sql)

fetch next from tablecursor into @tn
end

 

SQL rows to concatenated string

How to turn a list of (e.g.) countries into a concatenated string (e.g. France, New Zealand, California)

There’s a comprehensive post about different methods at https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485 : the method I’ve used is “Scalar UDF with t-SQL update extension”

ALTER function [dbo].[GetCountriesOfOrigin](@productId int) returns 
varchar(max)
as
begin
 
 declare @result varchar(max) 
 set @result = '' 
 select @result = @result + c.CountryOfOrigin + ', ' from tblProductCountry pc 
 inner join tblCountryOfOrigin c on pc.CountryOfOriginID=c.CountryOfOriginID 
 where pc.ProductID=@productId 
 order by c.CountryOfOrigin
 set @result = ltrim(rtrim(@result))
 if right(@result,1)=',' set @result=left(@result, len(@result)-1) --damn LEN! ignores trailing spaces
 if len(@result)=0 set @result=null
 return @result
end

Enable data audit and tracking

This sql script will add system versioning to all your tables in your existing SQL 2016 database.
For example table [Stuff] will have a [StuffHistory] table where all old values are logged.
As we have existing data, we have to do each table in four SQL statements and I don’t think we can make the columns HIDDEN.

/*
 Set all tables in this database as SYSTEM VERSIONED TEMPORAL TABLES
 Version 1.1
 Nick Ajderian
 
Enable data audit and tracking
References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table
Convert existing table to temporal table
*/ /* create table stuff6(stuffid int identity primary key, body nvarchar(200)) update stuff6 set body=body+'x' insert into stuff6(body) values('hello ') select * from stuff6 select * from stuff6history */ declare @sql1 nvarchar(1024),@sql3 nvarchar(1024),@sql4 nvarchar(1024), @sql2 nvarchar(1024) declare @sqlx nvarchar(1024) set @sql1='ALTER TABLE TABLE_NAME ADD SysStartTime datetime2(0) not null DEFAULT SYSUTCDATETIME() , SysEndTime datetime2(0) not null DEFAULT CONVERT(datetime2 (0), ''9999-12-31 23:59:59.99999999'') ' set @sql2='UPDATE TABLE_NAME SET SysStartTime = ''19000101 00:00:00.0000000'', SysEndTime = ''99991231 23:59:59.9999999'' ' set @sql3='ALTER TABLE TABLE_NAME ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);' set @sql4='ALTER TABLE TABLE_NAME SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TABLE_NAMEHistory))' --GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStart --GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEnd declare @tn nvarchar(200) declare c cursor for select name from sys.tables where not ( name like '%History' ) and not exists( select * from sys.tables tt where tt.name=sys.tables.name+'history' ) and name not in ('temp_NHSPatients','Diagnosis','EinsteinData','tbl_DiagnosisImport','tbl_OverwriteCCGs_KH','tbl_OverwriteCCGs_KH2') order by name open c fetch next from c into @tn while @@FETCH_STATUS=0 begin print 'Adding temporal fields to ' + @tn set @sqlx = replace(@sql1,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql2,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql3,'TABLE_NAME', @tn) exec (@sqlx) set @sqlx = replace(@sql4,'TABLE_NAME', @tn) exec (@sqlx) fetch next from c into @tn end close c deallocate c