JimKnopf2034

CREATE PROCEDURE [Orders].[SumAllOrdersPerCustomer] @CustomerId INT = 0
AS
SELECT SUM(OrderSum)
FROM [Orders].[Orders]
WHERE CustomerId = @CustomerId

JimKnopf2034

CREATE TABLE [dbo].[Namen] (
    [Id] INT NOT NULL PRIMARY KEY
    , [Namen] NVARCHAR(50)
    )

JimKnopf2034

CREATE SCHEMA [Orders] AUTHORIZATION [dbo]

JimKnopf2034

terraform {
  required_providers {
    docker = {
      source = "kreuzwerker/docker"
    }
  }
}

provider "docker" {}

JimKnopf2034

docker run - name mySqlServer -e ACCEPT_EULA=Y -e SA_PASSWORD=MyStrongPA55word! -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

JimKnopf2034

CREATE Type JobDef AS TABLE(
    [JobName] SYSNAME PRIMARY KEY
    , [JobId] UNIQUEIDENTIFIER
    , [Description] NVARCHAR(512) NULL
)
CREATE TYPE JobStepDef AS TABLE(
    [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , [job_name] SYSNAME NOT NULL 
    , [job_id] UNIQUEIDENTIFIER NULL

JimKnopf2034

/**
    Basic version of creating job and jobsteps on an sql server.
**/
-- create a agent job
EXEC msdb.dbo.sp_add_job
  @job_name = 'The daily test job';

-- add a jobstep to the job
EXEC msdb.dbo.sp_add_jobstep
  @job_name = 'The daily test job',

JimKnopf2034

/**
A bit tricky since it would normally run on msdb, but use statements are not allowed here
**/

-- add the job if it does not exist
DECLARE @ref_name SYSNAME

SELECT @ref_name = [name]
FROM msdb.dbo.sysjobs
WHERE [name] = 'The daily test job'

JimKnopf2034

CREATE LOGIN owi WITH PASSWORD = 'Bazinga1!' MUST_CHANGE, CHECK_EXPIRATION=ON, DEFAULT_DATABASE = DataStore ;
GO

CREATE USER owi FOR LOGIN owi
GO

ALTER ROLE db_owner ADD MEMBER owi
GO

-- Add some permissions the the schema for the newly created user

JimKnopf2034

-- Create the Schema, owned by dbo.
CREATE SCHEMA [tables] AUTHORIZATION dbo;
GO
CREATE SCHEMA Consumer AUTHORIZATION dbo;
GO