Function with a tablename as variable

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

Function with a tablename as variable

Author

Topic

hubschrauber
Starting Member

16 Posts

Posted - 2006-02-07 : 04:54:26

Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal = (SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE @tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY @tabel1.Maand, @tabel1.Jaar)
end

if @categorie = 'open_te_laat'
begin
SET @aantal = (SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE @tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY @tabel1.Maand, @tabel1.Jaar)
end

end

return @aantal

END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 05:39:10

You have to use Store Procedure instead.
- You can't use table name as a variable unless you are using dynamic sql like exec() or sp_executesql. However you can't use exec() or sp_executesql in a function.

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 08:28:24

You need to use Dynamic SQL which is not possible inside a Function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources