using local variable as a table name

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 2000 Forums

Transact-SQL (2000)

using local variable as a table name

Author

Topic

lovitz
Starting Member

3 Posts

Posted - 2005-04-04 : 16:45:24

Hello
I would like to write function with table name parameter but don't know how. I wrote something like this but with errors
if u can help me how to use function parameter as table name THX U

------------my function---------------
USE Report
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'f_view_report' AND type = 'IF')
DROP FUNCTION f_view_report
GO

create function f_view_report (@tablename sysname,@firstdate datetime,@lastdate datetime)
returns table
AS
return(
SELECT DISTINCT TOP 100 PERCENT ValueID, [TimeStamp], RealValue, Quality, Flags
FROM @tablename -- here is problem
WHERE ([TimeStamp] BETWEEN CONVERT(DATETIME, @firstdate , 102) AND CONVERT(DATETIME, @lastdate, 102))
ORDER BY [TimeStamp])
go

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-04 : 18:55:21

This is an extraordinarily bad idea, fortunately it can't be done as a UDF because of the dynamic nature of the command. It would be a significant security breach to allow someone to run this function and pass any table name they please. Not to mention that it could easily throw a syntax or runtime error.

The fact that you would need a function like this suggests that 1) you're storing similar or identical data in multiple tables, which further suggests 2) your database is probably not designed correctly for the data you want to store.

lovitz
Starting Member

3 Posts

Posted - 2005-04-05 : 02:38:37

I made something like this and it works

********************************
USE Report
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'proc1' AND type = 'P')
DROP PROCEDURE proc1
GO

CREATE procedure proc1
@tablename char(255),
@firstdate varchar(255),
@lastdate varchar(255)
AS
BEGIN
declare @tempSql nvarchar(4000), @Value float
select @tempSql = 'SELECT max(RealValue)
FROM '+ @tablename +'
WHERE [TimeStamp] BETWEEN CONVERT(DATETIME,'+ @firstdate +', 102) AND CONVERT(DATETIME,'+ @lastdate+', 102)'
print @tempSql
exec sp_executesql @tempSql, N'@Value float output', @Value output
end

*********
--example
exec proc1 'tag03','''2005-04-04 08:09:18''','''2005-04-04 08:09:22'''

Blastrix
Posting Yak Master

208 Posts

Posted - 2005-04-05 : 03:04:11

As Rob pointed out, there's probably a chance that your database design could use some work. In the event that that is not the case, and there's a fairly legitimate reason for having multiple tables with the same columns, you might want to try creating a view, and selecting off of that.



if (object_ID('dbo.vwValueTables') IS NOT NULL)
    DROP VIEW dbo.vwValueTables
GO

CREATE VIEW dbo.vwValueTables
AS

SELECT
	TableName = 'ValueTable1',
	ValueID, 
	[TimeStamp], 
	RealValue, 
	Quality, 
	Flags
FROM ValueTable1

UNION ALL

SELECT
	TableName = 'ValueTable2',
	ValueID, 
	[TimeStamp], 
	RealValue, 
	Quality, 
	Flags
FROM ValueTable2

--etc...

GO

if (object_ID('dbo.f_view_report') IS NOT NULL)
    DROP VIEW dbo.f_view_report
GO

create function dbo.f_view_report (
	@tablename varchar(255),
	@firstdate datetime,
	@lastdate datetime
	)
returns table
AS
return(
SELECT DISTINCT TOP 100 PERCENT 
	ValueID, 
	[TimeStamp], 
	RealValue, 
	Quality, 
	Flags
FROM vwValueTable
WHERE TableName = @tablename 
	AND ([TimeStamp] BETWEEN 
		CONVERT(DATETIME, @firstdate , 102) 
		AND CONVERT(DATETIME, @lastdate, 102))
ORDER BY [TimeStamp]
)
GO

lovitz
Starting Member

3 Posts

Posted - 2005-04-05 : 03:39:21

Thanks that was what I need

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources