arun kumar

arun kumar

  • NA
  • 18
  • 21.7k

How can i increase the efficiency of this dynamic sp

Feb 15 2013 12:53 AM
 

CREATE
procedure [dbo].[GetDetails]
@IPAddress
nvarchar(max),
@DateTimeFrm
datetime,
@DateTimeTo
datetime,
@eventtype
nvarchar(max),
@objectname
nvarchar(max),
@objecttype
nvarchar(max)
as

--Variable Declaration

Declare
@SQLQuery nvarchar(4000)
--Declare @Param nvarchar(4000)

Declare
@defn nvarchar(4000)
--

Set
@SQLQuery = 'Select * From GetDetails where (1=1)'
--Set @Param='where (1=1)'


if
(isnull(@IPAddress,'')<>'')
Set @SQLQuery = @SQLQuery + 'And (IPAddress = @IPAddress)'

if
(isnull(@DateTimeFrm,'')<>'') and (isnull(@DateTimeTo,'')<>'')
Set @SQLQuery = @SQLQuery + ' And (cast(CONVERT(varchar(10),[DateTime],112) AS datetime)>=@DateTimeFrm) and (cast(CONVERT(varchar(10), [DateTime], 112) AS datetime)<=@DateTimeTo)'

--if(isnull(@DateTimeFrm,'')<>'')
-- Set @SQLQuery = @SQLQuery + ' And (cast(CONVERT(varchar(10),[DateTime],112) AS datetime) =@DateTimeFrm)'


if
(isnull(@eventtype,'')<>'')
Set @SQLQuery = @SQLQuery + 'And (eventtype = @eventtype)'

if
(isnull(@objectname,'')<>'')
Set @SQLQuery = @SQLQuery + 'And (objectname = @objectname)'

if
(isnull(@objecttype,'')<>'')
Set @SQLQuery = @SQLQuery + 'And (objecttype = @objecttype)'

set
@defn ='@IPAddress nvarchar(max),
@DateTimeFrm datetime,
@DateTimeTo datetime,
@eventtype nvarchar(max),
@objectname nvarchar(max),
@objecttype nvarchar(max) '

--set @Param =@SQLQuery + @Param

Execute
sp_Executesql @SQLQuery,@defn,@IPAddress,@DateTimeFrm,
@DateTimeTo
,@eventtype,@objectname,@objecttype

Answers (1)