problem while running the 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 2008 Forums

Transact-SQL (2008)

problem while running the stored procedure

Author

Topic

anthu_soft
Starting Member

8 Posts

Posted - 2009-12-11 : 03:04:26

I have a problem while running the stored procedure.

I have 2 databases and tempdb

database 1 has Latin1_General_CI_AS collate
database 2 has Sql_Latin1_General_CP1_CI_AS collate
my tempdb has Latin1_General_CS_AS

I have created a stored procedure in database2.

Inside the stored procedure I have written the following code

create table #tble ([Stnum] nvarchar(5)COLLATE Latin1_General_CI_AS
)
insert into #tble
Select distinct [St_Number]
[database 1].dbo.stgtbl

update #tble
Set [[Stnum]]= CASE
WHEN LEN([Stnum]) = 1 THEN '0000' + [Stnum]
WHEN LEN([Stnum]) = 2 THEN '000' + [Stnum]
WHEN LEN([Stnum]) = 3 THEN '00' + [Stnum]
WHEN LEN([Stnum]) = 4 THEN '0' + [Stnum]
END
WHERE LEN([Stnum]) < 5

while running the stored procedure i got an error

Invalid object name '#tble'

urgent reply needed

Thanks in advance

Anthuvan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 04:11:31

insert into #tble
Select distinct [St_Number]
[database 1].dbo.stgtbl

should be

insert into #tble
Select distinct [St_Number] from
[database 1].dbo.stgtbl

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 13:04:30

also your update can be simplified as

update #tble
Set [[Stnum]]= RIGHT('0000' + [Stnum],5)
WHERE LEN([Stnum]) < 5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources