Help with Stored Procedures - 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 2000 Forums

Transact-SQL (2000)

Help with Stored Procedures

Author

Topic

Jirib
Starting Member

5 Posts

Posted - 2005-06-02 : 15:29:16

use SAV
go

CREATE PROCEDURE sp_procedimiento @Sucursal int = 171, @deposito int = 171, @ruta int = 000001
AS
select CodSuc, CodRut,CodDep
from tablsucu,tablruta,tabldepo
where codsuc = @Sucursal
and coddep = @deposito
and codrut = @ruta
and tablsucu.codsuc = tabldepo.codsuc
and tabldepo.coddep = tablruta.coddep

GO

this is my main code for a stored procedure and when check the code it comes an error saying

Error Code 209
the name of the field CodDep is ambiguous

NoNulls
Starting Member

14 Posts

Posted - 2005-06-02 : 15:32:42

select CodSuc, CodRut,CodDep

you need to specifiy which table you want these columns from...
i.e.
select tablsucu.codsuc, tablruta.cotrut, tabldepo.coddep

it is only necessary for the columns that exist in more than one of those tables

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 15:35:33

Apparently, CodDep column is in more than one of your tables. So you'll need to let it know which one to use:

CREATE PROCEDURE sp_procedimiento @Sucursal int = 171, @deposito int = 171, @ruta int = 000001
AS
select t1.CodSuc, t3.CodRut, t2.CodDep
from tablsucu t1
inner join tabldepo t2
on t1.codsuc = t2.codsuc
inner jion tablruta t3
on t2.coddep = t3.coddep
where t1.codsuc = @Sucursal
and t2.coddep = @deposito
and t3.codrut = @ruta

GO

Use the JOIN syntax for your joins and not the T-SQL way. Also use aliases for your tables so that you don't have to type the table names over and over again.

Tara

Jirib
Starting Member

5 Posts

Posted - 2005-06-02 : 15:38:59

Thx guys is working now.... ill be asking more if needed :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources