I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit datetime
AS
SET NOCOUNT ON
DECLARE @.tempDate datetime
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END
DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (' + @.ntid + ', ' + @.page + ', ' + @.thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
end
else
begin
DECLARE @.theColumn varchar (100)
SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Exec @.theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
Exec @.insString
end
GO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45
Incorrect syntax near the keyword 'Exec'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50
Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else
An alternative is finding out how to do something like an if not exists()
As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.
|||
LD50:
At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
elseAn alternative is finding out how to do something like an if not exists()
As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.
That do nothing must actually have been a slightly larger error, because when I changed it to declare a small variable (basically a bit, just to avoid too much memory usage), 2 of the errors went away. I'm still having ti gripe at me about the Exec (@.theColumn) line. I don't think in this case a temporary table will do the trick only because I'm basically only trying to get one value, which is the current value of the month counter. Any other ideas?
|||I'm not quite sure what you're trying to do, or what the column names in tbltracking are, so you'll have to modify this a bit. But it will compile.
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else
begin
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
An alternative is finding out how to do something like an if not exists()
That's exactly how you do it. IF NOT EXISTS(SELECT ...)
|||Oh, THANK you! I did have to slightly modify what you put in there, but you had the gist of it, so that's awesome. I would go ahead and mark an answer here, and I probably will since you answered that concern, but I was testing the logic, and trying to input the same record twice, which should have simply done nothing, but instead it inserted a duplicate record. Any idea why the logic failed me there?
|||well why don't you paste up your new code and we'll let you know...
A good troubleshooting tip is to add some select statements in each part of the if. That way you can run it and see which if statement is firing and/or see if the columns in the table contain the data you expect.
Example:
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
select * from tblTracking where lastVisit = @.thisHit
DECLARE @.theColumn1 varchar (100)
end
else
begin
select * from tblTracking
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
select * from tblTracking
Exec @.insString
end
I also noticed that your if is illogical
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
should be:
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
|||
I also noticed that your if is illogical
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
should be:
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
Actually, I noticed that myself and had already corrected it... *sheepish shrug*
OK, well I've added in a SELECT statement for the 'do-nothing' branch of logic and changed the two Exec statements to Print statements so I don't get a ton of duplicate records. But the logic is still somehow faulty, as I'm using the Query Analyzer to test it, and trying to input the same record that already exists in the table still prints out the INSERT string, instead of the do-nothing SELECT statement. Here's what I have now, see if you can help me figure out what the problem is...
CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit smalldatetime
AS
SET NOCOUNT ON
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END
DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking(ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (''' + @.ntid + ''','' ' + @.page + ''', ''' + Convert(varchar(15), @.thisHit, 101) + ''', ''1'', ''1'')'
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
DECLARE @.bs bit
SELECT * FROM tblTracking where lastVisit = @.thisHit
end
else
begin
DECLARE @.theString varchar (100)
SET @.theString = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND page = @.page'
CREATE TABLE #theColumn (theColumn varchar (100))
INSERT INTO #theColumn
Exec @.theString
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND page = @.page) + 1
SET @.monthCount = (SELECT theColumn FROM #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + Convert(varchar(15), @.thisHit, 101) + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND page = @.page'
Print @.updString
end
end
else
begin
Print (@.insString)
end
GO
Woo! Nevermind, I have finally figured it out. Turns out I needed to use LIKEs instead of =s when I was comparing the varchar values, and I needed to throw in some CASTs to turn some of the ints into varchars for the UPDATE string. If anyone cares to see the corrected, working code, I will post it, but it is working now.
Thanks for the help!
No comments:
Post a Comment