Dav.id davidsheardown

## mssql_trycatch_upsert.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_application_upsert]
	@Id						int = null,
	@Name					nvarchar(255) = null,
	@Description			nvarchar(255) = null,
	@OperationalStatusCode	nvarchar(255) = null,
	@OperationalStatusDesc	nvarchar(255) = null,

## DynamicSQL_XML.sql
select dense_rank() over(order by ID, I.N) as ID,
       F.N.value('(Text/text())[1]', 'varchar(max)') as Name,
       F.N.value('(Value/text())[1]', 'varchar(max)') as Value
into #T
from YourTable as T
  cross apply T.XMLCol.nodes('/Items/Item') as I(N)
  cross apply I.N.nodes('FormItem') as F(N)

declare @SQL nvarchar(max)
declare @Col nvarchar(max)

## SAP HANA Cross Server Join.sql
SELECT TOP (1)TX.[LIN_ItemNumber_PRI_02] as 'AmazonCode', TX.[SAP_ItemNumber] as 'ItemCode',
TX.RFF_ReferenceNumber,
TY.CardCode AS 'CardCode'

  FROM [MY_STAGING].[dbo].[MS-SQL-TABLE] TX
inner join OPENQUERY ("hana-b1",'SELECT T0."AddID", T0."CardCode" from "A-SAP-HANA-DB"."OCRD" T0') TY on TX.RFF_ReferenceNumber = TY.AddID
WHERE  [SAP_ItemNumber] IS NULL

## SQLblobToFile.sql
/*
  To directly add a file to blob
  (this example was a 120mb file)
*/
INSERT INTO [dbo].[TableWithBlob](Id, ProductId, Filename, [ActualFile])
   SELECT 1,1,'filename',BulkColumn
   FROM OPENROWSET(BULK N'c:\clients\test.exe', SINGLE_BLOB) as AnyAliasFieldYouLike


/*

## ms-sql-calendar-working-days.sql
/*
  This was from another source, probably good old Stackoverflow! but capturing here for quick reference

  This script will create a calendar table, generate a stored proc to populate it working out weekends and also if you provide
  a set of holiday dates, it will add those too.

  The end result is a couple of select statements that allows you to pass a date (todays date as default), then add/subtract
  number of days to calculate the next working date based on the above weekends and/or holidays
*/
CREATE TABLE [dbo].[Calendar](

## HeaderAndLinesCountCompare.sql
/*
  Quick query to compare a count total at header level to line count of successful rows i.e. 1 -> Many integrity check
  to ensure the correct row count of lines matches header
*/

select
	sph.Id,
	sph.Filename,
	sph.Status,
	sph.CreatedAt,

## CTE Recursive Running Total.sql
/*
  Using a CTE, we can iterate through to get the previous row in order to calculate and keep
  a running total
*/
WITH PrevRow
     AS (SELECT Acolumn,
                Bcolumn,
                HoldingColumn = Bcolumn,
                RunningTotal = HoldingColumn
         FROM   Atable

## MS SQL Get Date as Monday of Current week.sql
/*
  Get date as Monday of current week.. just change the "0" in the last param to whichever day you need
  i.e. SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) would return date as Wednesday of current week

  Handy to do date between or greater than checks
*/
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

## jQuery DIV Toggle.html
<!DOCTYPE html>
<!--[if lt IE 7]>      <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]>         <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]>         <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js">
<!--<![endif]-->

<head>
    <meta charset="utf-8">

## ReturnISOdate.sql
/*
  Replace "getdate()" with whatever date you need i.e. a field
*/
select convert(nvarchar(17),getdate(),127)+'00.00Z' as myISOdate
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	ALTER PROCEDURE [dbo].[sp_application_upsert]
	@Id int = null,
	@Name nvarchar(255) = null,
	@Description nvarchar(255) = null,
	@OperationalStatusCode nvarchar(255) = null,
	@OperationalStatusDesc nvarchar(255) = null,
	select dense_rank() over(order by ID, I.N) as ID,
	F.N.value('(Text/text())[1]', 'varchar(max)') as Name,
	F.N.value('(Value/text())[1]', 'varchar(max)') as Value
	into #T
	from YourTable as T
	cross apply T.XMLCol.nodes('/Items/Item') as I(N)
	cross apply I.N.nodes('FormItem') as F(N)

	declare @SQL nvarchar(max)
	declare @Col nvarchar(max)
	SELECT TOP (1)TX.[LIN_ItemNumber_PRI_02] as 'AmazonCode', TX.[SAP_ItemNumber] as 'ItemCode',
	TX.RFF_ReferenceNumber,
	TY.CardCode AS 'CardCode'

	FROM [MY_STAGING].[dbo].[MS-SQL-TABLE] TX
	inner join OPENQUERY ("hana-b1",'SELECT T0."AddID", T0."CardCode" from "A-SAP-HANA-DB"."OCRD" T0') TY on TX.RFF_ReferenceNumber = TY.AddID
	WHERE [SAP_ItemNumber] IS NULL
	/*
	To directly add a file to blob
	(this example was a 120mb file)
	*/
	INSERT INTO [dbo].[TableWithBlob](Id, ProductId, Filename, [ActualFile])
	SELECT 1,1,'filename',BulkColumn
	FROM OPENROWSET(BULK N'c:\clients\test.exe', SINGLE_BLOB) as AnyAliasFieldYouLike


	/*
	/*
	This was from another source, probably good old Stackoverflow! but capturing here for quick reference

	This script will create a calendar table, generate a stored proc to populate it working out weekends and also if you provide
	a set of holiday dates, it will add those too.

	The end result is a couple of select statements that allows you to pass a date (todays date as default), then add/subtract
	number of days to calculate the next working date based on the above weekends and/or holidays
	*/
	CREATE TABLE [dbo].[Calendar](
	/*
	Quick query to compare a count total at header level to line count of successful rows i.e. 1 -> Many integrity check
	to ensure the correct row count of lines matches header
	*/

	select
	sph.Id,
	sph.Filename,
	sph.Status,
	sph.CreatedAt,
	/*
	Using a CTE, we can iterate through to get the previous row in order to calculate and keep
	a running total
	*/
	WITH PrevRow
	AS (SELECT Acolumn,
	Bcolumn,
	HoldingColumn = Bcolumn,
	RunningTotal = HoldingColumn
	FROM Atable
	/*
	Get date as Monday of current week.. just change the "0" in the last param to whichever day you need
	i.e. SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) would return date as Wednesday of current week

	Handy to do date between or greater than checks
	*/
	SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
	<!DOCTYPE html>
	<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
	<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
	<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
	<!--[if gt IE 8]><!-->
	<html class="no-js">
	<!--<![endif]-->

	<head>
	<meta charset="utf-8">
	/*
	Replace "getdate()" with whatever date you need i.e. a field
	*/
	select convert(nvarchar(17),getdate(),127)+'00.00Z' as myISOdate