Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID
.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
>|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:

> Below is the function that I am trying to create. I am failing in trying t
o
> select from a random table. So I pass the table name in the function but i
t
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>