April 15, 2008 at 6:58 pm
Hello everybody.
I'm new here, so I hope I'm in the right forum section...
I have a stored procedure that generates the following error:
Must declare the scalar variable "@TempTable".
This procedure is being used as a search engine.
In this procedure, I first declare a table variable, and then select into it a table that consists of all fields in tblServers and custNum field from tblCustomers.
Then I try to LEFT JOIN this table with another table tblServersAddresses, which generates the error message.
here is the procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchServers](
@srvName nvarchar(50)=NULL,
@srvCabinet nvarchar(6)=NULL,
@srvSwitch nvarchar(20)=NULL,
@srvBkpSwitch nvarchar(20)=NULL,
@srvBkpName nvarchar(50)=NULL,
@custNum nvarchar(6)=NULL,
@IPAddress nvarchar(15)=NULL
)
AS
--BEGIN--
--SET NOCOUNT ON--
DECLARE @TempTable TABLE
(
srvID int, srvName nvarchar(50),
custID int, srvCabinet nvarchar(6),
srvKVM nvarchar(9), srvSwitch nvarchar(20),
srvPort nvarchar(10), srvBkpName nvarchar(50),
srvBkpSwitch nvarchar(20), srvBkpPort nvarchar(10),
srvComment nvarchar(50), custNum nvarchar(6)
)
INSERT INTO @TempTable
SELECT tblServers.*, tblCustomers.custNum
FROM tblServers, tblCustomers
WHERE (srvName LIKE '%'+@srvName+'%' OR @srvName IS NULL) AND
(srvCabinet LIKE '%'+@srvCabinet+'%' OR @srvCabinet IS NULL) AND
(srvSwitch LIKE '%'+@srvSwitch+'%' OR @srvSwitch IS NULL) AND
(srvBkpSwitch LIKE '%'+@srvBkpSwitch+'%' OR @srvBkpSwitch IS NULL) AND
(srvBkpName LIKE '%'+@srvBkpName+'%' OR @srvBkpName IS NULL) AND
(custNum LIKE '%'+@custNum+'%' OR @custNum IS NULL)
AND (tblServers.custID=tblCustomers.custID)
SELECT @TempTable.*, tblServersAddresses.IPAddress
FROM @TempTable
LEFT JOIN tblServersAddresses
ON @TempTable.srvID=tblServersAddresses.srvID
I have noticed that in the last part, if I change the query to something like:
SELECT *
FROM @TempTable
then I don't get the error.
Sorry for the long post, any help would be appreciated!
April 15, 2008 at 7:40 pm
SELECT @TempTable.*, tblServersAddresses.IPAddress
FROM @TempTable
LEFT JOIN tblServersAddresses
ON @TempTable.srvID=tblServersAddresses.srvID
Alias the table variable like this
SELECT tt.*, tsa.IPAddress
FROM @TempTable tt
LEFT JOIN tblServersAddresses tsa
ON tt.srvID=tsa.srvID
April 15, 2008 at 10:22 pm
ksullivan is correct, you must alias the table in order to qualify the column names.
As a side note, I hope your servers and customers tables are not huge. Any time you use a LIKE comparison with the '%' wildcard character as the first entry in a string, you are forcing a table scan.
April 16, 2008 at 6:25 am
Also, for what its worth, you're doing unecessary work, selecting into the temporary table and then selecting from the temporary table. Instead, just use the select statement that would load the temporary table as derived table and join against it in the same way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2008 at 8:15 am
The reason it's not working is because you can't have "select @TempTable.*" in a query. Also can't use the table variable name that way in Where, Having, Group By or Order By clauses. You need to give the table variable an alias in the From clause, and then use that. (Using an alias has already been mentioned, I'm just expanding on why that is.)
As Grant mentioned, you don't actually need to use a table variable here. You could just use:
SELECT tblServers.*, tblCustomers.custNum, tblServersAddresses.IPAddress
FROM tblServers, tblCustomers
left join tblServerAddresses
on tblServers.srvID = tblServersAddresses.srvID
WHERE (srvName LIKE '%'+@srvName+'%' OR @srvName IS NULL) AND
(srvCabinet LIKE '%'+@srvCabinet+'%' OR @srvCabinet IS NULL) AND
(srvSwitch LIKE '%'+@srvSwitch+'%' OR @srvSwitch IS NULL) AND
(srvBkpSwitch LIKE '%'+@srvBkpSwitch+'%' OR @srvBkpSwitch IS NULL) AND
(srvBkpName LIKE '%'+@srvBkpName+'%' OR @srvBkpName IS NULL) AND
(custNum LIKE '%'+@custNum+'%' OR @custNum IS NULL)
AND (tblServers.custID=tblCustomers.custID)
Try that, instead of using the table variable, and see if it does what you need. (I put my changes in bold font.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 8:24 am
Thanks you all for the helpful replies.
I changed the query into this:
SELECT tt.*, tsa.IPAddress
FROM
(SELECT tblServers.*, tblCustomers.custNum
FROM tblServers, tblCustomers
WHERE (srvName LIKE '%'+@srvName+'%' OR @srvName IS NULL) AND
(srvCabinet LIKE '%'+@srvCabinet+'%' OR @srvCabinet IS NULL) AND
(srvSwitch LIKE '%'+@srvSwitch+'%' OR @srvSwitch IS NULL) AND
(srvBkpSwitch LIKE '%'+@srvBkpSwitch+'%' OR @srvBkpSwitch IS NULL) AND
(srvBkpName LIKE '%'+@srvBkpName+'%' OR @srvBkpName IS NULL) AND
(custNum LIKE '%'+@custNum+'%' OR @custNum IS NULL)
AND (tblServers.custID=tblCustomers.custID)) tt
LEFT JOIN tblServersAddresses tsa
ON tt.srvID=tsa.srvID
Works fine.
Thanks again.
April 16, 2008 at 8:26 am
Glad we could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 12:44 pm
I have another question now.
Now the left join returns NULL values for servers that don't appear in the tblServersAddresses. If I want to show an empty filed, instead of NULL, should I declare a temp table here and insert into it the pervious table, and just fill the nulls with ""? Would it be reasonable?
April 16, 2008 at 12:56 pm
Take a look at IsNull() and Coalesce() in Books Online. They'll do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 1:16 pm
Thanks GSquared. I changed it into this:
SELECT temp.*, ISNULL(tsa.IPAddress, '') AS IP
FROM
(SELECT tblServers.*, tblCustomers.custNum
FROM tblServers, tblCustomers
WHERE (srvName LIKE '%'+@srvName+'%' OR @srvName IS NULL) AND
(srvCabinet LIKE '%'+@srvCabinet+'%' OR @srvCabinet IS NULL) AND
(srvSwitch LIKE '%'+@srvSwitch+'%' OR @srvSwitch IS NULL) AND
(srvBkpSwitch LIKE '%'+@srvBkpSwitch+'%' OR @srvBkpSwitch IS NULL) AND
(srvBkpName LIKE '%'+@srvBkpName+'%' OR @srvBkpName IS NULL) AND
(custNum LIKE '%'+@custNum+'%' OR @custNum IS NULL)
AND (tblServers.custID=tblCustomers.custID)) temp
LEFT JOIN tblServersAddresses tsa
ON temp.srvID=tsa.srvID
April 17, 2008 at 1:24 pm
Looks good to me!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply