December 10, 2001 at 4:39 pm
Steve, thanks for the help with adding go above the insert. After I did that I came across another problem. I am going to be using a parameter in the procedure. After adding the GO to the procedure I get a problem. The syntax checker in SQL Server says error 137 must declare variable @menu. I did declare the variable. I just assume I should be declaring somewhere else because I added the Go above the INSERT. Here's the complete procedure that totals up certain website hits from a logging database. Where should I declare the variable. Thanks.
CREATE PROCEDURE catnews @menu varchar(30) AS
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Curl')
DROP TABLE Curl
Create table Curl (url varchar(300), access datetime)
Go
INSERT INTO Curl
Select (S.Host_Name + P.Title) as Url, c.access_time as access
From Workstations W, Connections C, Sites S, Pages P
Where W.IP_Address = @menu and (W.Workstation_Id = C.Workstation_Id) and (C.Site_ID = S.Site_Id) and (C.Page_Id = P.Page_Id)
and S.Host_name LIKE 'www.webbooth.com'
SELECT url =
(case url
when 'www.webbooth.com/kiosk/pbd/redirectkcal9.asp' then 'Kcal 9 button'
when 'www.webbooth.com/kiosk/pbd/redirectlatimes.asp' then 'LA Times button'
when 'www.webbooth.com/kiosk/pbd/redirectcnn.asp' then 'CNN button'
when 'www.webbooth.com/kiosk/pbd/redirectwnews.asp' then 'Worldwide News button'
when 'www.webbooth.com/kiosk/pbd/redirectyahoofinance.asp' then 'Yahoo Finance button'
when 'www.webbooth.com/kiosk/pbd/redirectspeedvision.asp' then 'Speedvision button'
when 'www.webbooth.com/kiosk/pbd/redirectnba.asp' then 'NBA button'
when 'www.webbooth.com/kiosk/pbd/redirectnfl.asp' then 'NFL button'
when 'www.webbooth.com/kiosk/pbd/redirectmlb.asp' then 'MLB button'
when 'www.webbooth.com/kiosk/pbd/redirectespn.asp' then 'ESPN button'
end),
SUM(CASE WHEN datediff(day, access, getdate()) < 7 THEN 1 ELSE 0 end) AS 'count'
FROM Curl WHERE url like '%webbooth%redirect%kcal9%' or url like '%webbooth%redirect%latimes%' or url like '%webbooth%redirect%cnn%'
or url like '%webbooth%redirect%wnews%' or url like '%webbooth%redirect%yahoofinance%'or url like '%webbooth%redirect%speedvision%'
or url like '%webbooth%redirect%NBA%' or url like '%webbooth%redirect%NFL%'or url like '%webbooth%redirect%MLB%'
or url like '%webbooth%redirect%ESPN%'
group by url
order by 'count' desc
GO
December 10, 2001 at 6:37 pm
Sometimes just asking the question triggers the answer. Sorry for wasting some cyberspace with this posting, but I've had a hell of a time figuring things out today. Anyway for those who don't know, which I hope is maybe one or 2 of you. I needed to put this " DECLARE @menu varchar(30) "
after the GO statement before the INSERT INTO Curl statement. Thanks
December 11, 2001 at 10:23 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply