procedural approach - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

procedural approach

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-03 : 09:14:08

I have set 3 queries
1 delete from table1
where date_coulmn<=p_rundate

2 select * form table1
where date_coulmn<=p_rundate

3 select count(table1.id)
from table1
where date_coulmn<=p_rundate

Here p_rundate is the parameter which i need to enter
all the above 3 sets of queries I have to repeat for around 25 tables

And date coulmn for any table is different
Like
table1 has date_coulumn1
table2 has date_coulumn2
table3 has date_coulumn3
table4 has date_coulumn4
...............
................

SO how can be a good stored procedure for this.... which is applicable for all 25 tables

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-03 : 10:27:55

any advise

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 10:32:54

If you don't want to repeat your queries for your 25 tables with different columnnames there is only the way to use DYNAMIC SQL.

No, you're never too old to Yak'n'Roll if you're too young to die.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-03 : 11:39:00

could you please share any sample code for this

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-03 : 11:43:59

Sure:

Create Procedure GenericTableSelect
	@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM ' 
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO

Source: http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

No, you're never too old to Yak'n'Roll if you're too young to die.

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-04 : 02:33:02

thanks for sample code ..but I have to follow same for
1 delete from table1
where date_coulmn<=p_rundate

2 select count(table1.id)
from table1
where date_coulmn<=p_rundate

right?

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-06-04 : 09:29:52

any help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources