Updates from September, 2007 Toggle Comment Threads | Keyboard Shortcuts

  • ThomasPowell 12:40 pm on September 28, 2007 Permalink | Reply  

    1997 Toyota Corolla Parts Store 

    Toyota Parts and Books on Amazon.com

     
  • tech0x20 10:23 am on September 11, 2007 Permalink | Reply  

    Calculate average daily mileage required to make yearly goal 

    This might be useful if you’re programming a running site. For me, however, I wanted to write this calculation in SQL Server to expand my knowledge some.


    --Calculates the average daily mileage needed to meet a target yearly mileage goal, based on current mileage.
    CREATE FUNCTION averageDistanceNeeded (@targetMileage decimal(10,4), @currentMileage decimal(10,4))
    RETURNS decimal(10,4)
    AS
    BEGIN
    Declare @daysLeft decimal(10,4)
    Declare @dt datetime

    SELECT @dt = dt from openquery
    (
    "my-server",
    'select dt = getdate()'
    )
    select @daysLeft = datediff(day, @dt, CAST (CAST (YEAR(@dt) AS VARCHAR(4)) + '-12-31' AS DATETIME))
    return CONVERT(decimal(10,4), (@targetMileage - @currentMileage) / @daysLeft)
    END
    GO

    --Get average daily mileage needed to reach 2000 miles, based on a current mileage
    -- of 960.2
    select dbo.averageDistanceNeeded(2000.0, 960.2)
     
  • tech0x20 9:53 am on September 6, 2007 Permalink | Reply  

    Further refined version of code searching 

    I have now included views in the search code version. Both functions now return the object type the result was found in.


    --Searches stored procedures, functions and views for code
    -- containing the stringtosearch
    CREATE FUNCTION findTextInCode (@StringToSearch varchar(100))
    RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))
    AS
    BEGIN
    SET @StringToSearch = '%' + @StringToSearch +'%'
    INSERT @fNames
    SELECT DISTINCT @stringtosearch, SO.Name, SO.Type
    FROM sysobjects SO (NOLOCK)
    INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
    AND (SO.Type = 'P' OR SO.Type LIKE '%F%' OR SO.Type = 'V')
    AND SC.Text LIKE @stringtosearch
    ORDER BY SO.Name
    RETURN
    END
    GO


    --Searches column names from stored procedure parameters and function,
    -- user tables, and view columns
    CREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))
    RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))
    AS
    BEGIN
    SET @StringToSEarch = '%' + @StringToSearch + '%'
    INSERT @fColumnNames
    SELECT DISTINCT SO.NAME, SC.NAME, SO.TYPE
    FROM syscolumns SC (NOLOCK)
    INNER JOIN
    sysobjects SO (NOLOCK)
    ON SC.id = SO.id
    WHERE SC.NAME LIKE @StringToSearch
    ORDER BY SC.Name
    RETURN
    END
     
c
Compose new post
j
Next post/Next comment
k
Previous post/Previous comment
r
Reply
e
Edit
o
Show/Hide comments
t
Go to top
l
Go to login
h
Show/Hide help
shift + esc
Cancel
%d bloggers like this: