Run Dynamically generated SQLs in SQL Server 2000

Question

Post reply

Run Dynamically generated SQLs in SQL Server 2000

thamil_md

Newbie

Points: 3
More actions
March 22, 2012 at 4:46 am

#98626

Hi,
I have a column named 'SQLs' in Table A and the column 'SQLs' has SQL statements as data, like this:
select '21/03/2012' as date_modif_create, 'tblAddressesGP' as table_name, count(*) as total_rows_modified_updated from tblAddressesGP
select '21/03/2012' as date_modif_create, 'tblAddressesPatient' as table_name, count(*) as total_rows_modified_updated from tblAddressesPatient
select '21/03/2012' as date_modif_create, 'tblAddressRolesGP' as table_name, count(*) as total_rows_modified_updated from tblAddressRolesGP
I need to run these all statements in one go by like running a procedure and pass SQL statement/column 'SQLs' data as a parameter. Is it doable in SQL Server 2000?
Any ideas? Your advice would be appreciated.
Thanks in Advance.
Thamil K

Viewing 4 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

This should help. http://msdn.microsoft.com/en-us/library/aa933299%28v=sql.80%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323546 More actions · Answer 2

thamil_md (3/22/2012)
Hi,
I have a column named 'SQLs' in Table A and the column 'SQLs' has SQL statements as data, like this:
select '21/03/2012' as date_modif_create, 'tblAddressesGP' as table_name, count(*) as total_rows_modified_updated from tblAddressesGP
select '21/03/2012' as date_modif_create, 'tblAddressesPatient' as table_name, count(*) as total_rows_modified_updated from tblAddressesPatient
select '21/03/2012' as date_modif_create, 'tblAddressRolesGP' as table_name, count(*) as total_rows_modified_updated from tblAddressRolesGP
I need to run these all statements in one go by like running a procedure and pass SQL statement/column 'SQLs' data as a parameter. Is it doable in SQL Server 2000?
Any ideas? Your advice would be appreciated.
Thanks in Advance.
Thamil K

there are much easier ways to get the table counts than storing the commands in a table....

anyway,

i don't see anything that would be parameterized in your example data.

you could use a cursor, and a REPLACE command to change , say, the static date '21/03/2012' to some other value....

but those are all select statements...what are you going to do with the results? unless your application is prepared for Multiple Result Sets (MARS), or you are only doing this in SSMS, it don't see a value.

wouldn't it be easier to run a single select statement, instead of multiple results?

SELECT so.[name] as

, CASE WHEN si.indid between 1 and 254

THEN si.[name] ELSE NULL END

AS [Index Name]

, si.indid, rows

FROM sys.sysindexes si

INNER JOIN sysobjects so

ON si.id = so.id

WHERE si.indid < 2

AND so.type = 'U' -- Only User Tables

AND so.[name] IN('tblAddressesGP','tblAddressesPatient' ,tblAddressesGP )

ORDER BY so.[name]

anyway, if you insist on multipel results sets, here's amodel:

declare

@isql varchar(2000),

@sqlcmd varchar(64)

declare c1 cursor for select [SQLs] from [Table A]

open c1

fetch next from c1 into @sqlcmd

While @@fetch_status <> -1

begin

select @isql = 'ALTER DATABASE @sqlcmd SET AUTO_CLOSE OFF'

select @isql = replace(@sqlcmd, '21/03/2012' ,getdate())

print @isql

exec(@isql)

fetch next from c1 into @sqlcmd

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thamil_md Newbie Points: 3 More actions · Answer 3

Hi,

Thanks for your excellent suggestion.

What I am trying to do is that I need to count modified and/or created rows from each user tables from DB and I need a Date,count and Tablename in the result on daily basis.

I will try and apply your second suggestion to see how it goes.

Thanks

Thamil