February 18, 2012 at 2:33 am
Hi,
i have table ,I want result will be like this
SELECT
Field1
,Field2
,CASE WHEN Field3 = 1 then Value1 ELSE value 2 END
,......
,......
FROM Table WHERE filed1 = @parameter1 and field2 = @parameter2 .....
instead of writing this query every time i have create a user define function to get default resultset everytime when i execute. is it a good practice or not. or any drawback for calling continues the UDF..?
Please suggest and provide some articles...
Thanks
Patel Mohamad
February 20, 2012 at 8:27 am
Are you doing any actual processing of anything in here? This looks like nothing more than a select statement? If so, then a view is probably a better choice than a UDF.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 9:49 pm
Hi,
I have done some calculation internally which is required... but that calculation has to been done each and every time i calls the procedures or write any other..
is it good exercise?
Patel Mohamad
February 21, 2012 at 6:33 am
It is hard to say without more details but you say it is just a single query? You will be a lot happier down the road using a view instead of a function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2012 at 5:46 am
Hi sean,
the UDF which i have created is
Table1 is the Clone copy of Table2 for fast process we are transfering the data from table2 to table1
hope you understand the code,
Please suggest some other better techinques to improve my skill
CREATE FUNCTION [dbo].[Function1](
@Parameter1 VARCHAR(20)
,@Parameter2 DATETIME
,@Parameter3 DATETIME
,@Parameter4 Bit = 0--1/True to get the Odometer, 0/False
,@Parameter5 BIT = 1--1/True to get the Valid GPSSignalStatus = 'A' , 0/False else all the data
,@Parameter6 BIT = 0--1/True to retrieve data from DeviceParsedDatalog table , 0/False for normal routine
)
RETURNS /*
DECLARE --*/
@TempDeviceRecords TABLE(
[Deviceregistrationid] VARCHAR(20)
,[GpsSignalStatus] VARCHAR(10)
,[Speed_KmPerHour] INT
,[CmdReplyStatus] VARCHAR(100)
,[Latitude] numeric(9, 6)
,[longitude] numeric(9, 6)
,[Placename] VARCHAR(250)
,[IgnitionStatus] bit
,[HostStatus] INT
,[Odometer_Km] INT
,[UTCLoggedDateTime] datetime
,[OtherData] VARCHAR(2000)
,[AlertCodes] VARCHAR(300)
,[WaypointId] INT
,[TT2] decimal(9, 4)
,LoggedDateTime DATETIME
,Door1 BIT
,Door2 BIT
,AC1 BIT
,Temperature_DegreesCelsius SMALLINT)
AS/*
DECLARE
@Parameter1 VARCHAR(20)
,@Parameter2 DATETIME
,@Parameter3 DATETIME
,@Parameter4 Bit
,@Parameter5 BIT
,@Parameter6 BIT
SELECT
@Parameter1 = '7781110126'
,@Parameter2 = '2012-02-16 05:00:00.000'
,@Parameter3 = '2012-02-16 23:00:00.000'
,@Parameter4 = 1
,@Parameter5 = 0
,@Parameter6 = 1
--*/
BEGIN
-- Please Keep Tab indent Proper, Always follow the step to get the procedure details
-- Press Ctrl+T and then the execute sp_helptext 'Procedure name'
DECLARE @TimeZoneDiff INT
,@NewStartDate DATETIME
,@NewEndDate DATETIME
,@DTStart DATETIME
,@DTEnd DATETIME
,@StartingODOMeter_KM INT
,@RowsCount INT
SELECT @TimeZoneDiff = MinuteDifference
FROM dbo.DevicesTimeZone WITH (NOLOCK)
WHERE DeviceRegistrationID = @Parameter1
SELECT @StartingODOMeter_KM = 0
IF @Parameter4 = 1
BEGIN
SELECT @StartingODOMeter_KM = ISNULL(StartingODOMeter_KM ,0)
FROM MasterTable WITH (NOLOCK INDEX (IX_MasterTable_DeviceRegistrationId))
WHERE DeviceRegistrationID = @Parameter1
END
SELECT
@NewStartDate = @Parameter2
,@NewEndDate = @Parameter3
,@DTStart = DATEADD(MINUTE ,-@TimeZoneDiff ,@NewStartDate)
,@DTEnd= DATEADD(MINUTE ,-@TimeZoneDiff ,@NewEndDate)
,@StartingODOMeter_KM = ISNULL( @StartingODOMeter_KM ,0)
IF (@DTStart BETWEEN GETDATE()-7 AND GETDATE()-1
AND @DTEnd BETWEEN GETDATE()-7 AND GETDATE()-1)
AND @Parameter6 = 0
BEGIN
INSERT INTO @TempDeviceRecords
SELECT
DeviceRegistrationId
GPSSignalStatus
,Speed_KmPerHour
,CmdReplyStatus
,Latitude
,Longitude
,PlaceName
,IgnitionStatus
,HostStatus
,@StartingODOMeter_KM + Odometer_Km
,Loggeddatetime AS UTCLoggedDateTime
,OtherData
,ISNULL( AlertCodes ,'')
,WaypointId
,TT2
,DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
,ISNULL( Door1 ,0)
,ISNULL( Door2 ,0)
,ISNULL( AC1 ,0)
,ISNULL( Temperature_DegreesCelsius ,0)
FROM Table1 WITH (NOLOCK ,index(idx_DeviceId_Time))
WHERE
DeviceRegistrationId = @Parameter1
AND LoggedDateTime BETWEEN @DTStart AND @DTEnd
AND (@Parameter5 = 0 OR (@Parameter5 = 1 AND GPSSignalStatus = 'A'))
ORDER BY LoggedDateTime
Select @RowsCount = @@RowCount
IF @RowsCount = 0
BEGIN
GOTO ElseCondition -- ELSEIFCondition1
END
END
ELSE
BEGIN
ElseCondition:
INSERT INTO @TempDeviceRecords
SELECT
DeviceRegistrationId
,GPSSignalStatus
,Speed_KmPerHour
,CmdReplyStatus
,Latitude
,Longitude
,PlaceName
,IgnitionStatus
,HostStatus
,@StartingODOMeter_KM + Odometer_Km
,Loggeddatetime AS UTCLoggedDateTime
,OtherData
,ISNULL( AlertCodes ,'')
,WaypointId
,TT2
,DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
,ISNULL( Door1 ,0)
,ISNULL( Door2 ,0)
,ISNULL( AC1 ,0)
,ISNULL( Temperature_DegreesCelsius ,0)
FROM Table2 WITH (NOLOCK ,INDEX(idx_ID_Time))
WHERE
DeviceRegistrationId= @Parameter1
AND LoggedDateTime BETWEEN @DTStart AND @DTEnd
AND (@Parameter5 = 0 OR (@Parameter5 = 1 AND GPSSignalStatus = 'A'))
ORDER BY LoggedDateTime
END
RETURN /*
SELECT * FROM @TempDeviceRecords
--*/
END
Thanks
Patel Mohamad
February 22, 2012 at 8:55 am
When it comes to implementing a parameter driven process that performs multiple steps and then returns a result, that should generally go in a stored procedure, not a function.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 22, 2012 at 2:21 pm
I hope you have better names than Function1, @parameter1, Table1 etc...
Seems like just about every table has NOLOCK and is using index hints. How come?
In case you never read about the ramifications of NOLOCK and the potential bad things it can do...http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
All things being equal I think this would be easier as a proc because it returns a table (and you are on sql 2005), at least I think that is what you want???
It seems this could be streamlined quite a bit but it is awfully to read. In the future if you put your code inside a code tag it will keep the formatting. The code tags can be found over the left when you are posting. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 27, 2012 at 11:11 pm
Hi Sean,
this the Actual function which has been used in many procedures and in functions,
the use of the "GetDeviceLogData_SpecifiedTime" is depend upon procedures
i need Latitude,Longitude. and in some procedures i need some other fields etc.,
so i have taken required fields in the function depend upon procedures requirement .
and if i don't use With (NOLOCK) then it takes near about ten minutes to retrieve the data, for fast process i have used it..
Hope you got the meaning.
and thanks for your post ariticle it gives a lot of information,
Thanks once again.
-- -- -- -- The function is called in below procedure(s) ,function(s)
-- -- -- -- proc_RPTEngineOnOff
-- -- -- -- proc_RPTIdleVehicle
-- -- -- -- proc_RPTDailyVehicleActivity
-- -- -- -- proc_RPTStartStop
-- -- -- -- proc_RPTVehicleStop
-- -- -- -- proc_InsertVehicleSummary
CREATE FUNCTION [GetDeviceLogData_SpecifiedTime](
@DeviceRegID VARCHAR(20)
,@StartDate DATETIME
,@EndDate DATETIME
,@ODOMeter_KM Bit = 0--1/True to get the Odometer, 0/False
,@ValidGPSSignal BIT = 1--1/True to get the Valid GPSSignalStatus = 'A' , 0/False else all the data
,@DeviceParsedDatalog BIT = 0--1/True to retrieve data from DeviceParsedDatalog table , 0/False for normal routine
)
RETURNS /*
DECLARE --*/
@TempDeviceRecords TABLE(
[Deviceregistrationid] VARCHAR(20)
,[GpsSignalStatus] VARCHAR(10)
,[Speed_KmPerHour] INT
,[CmdReplyStatus] VARCHAR(100)
,[Latitude] numeric(9, 6)
,[longitude] numeric(9, 6)
,[Placename] VARCHAR(250)
,[IgnitionStatus] bit
,[HostStatus] INT
,[Odometer_Km] INT
,[UTCLoggedDateTime] datetime
,[OtherData] VARCHAR(2000)
,[AlertCodes] VARCHAR(300)
,[WaypointId] INT
,[TT2] decimal(9, 4)
,LoggedDateTime DATETIME
,Door1 BIT
,Door2 BIT
,AC1 BIT
,Temperature_DegreesCelsius SMALLINT)
AS/*
DECLARE
@DeviceRegID VARCHAR(20)
,@StartDate DATETIME
,@EndDate DATETIME
,@ODOMeter_KM Bit
,@ValidGPSSignal BIT
,@DeviceParsedDatalog BIT
SELECT
@DeviceREGID = '7781110126'
,@StartDate = '2012-02-16 05:00:00.000'
,@EndDate = '2012-02-16 23:00:00.000'
,@ODOMeter_KM = 1
,@ValidGPSSignal = 0
,@DeviceParsedDatalog = 1
--*/
BEGIN
-- Please Keep Tab indent Proper, Always follow the step to get the procedure details
-- Press Ctrl+T and then the execute sp_helptext 'Procedure name'
DECLARE @TimeZoneDiff INT
,@NewStartDate DATETIME
,@NewEndDate DATETIME
,@DTStart DATETIME
,@DTEnd DATETIME
,@StartingODOMeter_KM INT
,@RowsCount INT
SELECT @TimeZoneDiff = MinuteDifference
FROM dbo.DevicesTimeZone WITH (NOLOCK)
WHERE DeviceRegistrationID = @DeviceRegID
SELECT @StartingODOMeter_KM = 0
IF @ODOMeter_KM = 1
BEGIN
SELECT @StartingODOMeter_KM = ISNULL(StartingODOMeter_KM ,0)
FROM Devices WITH (NOLOCK INDEX (IX_Devices_DeviceRegistrationId))
WHERE DeviceRegistrationID = @DeviceRegID
END
SELECT
@NewStartDate = @StartDate
,@NewEndDate = @EndDate
,@DTStart = DATEADD(MINUTE ,-@TimeZoneDiff ,@NewStartDate)
,@DTEnd= DATEADD(MINUTE ,-@TimeZoneDiff ,@NewEndDate)
,@StartingODOMeter_KM = ISNULL( @StartingODOMeter_KM ,0)
IF (@DTStart BETWEEN GETDATE()-7 AND GETDATE()-1
AND @DTEnd BETWEEN GETDATE()-7 AND GETDATE()-1)
AND @DeviceParsedDatalog = 0
BEGIN
INSERT INTO @TempDeviceRecords
SELECT
DeviceRegistrationId
,GPSSignalStatus
,Speed_KmPerHour
,CmdReplyStatus
,Latitude
,Longitude
,PlaceName
-- ,CASE AlertCodes WHEN 'A089' THEN PlaceName + ' - ' + SUBSTRING(OtherData ,1 ,CHARINDEX('|' ,OtherData)-1) ELSE PlaceName END AS PlaceName
,IgnitionStatus
,HostStatus
,@StartingODOMeter_KM + Odometer_Km
,Loggeddatetime AS UTCLoggedDateTime
,OtherData
,ISNULL( AlertCodes ,'')
,WaypointId
,TT2
,DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
-- DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
,ISNULL( Door1 ,0)
,ISNULL( Door2 ,0)
,ISNULL( AC1 ,0)
,ISNULL( Temperature_DegreesCelsius ,0)
FROM DailyEmailReportData WITH (NOLOCK ,index(idx_DeviceId_Time)) -- Use Table DailyEmailReportData For 131 Server
WHERE
DeviceRegistrationID = @DeviceRegID
AND LoggedDateTime BETWEEN @DTStart AND @DTEnd
AND (@ValidGPSSignal = 0 OR (@ValidGPSSignal = 1 AND GPSSignalStatus = 'A'))
ORDER BY LoggedDateTime
Select @RowsCount = @@RowCount
IF @RowsCount = 0
BEGIN
GOTO ElseCondition -- ELSEIFCondition1
END
END
-- ELSE IF
-- BEGIN
-- ELSEIFCondition1:
-- -- perform the task
-- Select @RowsCount = @@RowCount
-- IF @RowsCount = 0
-- BEGIN
-- GOTO ElseCondition --ELSEIFCondition2
-- END
-- END
ELSE
BEGIN
ElseCondition:
INSERT INTO @TempDeviceRecords
SELECT
DeviceRegistrationId
,GPSSignalStatus
,Speed_KmPerHour
,CmdReplyStatus
,Latitude
,Longitude
,PlaceName
-- ,CASE AlertCodes WHEN 'A089' THEN PlaceName + ' - ' + SUBSTRING(OtherData ,1 ,CHARINDEX('|' ,OtherData)-1) ELSE PlaceName END AS PlaceName
,IgnitionStatus
,HostStatus
,@StartingODOMeter_KM + Odometer_Km
,Loggeddatetime AS UTCLoggedDateTime
,OtherData
,ISNULL( AlertCodes ,'')
,WaypointId
,TT2
,DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
-- DATEADD(MINUTE ,@TimeZoneDiff ,LoggedDateTime) AS LoggedDateTime
,ISNULL( Door1 ,0)
,ISNULL( Door2 ,0)
,ISNULL( AC1 ,0)
,ISNULL( Temperature_DegreesCelsius ,0)
FROM DeviceParsedDatalog WITH (NOLOCK ,INDEX(idx_ID_Time))-- Use Table DeviceParsedDatalog For 0.5 Plexitech Local Server
WHERE
DeviceRegistrationID = @DeviceRegID
AND LoggedDateTime BETWEEN @DTStart AND @DTEnd
AND (@ValidGPSSignal = 0 OR (@ValidGPSSignal = 1 AND GPSSignalStatus = 'A'))
ORDER BY LoggedDateTime
END
RETURN /*
SELECT * FROM @TempDeviceRecords
--*/
END
Patel Mohamad
February 28, 2012 at 7:38 am
patelmohamad (2/27/2012)
if i don't use With (NOLOCK) then it takes near about ten minutes to retrieve the data, for fast process i have used it..
Please realize that NOLOCK is by no means a "go fast" pill. It is fraught with all sorts of issues that hopefully you realized in that blog post. You should utilize isolation instead, snapshot isolation is the more common solution for blocking like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply