How can i increase the efficiency of this dynamic sp

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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

brunda k13y ago
Accepted answer
(cast(CONVERT(varchar(10),[DateTime],112) AS datetime) this is incomplete, instead use (cast(CONVERT(varchar(10),[DateTime],112) AS datetime) .

,@defn,@IPAddress,@DateTimeFrm,@DateTimeTo,@eventtype,@objectname,@objecttype this is not needed