Creating a Stored Procedure

Is it possible to create a stored procedure that execute a delete command from a table whose name is specified as a parameter of the stored procedure?

Thank you

Yes. You would need to use dynamic SQL for that.

CREATE PROC....@.tblvarchar(25)AS...Declare @.sqlvarchar(100)--Build your SQLSET @.sql ='DELETE FROM ' + @.tbl +' WHERE <condition>'--Execute your SQLEXEC(@.sql)-- You can also use sp_ExecuteSQL. Check out BOL for more info.

|||

A problem with dynamic SQL though, is that by default the caller has to have delete permissions on the table. Will all callers have that permission? That could be a significant vulnerability in your application and database server.

What version of SQL Server are you using? If it's 2005, you could use the EXECUTE AS option in the stored procedure to run with another principal's permissions. That could go a long way towards making this more secure.

Don

|||

Thank you for the info.

A valuable one.

CreateReportHistorySnapshot

Is there a way to setup history snapshots to be deleted after a specified
timeframe (30 days) forexample? Does RS 2005 have an automated cleanup
process to delete these snapshots?Yes. From the Report Manager home page, select SITE SETTINGS. Here you can
specify how many days snapshot are kept before deleting.
"shassbel" wrote:
> Is there a way to setup history snapshots to be deleted after a specified
> timeframe (30 days) forexample? Does RS 2005 have an automated cleanup
> process to delete these snapshots?|||The only setting for report history that I see is to limit the number of
copies for report history. The number of days on that screen refers to
execution logs.
"kbright" wrote:
> Yes. From the Report Manager home page, select SITE SETTINGS. Here you can
> specify how many days snapshot are kept before deleting.
> "shassbel" wrote:
> > Is there a way to setup history snapshots to be deleted after a specified
> > timeframe (30 days) forexample? Does RS 2005 have an automated cleanup
> > process to delete these snapshots?