Help with Stored Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with Stored Procedure

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-04-30 : 14:50:27

I get the message "Ambiguous column name 'DateVar'.
" when executing the following Stored Procedure. This SP works fine when running on SQL Server 2000, only fails when run on SQL Server 2005. Did something change between 2000 and 2005 that would cause this error?

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[stp_rPickLists] @DoSection int = null
as

Declare @IntVar int,
@CharVar3 char(3),
@CharVar5 char(5),
@CharVar10 char(10),
@CharVar30 char(30),
@CharVar char(50),
@Symbol char(3),
@TranType char(10),
@Date1 datetime,
@Date2 datetime,
@maxyr int,
@Maxmo int,
@MaxDate datetime,
@Chg money

Set NoCount On

If @DoSection = 1
Begin
Select Chr3Var, Chr30Var
from PickLists
where PickID = '10'
and SubID = 'DSSYM'
Order by Chr3Var
End

If @DoSection = 2
Begin
select @Date1 = min(DateVar),
@Date2 = max(DateVar)
from PickLists
where PickID = '10'
and SubID = 'DSDAT'

Select DateVar, 'DateFull' = convert(char,DateVar,101)
from PickLists
where PickID = '10'
and SubID = 'DSDAT'
and DateVar between @Date1 and @Date2
Order by convert(datetime,DateVar) desc

End

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-30 : 14:58:50

I've only seen this error when there are joins in the queries, but I don't see any in yours.

Could you post the CREATE TABLE statement for PickLists?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 15:05:37

Have you posted the full code here?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-30 : 15:21:29

I'm sure that PickLists is a VIEW. That is where the ambiguity will be.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources