Example SQL Output - mpavey/sql-code-gen GitHub Wiki

procs.sql

CREATE PROCEDURE [dbo].[Fields_List]
(
	@FieldID 	INT = 0
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
 
	-- get data
	SELECT		FieldID,
				FarmID,
				Field,
				Acres
	FROM		Fields
	WHERE		FieldID = 
					CASE
						WHEN @FieldID > 0 THEN @FieldID
						ELSE FieldID
					END
	ORDER BY	FieldID
END
GO

CREATE PROCEDURE [dbo].[Fields_Save]
(
	@FieldID 	INT,
	@FarmID 	INT,
	@Field 		VARCHAR(100),
	@Acres 		DECIMAL(6, 2)
)
AS
BEGIN
	-- check to see if record exists
	IF EXISTS (SELECT FieldID FROM Fields WHERE FieldID = @FieldID)
	BEGIN
		-- update
		UPDATE	Fields
		SET		FarmID = @FarmID,
				Field = @Field,
				Acres = @Acres
		WHERE	FieldID = @FieldID
	END
	ELSE
	BEGIN
		-- insert
		INSERT INTO	Fields
			(FarmID, Field, Acres)
		VALUES
			(@FarmID, @Field, @Acres)
 
		-- get identity value
		SET @FieldID = SCOPE_IDENTITY()
	END
 
	-- return value
	RETURN @FieldID
END
GO

CREATE PROCEDURE [dbo].[Fields_Delete]
(
	@FieldID	INT
)
AS
BEGIN
	DELETE
	FROM	Fields
	WHERE	FieldID = @FieldID
END
GO