SQLTeam.com | Weblogs | Forums

Stored Procedure with Where ColumnA in (Select Statement)

#1

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.

#2

I don't think that can be right, because looking at your code it should work "just fine".

Perhaps

INTERID = DB_NAME()

is false when the SProc actually runs (maybe the database context changes or somesuch?)

If you can run the SProc manually, rather than through an APP etc., then I would put

SELECT DB_NAME() AS [DEBUG_DBName]
SELECT ']' + ltrim(rtrim(CrmServiceUrl)) + '[' AS [DEBUG_CrmServiceUrl]
FROM S01500 
WHERE INTERID = DB_NAME()

on a line before the main SELECT statement and see what values you get, at runtime.

#3

I would expect SQL to make an implicit conversion, but that is a list of INTEGERS whereas your:

 in (SELECT ltrim(rtrim(CrmServiceUrl)) FROM S01500 WHERE INTERID = DB_NAME())

is a list of String Values. You could explicitly convert them to INT (assuming that Company_Code is an INT column?)

#4

Actually the Company_Code field amazingly enough is a varchar(24), it only contains 3 digit values that are numeric in this particular case but it can contain Alpha obviously and I'm only trying to retrieve the match on the numeric values. in my other varchar column CrmServiceURL.

Again works perfectly fine if the CrmServiceURL column only contains 1 value 401 for example as soon as it contains 401, 402 then the Select statement in my procedure returns nothing.

With respect to the db_name() component the stored procedure is in the source db so in theory there is no possible way it can return something other than the db that it resides in unless I'm missing something.

Any other ideas since they are both varchar columns?

#5

So the column "INTERID" contains the database name?

Otherwise, I don't see any issue that should prevent a valid comparison. Is it possible that the values in CrmServiceUrl (odd name for a "company code" value?!) contain leading zeros or some other chars that would throw off a character comparison?

#6

Put the DEBUG statements in the SProc and see what they say. That might lead you to the cause, and thus the answer.

Ah ... well it won't. In effect you are doing

WHERE Company_Code in (SELECT ltrim(rtrim(CrmServiceUrl)) FROM S01500 WHERE INTERID = DB_NAME())

which resolves to

WHERE Company_Code in ('401, 402')

which is not going to match.