This is a sample for select and insert stored procedures

Template for a Stored Procedure used by TraditionalTemplate: ghulamostafa

-- =============================================
-- Author:		  Ghulam Mustafa
-- Create date: 30-Jun-2017
-- Description:	Here goes the description for Select Stored Procedure
-- =============================================
CREATE PROCEDURE SP_NameOfStoredProcedureForSelect
	-- Add the parameters for the stored procedure here
	@Action varchar(50) = NULL
	/*Here goes the rest of the parameters*/
	,@result int = 0 output
	,@message nvarchar(150) = NULL output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  SELECT	FirstName, LastName
	FROM	  PersonTable

	SET @result = 1
	SET @message = 'Message back to front end'
END


-- =============================================
-- Author:		Ghulam Mustafa
-- Create date: 30-Jun-2017
-- Description:	Here goes the description for Insert/Update Stored Procedure
-- =============================================
CREATE PROCEDURE SP_NameOfStoredProcedureForInsert
	-- Add the parameters for the stored procedure here
	@Action varchar(50) = NULL
	/*Here goes the rest of the parameters*/
	,@result int = 0 output
	,@message nvarchar(150) = NULL output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    BEGIN TRY
	  BEGIN TRANSACTION
		  INSERT INTO
		  PERSONTABLE
		  (FirstName, LastName)
		  VALUES
		  ('Ghulam', 'Mustafa')

		  SET @result = 1
		  SET @message = 'Message back to front end'
	COMMIT TRANSACTION
	END TRY
	
  BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION
		END
		SET @result = 0
		SET @message = ERROR_MESSAGE() /*This will return the error that caused this exception*/
	END CATCH	
END