Let´s see best practices implemented on creating Stored procedures. So when we create the procedure we must implement some kind of error handling to control. So let´s see what is the syntax:
Create Procedure Nametheprocedure
-- Parameters input or output
As
BEGIN TRY
Begin Transaction
-- Commands
commit transaction -- If it runs without error commit the transaction
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
Rollback
END CATCH
GO
So these is the syntax and bellow is an example
USE AdventrureWorks
go
CREATE PROCEDURE Production.AddReview
@ProductID int,
@ReviewerName nvarchar(50),
@EmailAddress nvarchar(50),
@Rating int,
@Comments nvarchar(3850)
as
BEGIN TRY
Begin transaction;
INSERT Production.ProductReview (ProductID, ReviewerName, EmailAddress, Rating, Comments)
VALUES (@ProductID, @ReviewerName, @EmailAddress, @Rating, @Comments)
commit transaction;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE()
rollback transaction;
END CATCH
GO
-- Test stored procedure with error handling
EXECUTE Production.AddReview 715, 'Doug', 'doug@siteexample.com', 10, 'Great product!'
GO
See you Soon :)
Related Posts:
Showing posts with label Stored Proc. Show all posts
Showing posts with label Stored Proc. Show all posts
Stored Procedure to Copy Files
Stored procedure to help you out to copy files see the code bellow
USE [aspnetdb] -- Replace with your DB
GO
/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_copyfile]
@msg as varchar(200),
@input as varchar(100)
,@retorno as varchar(200) output
as
Begin
declare @drive as varchar(100)
declare @drive_dest as varchar(100)
declare @path as varchar(800)
declare @path_dest as varchar(800)
declare @cmd as varchar(800)
declare @ext as char(3)
set @path_dest='\Dest_Path\'
-- Para Exemplo, não esquecer barra de directorio final
-- Do not forget of blackslash
--
set @path='\PROGRA~1\DATA\'
set @drive='c:'
set @drive_dest='d:'
set @cmd='copy '+@drive+@path+@msg+@ext
set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))
+(select cast(month(getdate()) as varchar(2)))
+(select cast(year(getdate()) as varchar(4)))
+replace((select convert(varchar(8),getdate(),108)),':','')+@ext
-- Para Testar Caminhos e Visualizar
-- For testing paths and to show
-- select @cmd
exec master..xp_cmdshell @cmd ,no_output
Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))
+(select cast(month(getdate()) as varchar(2)))
+(select cast(year(getdate()) as varchar(4)))
+replace((select convert(varchar(8),getdate(),108)),':','')+@ext
end
