Prevent Ambiguous Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Prevent Ambiguous Stored Procedure

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2010-02-11 : 12:16:50

The following is the definition of my stored procedure:

PROCEDURE [dbo].[sp_GetShiftManagement] AS BEGIN
SELECT m.ID, m.[Group] AS 'Group', m.[Shift], m.Dept, e.Firstname+' '+e.Lastname AS 'Manager',
 m.MgrNum, m.Schedule, m.SubGroup
FROM ShiftManage m INNER JOIN EmployeeInfo e ON (m.MgrNum=e.Num)
 ORDER BY m.ID
END

I fill this data into my ShiftManager data table in Visual Studio and query it as needed:

DataRow[] QueryShiftManager(string sqlText) {
  return ShiftManager.Select(sqlText);
}

I have been getting 0 records for data that I knew to be there. I queried the table directly by punching in everything into Query Analyzer:

SELECT m.ID, m.[Group] AS 'Group', m.[Shift], m.Dept, e.Firstname+' '+e.Lastname AS 'Manager',
 m.MgrNum, m.Schedule, m.SubGroup
FROM ShiftManage m INNER JOIN EmployeeInfo e ON (m.MgrNum=e.Num)
where (Shift='1') AND ([Group]='OFFICE') AND (Dept='ENGINEERING')
 ORDER BY m.ID

That was where I found 'Ambiguous column name' errors, because the INNER JOIN EmployeeInfo table also contains the fields I am trying to filter - namely 'SHIFT', 'GROUP', and 'DEPT'.

I can only guess that somehow my DataTable still knows about the non-selected fields from the INNER JOIN table.

How do I tell a DataTable's Select command that I only want to select from the rows and columns in the actual table??? ...or, am I missing something else?

Renaming the Stored Procedure's output columns is possible, but would add several weeks of debugging out new column name changes.

Thanks in advance,
~Joe

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 12:22:47

As long as you use aliases correctly to determine from which table you need the reqd columns ambiguos column error wont occur. its only when you use just column name or * then it throws the error provided you've columns with same name existing on more than one table.

so your statement should be


SELECT m.ID, m.[Group] AS 'Group', m.[Shift], m.Dept, e.Firstname+' '+e.Lastname AS 'Manager',
 m.MgrNum, m.Schedule, m.SubGroup
FROM ShiftManage m INNER JOIN EmployeeInfo e ON (m.MgrNum=e.Num)
where (m.Shift='1') AND (m.[Group]='OFFICE') AND (m.Dept='ENGINEERING')
 ORDER BY m.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jp2code
Posting Yak Master

175 Posts

Posted - 2010-02-11 : 12:41:19

Hi Visakh16!

When I run the query supplied by you, I get 4 rows returned.

Whenever I use the equivalent stored procedure below

DataRow[] QueryShiftManager(string sqlText) {
  return ShiftManager.Select(sqlText);
}

...I get 0 rows returned.

I (incorrectly, of course) assumed that this discrepancy was due to the fact that my table somehow knew about the INNER JOIN columns.

What else could be causing my table query to be returning the incorrect number of rows?

Avoid Sears Home Improvement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources