Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
Hello everyone,
 
Below Storedprocedure takes 15 secs to execute , please suggest how to optimize this SP?
 
 
USE [Helpdesk_Server]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertSchoolComplaint]    Script Date: 09/26/2012 10:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertSchoolComplaint]
	-- Add the parameters for the stored procedure here
	@complaintnumber		nvarchar(50)	,
	@assettypeid			int				,
	@assetid				int				,
	@schoolid				int				,
	@stateid				int				,		
	@complainantname		nvarchar(50)	,
	@complainantcontact		nvarchar(50)	,
	@complaintdescription	nvarchar(max)	,
	@remarks				nvarchar(max)	,
	@createdby				int				,
	@emailid				nvarchar(100)	,	
	@employeecomplaintname nvarchar(max),
	@employeecomplaintcity	nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @message nvarchar(20)
	begin try
	
	Insert Into [MH.Complaints]
				(
					ComplaintNumber				,
					AssetTypeId					,
					AssetId						,
					SchoolId					,					
					StateId						,					
					ReportedDate				,
					Reporteddatetime			,
					ComplainantName				,
					ComplainantContactNumber	,
					ComplaintDescription		,
					ComplaintStatusId			,
					Remarks						,
					CreatedBy					,
					isDeleted					,
					isAdminApproved				,
					EmailId									,
					EmployeeComplaint,
					EmployeeComplaintCity,
					isMailed
					
				)	
				values
				(
					@complaintnumber			,
					@assettypeid				,
					@assetid					,
					@schoolid					,					
					@stateid					,					
					GETDATE()					,
					GETDATE()					,
					@complainantname			,
					@complainantcontact			,
					@complaintdescription		,
					'5'							,
					@remarks					,
					@createdby					,
					'false'						,
					'false'						,
					@emailid 					,@employeecomplaintname ,@employeecomplaintcity	,'false'
				)
				set @message ='Yes'
			end try		
			begin catch
			set @message ='No'
			end catch
						
	Select @message as 'Message'
 
	
END
Posted 25-Sep-12 20:12pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Remove the set @message you can achieve the same result by checking the return of the insert with @IDENTITY :
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/efbe2d59-9129-455e-b723-43ceb6752d56/[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,078
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web02 | 2.8.1411022.1 | Last Updated 26 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100