Standard structure of a SQL Server database

Asked

Viewed 530 times

1

I have a model structure, I wonder if it is suitable to 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

  • It will fail every time you run a first time when there is no such thing procedure. Could start with a if isnull(object_id ... drop and instead of being alter would always be a create.

  • this model had already been created, it was being changed only

1 answer

1

Some suggestions:

(1) use the command

set NOCOUNT on

at the beginning of the procedure, before any other.

(2) return information on whether the procedure was 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) perform error handling using TRY..CATCH

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

Reading suggestion: My stored Procedure "best Practices" Checklist, of Aaron Bertrand.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.