Dynamic query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2009-04-12 : 05:14:21

is there a way to make a query on a Dynamic table name like this :

Select * from Messag2008+@MonthName

but withought using a dynamic query which will be activated by Exec?

Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-04-12 : 08:40:29

>>but withought using a dynamic query which will be activated by Exec?

Why..?

if you are thinking about not using EXEC and dynamic query due to sql injection. the following is a safe code

CREATE PROC uspExecQuery
@myTable varchar(50)
AS
BEGIN

DECLARE @SQL nvarchar(500)
SET @SQL = 'Select col1,col2,co3 from '

IF @myTable = 'Table1'
SET @SQL = @SQL + 'Table1'
IF @myTable = 'Table2'
SET @SQL = @SQL + 'Table2'
IF @myTable = 'Table3'
SET @SQL = @SQL + 'Table3'
IF @myTable= 'Table4'
SET @SQL = @SQL + 'Table4'

--go on

EXEC @SQL --use sp_executesql instead look for its syntax

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:30:15

why does your table change monthly?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 02:43:13

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources