Passing TableName as parameter

[luvmy2Js]

I am trying to pass a tablename as a parameter. Below is the code I have and it is giving me an error

CREATE PROCEDURE [dbo].[pcs_DeleteOldPAFiles]

@upltable nvarchar(50)
AS
BEGIN

Declare @startdate datetime
SET @startdate = CONVERT(nvarchar, getdate(), 101)

Delete * from @upltable
Where [Date of Last Change] < @startdate-6

END

Error: Incorrect syntax near '*'.

What am I doing wrong..

 

[markros]

Before you go this route, is the dynamic table name really required?

Take a look at the same question in another forum:

http://social.msdn.microsoft.com/Fo...l/thread/4ef8c917-abe8-44cf-a754-96cad65b694e

PluralSight Learning Library

 

[luvmy2Js]

Because this stored procedure will have to be used in several other procedures, I do need to be able to pass the tablename if at all possible..

Thanks!!

 

[simian336]

You did not say if you found your solution.

If not you do not, you do not need the "*" in the query. You will probably need to convert your query to a dynamic query like the example url.

Simi

 

[luvmy2Js]

Simian336 I didn't find the solution but will take a look at the url as indicated.

Thanks,

 

[markros]

This procedure needs to be re-written as such

CREATE PROCEDURE [dbo].[pcs_DeleteOldPAFiles]

    @upltable nvarchar(50)
AS
BEGIN

if not exists (select 1 from 
INFORMATION_SCHEMA.Tables where
table_name = @uplTable)
   begin
   raiserror('Invalid table name %s passed!',16,1,@ulpTable)
   return -1
  end
 
Declare @startdate datetime
SET @startdate = dateadd(day,-6 + datediff(day, '19000101', getdate()),'19000101')

declare @SQL nvarchar(max)

set @SQL = 'delete from ' + quotename(@upltable) + 

' Where [Date of Last Change] < @startdate'

execute sp_executeSQL @SQL,N'@StartDate datetime',@StartDate
end

END

PluralSight Learning Library

 

[luvmy2Js]

Thanks Markros. This works perfect (thanks again)..

Now how do I go about adding an "inner join" to this query or and an "or" operator to the statement...

these 2 pieces needs to be added:

inner join pa_processedreports r on r.sapid = p.sapid
and [Change Date])) = [Date of Last Change]

or

' Where [Change Date] < @startdate'

Again thanks a milion because I have struggled with this a couple of days and have tried to add the pieces to your code above but can't seem to get it..

 

[markros]

set @SQL = 'delete from ' + quotename(@upltable) + 

' Where [Date of Last Change] < @startdate'

will become

set @SQL = 'delete T from ' + quotename(@upltable) + 

' T Where [Date of Last Change] < @startdate 
and exists (select 1 from pa_ProcessedReports r
where r.SapID = t.SapID 
and r.[Change Date] = T.[Date of Last Change]'

I think EXISTS subquery performs better than inner join in case of UPDATE or DELETE, so in most cases it's better to use this syntax (if possible).

You may check this blog post

http://bradsruminations.blogspot.com/2010/02/dear-from-clause.html

and there is another blog on this same topic by Hugo Kornelis you may try googling on.

PluralSight Learning Library

 

[markros]

Missed closing ) in the query above.

should be where exists (select ...)

PluralSight Learning Library

 

[luvmy2Js]

Okay great!! Now how do I incorporate the "or" part of it because some of the tables field name is [Change Date] as opposed to {Date of Last Change] so I need to add this:

Where [Date of Last Change] < @startdate
or
[Change Date] < @startdate

Again thanks for everything; you are truly a life-saver on this )...

 

[markros]

If you have different structure in these tables, then you need to do it differently, e.g.

if exists(select 1 from Information_Schema.Columns
where Table_Name = @TableName and Column_Name = 'Date of Last Change])
  begin
    set @SQL = 'delete from ' +
     quotename(@TableName) + where [Date of Last Change] 
    < @StartDate'
  end
else
  if exists(select 1 from Information_Schema.Columns
where Table_Name = @TableName and Column_Name = 'Change Date')
  begin
    set @SQL = ...
  end
else -- none of these - bail out
 begin
   raiserror('The table name %s passed doesn''t have
Date of Last Change or Change Date columns',16, 1,@tablename)
  return  -3

-- execute dynamic SQL now
   execute sp_ExecuteSQL @SQL,N'@startDate datetime',@StartDate

PluralSight Learning Library