Sproc Loads No Data, But No Error Either?

[SQLJoe]

My sproc (below) is supposed to load the data from a table that is passed to it into a table called tblPrintLog. It does not err out, but it does not load any data either. It just shows "(0 row(s) affected)". In the test table I'm using, there are 108 rows. What am I doing wrong? As an aside, there are a lot of things I'd change about the tables I have to load (column naming styles, data types, etc.), but unfortunately I have to load them as is.

CREATE procedure [dbo].[spLoadPrintLog](@TableName varchar(200)) as

declare @SQL varchar(5000)

set @SQL =
'insert into
dbo.tblPrintLog
select
'''' as PrintLogID,
'''' as DocumentID,
'''' as CostCenter,
[Document Name],
[Print Server Name],
[Virtual Printer],
cast(substring([Job Submission Date],2,len([Job Submission Date])-2) as DATE) as [Job Submission Date],
cast(substring([Start RIP Time],2,len([Start RIP Time])-2) as DATE) as [Start RIP Time],
cast(substring([Stop RIP Time],2,len([Stop RIP Time])-2) as DATE) as [Stop RIP Time],
[Total Black Only Pages Printed],
[Total Color Pages Printed],
'''' as ReportingMonth,
'''' as ReportingYear,
getdate() as DateTimeImported,
'''+@TableName+''' as TableName
from
'+@TableName+'
where
([Total Black Only Pages Printed] is not null
and [Total Color Pages Printed] is not null)
and
([Total Black Only Pages Printed] <>''0''
and [Total Color Pages Printed] <>''0'')
and
[Document Name] is not null
and
[Job ID] not like ''%Job ID%''
and
[Job Submission Date] <>''Unknown'''

exec (@SQL)

I call the above procedure like this:

exec spLoadPrintLog 'dbo.t110630_080038_dp4127_output'

 

[Qik3Coder]

    ([Total Black Only Pages Printed] <>''0''    and [Total Color Pages Printed] <>''0'')

should probably be:

    ([Total Black Only Pages Printed] <>''0''    or [Total Color Pages Printed] <>''0'')

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[gmmastros]

Change exec (@SQL) to:

Print @SQL

Run again. This time, you will see the SQL that is executed. If nothing appears wrong with it, copy/paste it to a new query and run it from there.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLJoe]

Got it! Very helpful. Thanks both!