PDA

Click to See Complete Forum and Search --> : [RESOLVED] Temporary Table issue

WolfShade
05-25-2008, 10:01 PM
Hello, all.

I'm working on a website for a client, and the client would like a very basic search function for the site. I'm attempting to use a temporary table in a stored procedure and conducting several searches, each search adding data to the recordset of the temp table. This is a MS-SQL Server 2003 database.

Whenever I attempt to create the stored procedure, I get an error message "#tempSearchResults already exists", even if I try to drop the table. And it happens no matter what I name the temp table, even on the first time the object exists.

Here's my code:

CREATE PROCEDURE dbo.cpwr_sys_search_content
@searchKey nvarchar(100)

AS

CREATE TABLE #tempSearchResults (
navID INT,
snID INT,
subNavID INT,
itemTitle nvarchar(50),
itemContents ntext,
datestamp datetime
)

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp)
VALUES (
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp INTO #tempSearchResults
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%Mission%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp
)

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp)
VALUES (
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp INTO #tempSearchResults
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%contribution%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp
)
SELECT * FROM #tempSearchResults

DROP TABLE #tempSearchResults

It's been a LONG time since I even thought of using temp tables, so I'm sure I'm missing something simple, but I can't see it. Any suggestions?

Thanks,

^_^
chazzy
05-26-2008, 09:10 AM
1. Check in sql server mgmt studio if the table exists. if it does, drop it as an admin.

2. You missed a keyword in your create statement

CREATE TEMP TABLE #tempSearchResults


3. this sql

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp)
VALUES (
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp INTO #tempSearchResults
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%Mission%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp
)

should actually be this sql

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp)
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp INTO #tempSearchResults
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%Mission%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp


When doing an INSERT INTO... SELECT... you don't use the values clause.

4. I think the temp table will actually slow performance in this case, since you're actually working with the same data, just different filters. unless it's going to get more complex later on.
WolfShade
05-26-2008, 05:26 PM
1. Check in sql server mgmt studio if the table exists. if it does, drop it as an admin.

2. You missed a keyword in your create statement

CREATE TEMP TABLE #tempSearchResults


3. this sql... TRIM
should actually be this sql

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp)
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp INTO #tempSearchResults
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%Mission%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp


When doing an INSERT INTO... SELECT... you don't use the values clause.

4. I think the temp table will actually slow performance in this case, since you're actually working with the same data, just different filters. unless it's going to get more complex later on.
Hi, chazzy. Thanks for the response.

1. I've been using Enterprise Manager for everything, and have very little experience with srvr mgmt studio express. However, I have set studio express to access this database, as well. How do I check for and drop the temp table?

2. I did as you suggest, and got an error message. I will 'print screen' it and include the image of the error message.

3. See Answer #2, same thing.

4. I don't _know_ that it will become more complex in the future, but there is _always_ that potential. I'd love for it to be more efficient; what do you recommend?

Thanks,

^_^
WolfShade
05-26-2008, 05:48 PM
I think I got it fixed.


/*
05/25/2008 - Retrieves searched keyword items
*/

CREATE PROCEDURE dbo.cpwr_sys_search_content
@searchKey nvarchar(100)

AS

CREATE TABLE #tempSearchResults (
navID INT,
snID INT,
subNavID INT,
itemTitle nvarchar(50),
itemContents ntext,
datestamp datetime,
searchAppears nvarchar(50)
)

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp, searchAppears)
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp, 'Title'
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemTitle LIKE '%'+@searchKey+'%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp

INSERT into #tempSearchResults (navID, snID, subNavID, itemTitle, itemContents, datestamp, searchAppears)
SELECT subNav.navID, subNav.snID,
uCon.subNavID, uCon.itemTitle, uCon.itemContents, uCon.datestamp, 'Content'
FROM cpwr_Usr_Content uCon JOIN cpwr_Usr_Nav_Sub subNav ON subNav.subNavID = uCon.subNavID
WHERE uCon.itemContents LIKE '%'+@searchKey+'%' AND uCon.isActive = 1 AND subNav.isActive = 1
ORDER BY subNav.navID, subNav.snID, uCon.datestamp

SELECT * FROM #tempSearchResults

DROP TABLE #tempSearchResults
GO

This has not yet produced any errors. Granted, it can be redundant because it's searching both title and content for the keywords, but I think the client will be okay with that.

However, if you still have any suggestions (ie, your #4) on how to increase efficiency, it would be _greatly_ appreciated.

Thanks, again, chazzy for your help.

^_^
chazzy
05-26-2008, 10:10 PM
sorry. for #2 my brain slipped slightly and forgot that sql server doesn't use the temp keyword :-(

i'm curious though, was the table always there? did you create it by accident? i have seen once or twice in the past, where the temp table will be created, and won't be released because of some weird database pooling issue.

have you considered keeping a full text search key on the columns?
WolfShade
05-27-2008, 12:17 AM
sorry. for #2 my brain slipped slightly and forgot that sql server doesn't use the temp keyword :-(

i'm curious though, was the table always there? did you create it by accident? i have seen once or twice in the past, where the temp table will be created, and won't be released because of some weird database pooling issue.

have you considered keeping a full text search key on the columns?
No worries RE: TEMP keyword.

I don't know if the table was always there, or not. Every time I would use QA to test (not creating the SP, just executing the code), it didn't matter if I ran the CREATE or DROP, I would get an error message - ie, if I ran the DROP, "Object xxx doesn't exist in database", then when I ran the CREATE I would get "Object xxx already exists in database". Running in circles. (shrug)

I've thought about making this a Verity search, but I've never worked with Verity searches, before, and don't have a lot of time to waste on this. Don't know anything about "full text search key on columns". This is my first search function.

Thanks,

^_^
chazzy
05-27-2008, 07:19 AM
http://msdn.microsoft.com/en-us/library/ms345119.aspx

Maybe that link would help a bit. It has an obligatory 'What can FTS do for me?' section.
mattyblah
05-28-2008, 01:20 AM
if object_id('tempdb..<tablename>') is not null
drop table <tablename>


On the other hand, since you could have multiple people running this, it's probably better to use a table variable.
Something like:

declare @temp table
(
)

with the table definition between the ().
mattyblah
05-28-2008, 01:22 AM
sorry, didn't mean to post and then post again. you could probably consolidate
WHERE uCon.itemTitle LIKE '%'+@searchKey+'%'
and
WHERE uCon.itemContents LIKE '%'+@searchKey+'%'

to be this
WHERE (uCon.itemTitle LIKE '%'+@searchKey+'%' or uCon.itemContents LIKE '%'+@searchKey+'%')