Stored Procedure Generation

This is an overview how how the stored procedure generation works. Take the table below when you run the generate_crud_procedures_for_tables query on a database that has the table defined in it it will generate you stored procedures that fully match the data types and names of all the columns

create table dbo.Note
(
 NoteId bigint not null identity (1, 1),
 ReferenceTypeId smallint not null,
 ReferenceEntityId bigint not null,
 Body nvarchar(1000) not null,
 CreatedByUserId int not null,
 CreatedDate datetime not null,
 IsRemoved bit not null,
 PRIMARY KEY(NoteId)
);

will generate the following stored procedures

Create

create procedure [dbo].pNote_Add
(
		@NoteId bigint OUTPUT,
		@ReferenceTypeId smallint,
		@ReferenceEntityId bigint,
		@Body nvarchar(2000),
		@CreatedByUserId int,
		@CreatedDate datetime,
		@IsRemoved bit
) AS
BEGIN
	INSERT INTO [dbo].Note
	(
		ReferenceTypeId,
		ReferenceEntityId,
		Body,
		CreatedByUserId,
		CreatedDate,
		IsRemoved
	)
	VALUES
	(
		@ReferenceTypeId,
		@ReferenceEntityId,
		@Body,
		@CreatedByUserId,
		@CreatedDate,
		@IsRemoved
	);
	SET @NoteId = SCOPE_IDENTITY();
END

Update

create procedure [dbo].pNote_Modify
(
		@NoteId bigint,
		@ReferenceTypeId smallint,
		@ReferenceEntityId bigint,
		@Body nvarchar(2000),
		@CreatedByUserId int,
		@CreatedDate datetime,
		@IsRemoved bit
) AS
BEGIN
	UPDATE
		[dbo].Note
	SET
		ReferenceTypeId = @ReferenceTypeId,
		ReferenceEntityId = @ReferenceEntityId,
		Body = @Body,
		CreatedByUserId = @CreatedByUserId,
		CreatedDate = @CreatedDate,
		IsRemoved = @IsRemoved
	WHERE
		[dbo].Note.NoteId = @NoteId;
END

Remove

create procedure [dbo].pNote_Remove
(
		@NoteId bigint
) AS
BEGIN
	DELETE FROM
		[dbo].Note
	WHERE
		[dbo].Note.NoteId = @NoteId;
END

Fetch

create procedure [dbo].pNote_Fetch
(
		@NoteId bigint
) AS
BEGIN
	DECLARE @SearchResults dbo.IntSortedList;

	IF @NoteId = 0
		INSERT INTO
			@SearchResults
		SELECT
			t.NoteId,
			ROW_NUMBER() OVER(ORDER BY t.NoteId ASC)
		FROM
			[dbo].Note t;
	ELSE
		INSERT INTO
			@SearchResults
		SELECT
			@NoteId,
			0;

	EXEC [dbo].pNote_Search @SearchResults;
END

Search

create procedure [dbo].pNote_Search
(
	@SearchResults dbo.IntSortedList READONLY
) AS
BEGIN
	SELECT
		t.NoteId,
		t.ReferenceTypeId,
		t.ReferenceEntityId,
		t.Body,
		t.CreatedByUserId,
		t.CreatedDate,
		t.IsRemoved
	FROM
		@SearchResults r
		INNER JOIN [dbo].Note t
			ON r.ItemId = t.NoteId
	ORDER BY
		r.Sequence ASC;
END