Default structure of a SQL Server procedure [closed]

Question

Default structure of a SQL Server procedure [closed]

Navigation

#1 by (1 votes)

1

I have a structure template, I would like to know if it is suitable for a good standard.

USE [teste]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sprEstruturaPadrao] 
   --declaração de variáveis
   @idusuario int

AS
BEGIN
   --abre a transação para update e insert
   --BEGIN TRAN sprEstruturaPadrao;

   if(@idusuario > 0)
   begin
       --select
       select * from TB_USUARIO where idusuario = @idusuario

      if(@@ERROR <> 0)
         PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(20));
         -- finaliza transação para update e insert
         --  ROLLBACK TRAN sprEstruturaPadrao 
      else
           PRINT 'tudo certo';
         -- finaliza transação para update e insert
         --  COMMIT TRAN  sprEstruturaPadrao


   end


END

sql sql-server

asked by anonymous 04.05.2017 / 16:22

1 answer

How can I tell the compiler that it is an IEEE 754? How to insert .php page inside an HTML element with JS

score 1 · Answer 1

Some suggestions:

(1) use the

set NOCOUNT on

at the beginning of the procedure, before any other command.

(2) return information about whether the procedure executed with or without errors through RETURN valor . If there was an error, the suggestion is that the returned value is negative. For example, if error 488 occurred in the SQL statement, the returned value is -488.

(3) Do not use SELECT *

(4) error handling using TRY..CATCH

(5) Standard how the T-SQL statements will be spelled out. In the example you posted, the "select" is in lowercase but the "PRINT" is uppercase, which demonstrates the need for standardization.

Suggested Reading: My stored procedure "best practices" checklist , by Aaron Bertrand.