January 13, 2004 at 1:54 am
I don't know if it is possible, but it would help me a lot:
I want to query my database (sqlserver2000) to return to me a list:
(tablename, id, datum_archief)
- tablename: the name of the table
- id: the pk (every table has got a column named "id" which holds the pk)
- datum_archief: every table has got a column named "datum_archief", if this value is set, the record is archieved (deleted)
Is it possible to write a stored procedure to get me these three columns from all the records from all the tables where the datum_archief is not null?
How can I cycle through all the tables and add the records with a datum_archief value to the output recordset?
thx
January 13, 2004 at 9:52 am
Try something like this:
exec sp_msforeachtable 'select ''?'' as tablename, id, datumarchief from ?
where datumarchief is not null'
Is that what you want?
Cheers,
Chris
January 13, 2004 at 10:49 am
chris's idea is probably the simplest. Not sure this will get you what you want (multiple result sets), but there isn't an easy way to do that. Could insert the values into a temp table and then select them all out of there at the end.
create table #mytemp
( tablename, id, datumarchief)
exec sp_msforeachtable 'insert #mytemp select ''?'' as tablename, id, datumarchief from ?
where datumarchief is not null'
select * from #mytemp
drop table #mytemp
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 15, 2004 at 7:16 am
thx!! it works
the actual stored procedure to erase the 'datumarchief' date and undelete record gives me a problem.
CREATE PROCEDURE "spDoUndelete"
(
@id int(4),
@tablename nvarchar(300)
)
AS
SET NOCOUNT ON
UPDATE @tablename SET datumarchief = NULL WHERE id = @id
RETURN
gives ADO-error: must declare the variable 'tablename'
But it is a parameter?
January 15, 2004 at 10:20 am
Hello visbek,
You cannot use a variable for a tablename in a SQL statement. You can, however, dynamically execute a SQL string. The code below shows how.
By the way, notice that I changed the datatype for your @tablename variable. SQL Server provides the SYSNAME datatype as a synonym for whatever the datatype is that holds object names in the system and database catalogs. It used to be varchar(30); when SQL Server began allowing for longer object names, it was remapped to nvarchar(128), since object names can be up to 128 characters in length; in the future I expect that it will be kept up-to-date with whatever the maximum object length becomes.
I also got rid of the (4) in "int(4)."
CREATE PROCEDURE "spDoUndelete"
(
@id int,
@tablename sysname
)
AS
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(4000)
SET @SQL =
N'UPDATE ' + @tablename
+ N' SET datumarchief = NULL '
+ N' WHERE id = ' + CAST(@id AS VARCHAR)
EXEC (@SQL)
RETURN
Good luck!
Chris
January 16, 2004 at 5:41 am
thx, it works now, but is this way faster then using an ado connection to execute a local sql statement?
January 16, 2004 at 7:24 am
Visbek,
The stored procedure is more efficient because it does not have to be parsed, optimized, and compiled repeatedly at run time. However, in this case you are using dynamically executed SQL, which does have to be parsed, optimized, and compiled at run time. So there may not be much difference in execution speed in this particular case. If I'm overlooking something here, maybe somebody else will jump in. I would leave it in a stored procedure myself, since it leaves room for code re-use on the server. There are other reasons for generally favoring server-side SQL (compiled SQL, such as stored procedures and the like), but I can't say that in this case it will result in much better performance.
HTH,
Chris
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply