help in troubleshooting a stored proc

Question

Post reply

help in troubleshooting a stored proc

cspangler

Say Hey Kid

Points: 704
More actions
August 10, 2005 at 12:58 pm

#165215

I have a stored procedure that is called from a scheduled job. It looks for a specific table based on the week number and if exists, drop, if not report that it does not exists in the job log. The problem is that I am unable to get it to function correctly.
Would anyone have any insight to my dilema? Any assistance would be greatly appreciated.
Below is the portion of the stored proc that's causing me pain.
declare @week int,
@tblnmchar(50),
@suffix char(5),
@drop_oldtbl char(25),
@srctbl char(50),
@SQLString NVARCHAR(4000)
select @tblnm='Current_Cost_Model'
select @week=DATENAME(ww,getdate())
if isnull(@week,1)=1
select @suffix='_'+DATENAME(ww,getdate())
else
select @suffix='_'+ltrim(str(@week))
select @tblnm=rtrim(@tblnm)+ltrim(@suffix)
if exists (select name from cost_model.dbo.sysobjects where name = @tblnm and xtype = 'U')
begin
select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
select @sqlstring = N'drop table Cost_Model.dbo.' + @drop_oldtbl
select 'Table being deleted: ' + @sqlstring
exec sp_executesql @sqlstring
end
else
begin
select @tblnm = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))
select @sqlstring = 'Current table Current_Cost_model' +@tblnm + 'does not exist'
exec sp_executesql @sqlstring
end

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mathew J Kulangara SSCommitted Points: 1610 More actions · Answer 1

You are checking for the existance of @tblnm..

and dropping something else. I'm not sure thats what you're trying to do but...

probably your error is here:

select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))

try

select @drop_oldtbl = stuff(@tblnm , 20, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))

HTH

Mathew J Kulangara
sqladventures.blogspot.com

cspangler Say Hey Kid Points: 704 More actions · Answer 2

sorry about that, the name of the table is 'curr_cost_model' not current_cost_model'.

so the this section of the code: select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6))), is actually correct on starting on the 17th character.

i really appreciate your input. actually, maybe i wasn't clear earlier, the problem is once the table is dropped, and the stored procedure is called again, this step looks for the table and since it no longer esists, i get a failue.

i tried using a true\false scenario, so if the table does not exist, report so and end successfully...but yet to no fruition.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

cspangler...go look at this other post....the problem seems to be similar except i don't have the time to follow through and verify...

other post

**ASCII stupid question, get a stupid ANSI !!!**

nigel. SSChampion Points: 11636 More actions · Answer 4

I think the problem is in the final two statements

select @sqlstring = 'Current table ...'

exec sp_executesql @sqlstring

you're trying to run invalid SQL ie The statement 'Current table ...'.

I'm pretty sure this is not what you intended. I think you meant to just return the message eg:

select 'Current table ... ' + @tblnum + 'does not exist'

HTH

Nigel
How to post questions

Nancy Simon SSC Enthusiast Points: 196 More actions · Answer 5

You can also try a print statement:

print @sqlstring

The advantage of this is that you don't get the dashed line above the output.

-n

cspangler Say Hey Kid Points: 704 More actions · Answer 6

thanks everyone for your insight. what i did was to take this portion out, tweak it a little, and place it into a separate job that runs once a week to drop the specific table.

i ran it through a couple of tests and it works fine.

thanks again.

declare @week int,

@tblnm char(50),

@suffix char(5),

@drop_oldtbl char(25),

@SQLString NVARCHAR(4000)

select @tblnm='Current_Cost_Model'

select @week=DATENAME(ww,getdate())

if isnull(@week,1)=1

select @suffix='_'+DATENAME(ww,getdate())

else

select @suffix='_'+ltrim(str(@week))

select @tblnm=rtrim(@tblnm)+ltrim(@suffix)

if exists (select name from cost_model.dbo.sysobjects where name = @tblnm and xtype = 'U')

begin

select @drop_oldtbl = stuff(@tblnm , 17, 2, ltrim(str(DATENAME(ww,getdate()) - 6)))

select @sqlstring = N'drop table Cost_Model.dbo.' + @drop_oldtbl

select 'Table being deleted: ' + @sqlstring

exec sp_executesql @sqlstring

end