Delete via stored procedure

Question

Delete via stored procedure

eica

Mr or Mrs. 500

Points: 539
More actions
June 8, 2007 at 4:30 am

#172659

Hi folks!
Please can someone tell me, how to write T-SQL delete command in my SP, where I would like to delete rows from noncurrent database ? The problem is there, that I cannot write "delete databasename.owner.tablename where..." when I am using databasename as parameter of SP.
CREATE PROCEDURE DEL_AL_DAY_BY_DAY
@dbname varchar(50),
@nahaj smallint
AS
IF EXISTS (select *
from master.sys.tables
where name = 'XXX_AL')
DROP TABLE dbo.XXX_AL
CREATE TABLE dbo.XXX_AL (popis varchar (50))
DECLARE @Last datetime
DECLARE @first datetime
DECLARE @obdobie varchar(10)
SELECT @Last = MAX(started), @first = MIN(started)
FROM lcs.auditlog_hlavicka@+@dbname
SET @Last = DATEADD(MONTH, @nahaj, @Last)
SET @obdobie = CAST(YEAR(@first) as varchar(4))+'/'+CAST(MONTH(@first) as varchar(2))+'/'+ CAST(DATEPART(dd,@first) as varchar(2))
INSERT INTO master.dbo.XXX_AL (popis) VALUES ('Zaciname ' +@obdobie)
WHILE @first < @Last
BEGIN
DELETE master.dbo.XXX_AL
SET @obdobie = CAST(YEAR(@first) as varchar(4))+'/'+CAST(MONTH(@first) as varchar(2))+'/'+ CAST(DATEPART(dd,@first) as varchar(2))
INSERT INTO master.dbo.XXX_AL (popis) VALUES ('Mazem AL ' +@obdobie)
???? DELETE from @dbname.history_of_use ?????
...
..
.
How can I do that ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

You'll need to use dynamic SQL to create and execute the delete, but I've gotta ask a question...

Your DBA let's you write to the Master database??? Or, if you're the DBA, you write to the Master database??? Nay, Nay... there's too many reasons to post why you shouldn't do this... create a separate database, call it DBAWork or something, but stop writing stuff to the Master database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eica Mr or Mrs. 500 Points: 539 More actions · Answer 2

I am using master db, cause I have cca 30 different databases, where I would like to set up sql job for deleting huge table lcs.auditlog_hlavicka after database restore. These databases are from our customers. I don't need this traffic table, which stores traffic operations from database application. I cannot use drop table. The reason is more complicated. That's why I have to delete rows after small steps (days) not in one step, because the transaction log grows to heaven. So if I create this procedure under other dbs, I cannot use this sp for all databases, and create this sp for each db is useless.

One more thing I am DBA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

OK, but you need to use dynamic SQL anyway... why temp fate with Master? Create the DBA database and work from there...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Sorry... just figured out what you're trying to do...

If you call the stored proc in Master from a given other database, then I think you just do the delete using 1 or 2 part naming convention because the current database will not be the Master db... it's how the system procs work... no need for dynamic SQL or 3 part naming.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eica Mr or Mrs. 500 Points: 539 More actions · Answer 5

Hmm it's strange, please correct me, if you know, where is the mistake ?

declare @first datetime

declare @Last datetime

declare @dbname sysname

set @dbname = 'komes'

exec ('SELECT @Last = MAX(started), @first = MIN(started)

FROM '+@dbname+'.lcs.auditlog_hlavicka')

query returns:

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable "@last".

I tryed this too:

declare @first datetime

declare @Last datetime

declare @dbname varchar(50)

declare @sql varchar (100)

set @dbname = 'komes'

set @sql = 'SELECT MAX(started) FROM '+@dbname+'.lcs.auditlog_hlavicka'

exec @Last = (@sql)

still with error with syntax

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Where does this proc live? In Master?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

zjhan Old Hand Points: 364 More actions · Answer 7

The dynamic SQL couldn't see the variables declared in your current session. Think of it running in a different session.

Try using sp_executesql. Check BOL. It has an example to use OUTPUT parameter to get the result set back from the dynamic SQL.

Hope this helps.

Jin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Jin is correct... if you want multiple variable returns, one of the preferred mthods is to us sp_ExecuteSQL... and Books Online is the place to learn how to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)