I'm trying to create a stored procedure that will look at a table within a given database and return rows where the values from the source table are 'in' the given returned set.
Here is the Select portion of the query:
Select Budget_Id, Company_Code, Department_Code, Natural_Account, '', '', CONVERT(varchar(4), bdt.YEAR1) + '-01-01 00:00:00.000', 1
, Period1 as Budget_Amount, Mst.Accatnum, Act.Actindx, bdt.Year1
From _DSI_ForecasterLE_Budget bdt
WHERE Company_Code in (SELECT ltrim(rtrim(CrmServiceUrl)) FROM S01500 WHERE INTERID = DB_NAME())
The query returns no records. However if I change the Where clause to this:
WHERE Company_Code in (400, 401, 402, 403, 404, 405)
Then records are returned. The values in the parenthesis are the same values if I run the Select ltrim(rtrim(CrmServiceURL)) statement
400, 401, 402, 403, 404, 405
Any ideas on how or why the Where Company_Code in (Select ...) doesn't work?
The Select statement works fine and returns records when the column CrmServiceURL only contains 1 numeric value.