Need help with my stored procedure

• actinicblue

  Mr or Mrs. 500

  Points: 575

  More actions

  September 27, 2001 at 11:19 am

  #157967

  I am creating a a web based smtp log file reporting system. I want to create a procedure that takes a column (ip) and counts the number of times a particular ip appears, then adds a temporary column to the table for that particular ip to store the count. Ultimately there will be up to 30+ temporary columns.I'm not sure how to go about doing it or if that is the best way. Here is what I have that does not seem to work the way I want it too, but may give a better idea as to what I'm trying to accomplish.

  CREATE PROCEDURE SP_SMTPSTATS AS

  SELECT count(ip) mcd

  FROM test

  where ip = '10.0.0.5'

  count(ip) mcdd

  from test

  where ip = '10.0.0.4'

  GO

• Matthew-12035

  Right there with Babe

  Points: 735

  More actions

  September 27, 2001 at 11:54 am

  #418554

  ok, you didn't give a whole heck of a lot of info, but here's a suggestion.

  Rather than add a column to a table to store IP counts, use a separate table.

  So, your SMTP log could be uploaded into a temp table, the statistics then calculated and stored into a permant SMTPStats table. So, in this example, I have a assumed that your smtp log file has already been uploaded/converted into a table called SMTP_LOG_TEMP, and that the table that contains all your smtp statistics is called SMTP_STATS. SMTP_STATS in this example has 2 fields: IP_ADDRESS, and IP_COUNT.

  -- First, get the IPs, and their counts from the latest log file

  INSERT INTO #SMTP_STATS_WORK

  SELECT IP, Count(IP) as LATEST_COUNT

  FROM SMTP_LOG_TEMP

  GROUP BY IP

  -- Second, update the SMTP_STATS table

  UPDATE SMTP_STATS

  SET a.IP_COUNT = a.IP_COUNT + b.LATEST_COUNT

  FROM SMTP_STATS a, #SMTP_STATS_WORK b

  WHERE a.IP_ADDRESS = b.IP

  -- Lastly, insert any IPs from this run, that weren't in the SMTP_STATS table already

  INSERT INTO SMTP_STATS (IP_ADDRESS, IP_COUNT)

  SELECT IP, LATEST_COUNT

  FROM #SMTP_STATS_WORK

  WHERE IP not in (SELECT IP_ADDRESS FROM SMTP_STATS)

  Good luck!

  
  
  Matthew Mamet