Problem using a parameter in a subquery

The following code works (assuming that there is an employee with the alias
'some alias', of course)
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS SELECT
ExpenseLocations.LocationId, ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees WHERE EmployeeAlias = 'some alias'))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
However, the following parameterized code does not work:
ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
int output) AS SELECT ExpenseLocations.LocationId,
ExpenseLocations.LocationName
FROM ExpenseLocations INNER JOIN
myLocations ON ExpenseLocations.LocationId =
myLocations.LocationId
WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM Employees
WHERE EmployeeAlias = @.EmployeeAlias))
ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
I am obviously doing something wrong but am not sophisticated enough
(apparently) to figure out why. Thanks in advance for any suggestions.
DuncanYou do not define a length of the char param
@.EmployeeAlias char
Should be something like this
@.EmployeeAlias char(10),
my suggestion:
exec sp_help Employees
make the variable the same length as the EmployeeAlias column as it is
defined within the table.
Keith
"duncans" <duncans@.discussions.microsoft.com> wrote in message
news:59D22412-47F6-4ED6-A1D9-B31C53994CC8@.microsoft.com...
> The following code works (assuming that there is an employee with the
alias
> 'some alias', of course)
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.RowCount int output) AS
SELECT
> ExpenseLocations.LocationId, ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
> Employees WHERE EmployeeAlias = 'some alias'))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> However, the following parameterized code does not work:
> ALTER PROCEDURE dbo.GetMyLocationsSubquery (@.EmployeeAlias char, @.RowCount
> int output) AS SELECT ExpenseLocations.LocationId,
> ExpenseLocations.LocationName
> FROM ExpenseLocations INNER JOIN
> myLocations ON ExpenseLocations.LocationId =
> myLocations.LocationId
> WHERE (myLocations.EmployeeId = (SELECT Employees.EmployeeId FROM
Employees
> WHERE EmployeeAlias = @.EmployeeAlias))
> ORDER BY ExpenseLocations.LocationName;SELECT @.RowCount=@.@.ROWCOUNT
> I am obviously doing something wrong but am not sophisticated enough
> (apparently) to figure out why. Thanks in advance for any suggestions.
> Duncan
>