Creating all SPs

The last missing piece in the examples is, how all the default stored procedures look like, when you create all CRUD operations.

Well, lets do this and look at the results! We use the same command line as we did in the Creating Joins example, but this time, we set sp:* instead of sp:*

qss mssql Account sp:* c:loginname,nvarchar(64),nnu c:email,nvarchar(128),nnu c:birthday,datetime,nn fk:isocountry j:isocountry.name

We skip all source code except for the stored procedures generate:

View the `Get` procedures generated

CREATE OR ALTER PROCEDURE [AccountGetAll] ( 
	@VALID		SMALLINT = 1
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SELECT
		T.ACCOUNTID,
		T.ISOCOUNTRYID,
		J1.NAME ISOCOUNTRYNAME,
		T.LOGINNAME,
		T.EMAIL,
		T.BIRTHDAY,
		T.VALID,
		T.MOD_USER,
		T.MOD_TIMESTAMP,
		T.CR_USER,
		T.CR_TIMESTAMP
	FROM ACCOUNT T
	INNER JOIN ISOCOUNTRY J1 ON J1.ISOCOUNTRYID = T.ISOCOUNTRYID
	WHERE 
		T.VALID = @VALID

END
GO

CREATE OR ALTER PROCEDURE [AccountGetByAccountID] ( 
	@ACCOUNTID	BIGINT,
	@VALID		SMALLINT = 1
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SELECT
		T.ACCOUNTID,
		T.ISOCOUNTRYID,
		J1.NAME ISOCOUNTRYNAME,
		T.LOGINNAME,
		T.EMAIL,
		T.BIRTHDAY,
		T.VALID,
		T.MOD_USER,
		T.MOD_TIMESTAMP,
		T.CR_USER,
		T.CR_TIMESTAMP
	FROM ACCOUNT T
	INNER JOIN ISOCOUNTRY J1 ON J1.ISOCOUNTRYID = T.ISOCOUNTRYID
	WHERE 
		T.VALID = @VALID
	AND T.ACCOUNTID = @ACCOUNTID

END
GO

CREATE OR ALTER PROCEDURE [AccountGetByIsocountryID] ( 
	@ISOCOUNTRYID	BIGINT,
	@VALID		SMALLINT = 1
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	SELECT
		T.AccountID,
		T.ISOCOUNTRYID,
		J1.NAME ISOCOUNTRYNAME,
		T.LOGINNAME,
		T.EMAIL,
		T.BIRTHDAY,
		T.VALID,
		T.MOD_USER,
		T.MOD_TIMESTAMP,
		T.CR_USER,
		T.CR_TIMESTAMP
	FROM Account T
	INNER JOIN ISOCOUNTRY J1 ON J1.ISOCOUNTRYID = T.ISOCOUNTRYID
	WHERE 
		T.VALID = @VALID
	AND T.ISOCOUNTRYID = @ISOCOUNTRYID

END
GO

View the `Insert` procedure generated

CREATE OR ALTER PROCEDURE [AccountInsert] ( 
	@ISOCOUNTRYID	BIGINT,
	@LOGINNAME	NVARCHAR(64),
	@EMAIL	NVARCHAR(128),
	@BIRTHDAY	DATETIME,
	@VALID		SMALLINT = 1
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	INSERT INTO ACCOUNT (
		ISOCOUNTRYID,
		LOGINNAME,
		EMAIL,
		BIRTHDAY,
		VALID, 
		MOD_USER, MOD_TIMESTAMP, CR_USER, CR_TIMESTAMP)
	VALUES (
		@ISOCOUNTRYID,
		@LOGINNAME,
		@EMAIL,
		@BIRTHDAY,
		CASE
			WHEN @VALID IN (0,1) THEN @VALID
			WHEN @VALID IS NULL			  THEN 1
			ELSE 0
		END,
		CURRENT_USER, CURRENT_TIMESTAMP, CURRENT_USER, CURRENT_TIMESTAMP
	)

END
GO

View the `Update` procedure generated

CREATE OR ALTER PROCEDURE [AccountUpdate] ( 
	@ACCOUNTID	BIGINT,
	@ISOCOUNTRYID	BIGINT,
	@LOGINNAME	NVARCHAR(64),
	@EMAIL		NVARCHAR(128),
	@BIRTHDAY	DATETIME,
	@VALID		SMALLINT = 1
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	UPDATE [ACCOUNT] SET
		ISOCOUNTRYID = @ISOCOUNTRYID,
		LOGINNAME = @LOGINNAME,
		EMAIL = @EMAIL,
		BIRTHDAY = @BIRTHDAY,
		VALID = CASE
			WHEN @VALID IN (0,1) THEN @VALID
			ELSE 0
		END, 
		MOD_USER		= CURRENT_USER,
		MOD_TIMESTAMP	= CURRENT_TIMESTAMP
	WHERE
		ACCOUNTID = @ACCOUNTID

END
GO

View the `Delete` procedure generated

CREATE OR ALTER PROCEDURE [AccountDelete] ( 
	@ACCOUNTID	BIGINT
) AS
BEGIN
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	DELETE FROM [ACCOUNT]
	WHERE
		ACCOUNTID = @ACCOUNTID

END
GO