Stored ProceduresCode at client side
The following are disadvantages when we keep the code at client side 1)network traffic is high 2)Maintenance of the code is difficult 3)No security for the code at the client side that means code can be tampered easily 5)Performance of the code is slow Code at Client Side To overcome the above issues ,we need to centralize the code or keep the code at server side To keep the code at server , we have following options views(no input parameters) SP(unique feature called cache execution plan) Functions(only select; no I/U/D) STORED PROCEDURE:- stored procedure is nothing but set of pre-compiled t-sql statements stored on the db server. A stored procedure is a saved collection of transact-sql statements or a reference to a Microsoft .net framework common language runtime(CLR) method that can take and return user-supplied parameters. Procedures can be created for permanent use or for temporary use within a session, local temporary procedure, or for temporary use within all sessions, global temporary procedure. Stored procedure can also be created to run automatically when an instance of sql server starts. Types of SP's 1)system defined sp's sp_help storedProcName sp_helpdb dbName sp_HelpText storedProcName sp_helpindex storedProcName 2) User Defined Stored Procedures CREATE PROCEDURE <Proc Name> ( INPUT --To pass the data to sp OUTPUT --To return from SP ) AS BEGIN --Variables ( @id ) --DDL --DML --Programming (declare @name varchar(50)) --call a function --call a sp --RETURN stmt END GO --CALL THE SP ------------------------------ EXEC <SP NAME> ‘Asia’ Stored Procedure Example:- CREATE PROCEDURE ContinentEmp ( @Continent varchar(100) ---input parameter ) as begin SELECT EmpId, EmpName, EmpIncome, Continent, Country, EmpLoc FROM Employee WHERE (Continent = @Continent) AND (Country = @Country) and (Location =@Location) End Exec ContinentEmp ‘ASIA’ Data Loading stored procedure:- CREATE PROCEDURE DataLoadingEmp ( @EmpId int ,@EmpName varchar(20) ,@EmpSal float ) as begin INSERT INTO EMPLOYEE(EmpId,EmpName,EmpSal) Values(@EmpId,@EmpName,@EmpSal) End EXEC DataLoadingEmp 1,’abhi’,5000 Merge example:- Using try catch in SQL Server stored procedures CREATE PROCEDURE uspTryCatchTest AS BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH Execute uspTryCatchTest ERROR HANDLING EXAMPLE:- create PROCEDURE ErrorHandling AS BEGIN BEGIN TRY BEGIN TRANSACTION; SELECT 1/0 SELECT 'TRY BLOCK' select @@TRANCOUNT COMMIT TRANSACTION; END TRY BEGIN CATCH select 'CATCH BLOCK' SELECT @@TRANCOUNT IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorLine INT = ERROR_LINE(); DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); DECLARE @ErrorState INT = ERROR_STATE(); PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10)); PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10)); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END; execute ErrorHandling TRY CATCH WITH COMMIT/ROLLBACK declare @Empid INT declare @EmpTax float set @Empid = 8 set @EmpTax = 6000 BEGIN TRY BEGIN TRANSACTION; UPDATE Employee SET EmpSalary = EmpSalary-@EmpTax WHERE Empid = @Empid; SELECT 'TRY BLOCK' select @@TRANCOUNT COMMIT TRANSACTION; END TRY BEGIN CATCH select 'CATCH BLOCK' SELECT @@TRANCOUNT IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH Passing multiple values through single parameter in stored procedure Which is usefull in ssrs report alter procedure SampleEmployee ( @EmpId nvarchar(max) ) as begin declare @sql varchar(max) SET @EmpId = REPLACE(@EmpId,',',''',''') ‘6’,’7’,’8’ set @sql = 'select EmpId,EmpName,EmpLoc,EmpContactNumber,EmpSal,EmpCountry,EmpContinent,EmpEmail from SampleEmp where EmpId in (''' + @EmpId + ''')' exec(@sql) end Execute SampleEmployee '6,7,8' |
|