chrscote
05-28-2009, 01:42 PM
I am trying to set up a view in my SQL Server Database so I can just call the view from an ASP page. The problem I have is that I want to have one of the items in the WHERE clause as a variable entry. I am using SQL Server 2005 Management Studio. I currently have the following:
SELECT dbo.v_ecImpact.rackID, dbo.tblEC.sortOrder, dbo.tblRack.rackName, dbo.v_ecImpact.type, dbo.v_ecImpact.classID
FROM dbo.v_ecImpact INNER JOIN
dbo.tblRefDes ON dbo.v_ecImpact.refID = dbo.tblRefDes.refID INNER JOIN
dbo.tblEC ON dbo.tblRefDes.ecID = dbo.tblEC.ecID INNER JOIN
dbo.tblDocType ON dbo.tblEC.docTypeID = dbo.tblDocType.docTypeID INNER JOIN
dbo.tblRack ON dbo.tblRefDes.rackID = dbo.tblRack.rackID
WHERE (dbo.v_ecImpact.classID = 3) AND (dbo.tblEC.sortOrder < @sortOrd)
But when I try to save this query, I get an error message "Must declare the scalar variable "@sortOrd". I've tried adding
DECLARE @sortOrd int
above the SELECT statement, but then, when I try to save, the Management Studio shuts down on me.
Any and all help is greatly appreciated.
Chris
SELECT dbo.v_ecImpact.rackID, dbo.tblEC.sortOrder, dbo.tblRack.rackName, dbo.v_ecImpact.type, dbo.v_ecImpact.classID
FROM dbo.v_ecImpact INNER JOIN
dbo.tblRefDes ON dbo.v_ecImpact.refID = dbo.tblRefDes.refID INNER JOIN
dbo.tblEC ON dbo.tblRefDes.ecID = dbo.tblEC.ecID INNER JOIN
dbo.tblDocType ON dbo.tblEC.docTypeID = dbo.tblDocType.docTypeID INNER JOIN
dbo.tblRack ON dbo.tblRefDes.rackID = dbo.tblRack.rackID
WHERE (dbo.v_ecImpact.classID = 3) AND (dbo.tblEC.sortOrder < @sortOrd)
But when I try to save this query, I get an error message "Must declare the scalar variable "@sortOrd". I've tried adding
DECLARE @sortOrd int
above the SELECT statement, but then, when I try to save, the Management Studio shuts down on me.
Any and all help is greatly appreciated.
Chris