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 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
>
>
>|||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:
> 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 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
> >
> >
> >
> >
> >|||..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 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
>
>
>

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

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
END
You 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 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
>
>
>
|||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 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
>
>
>

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIA
Did you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/2909...22120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA
|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:

> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/2909...22120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
more information.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> > Hi,
> > My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> > How do i fix it or do i need to reinstall?How?
> >
> > This is the error msg i get.What file is it looking for?By replacing the
> > file, can i get it back online?
> >
> > "An error occurred during the online operation for instance <SQL Server
> > Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> > specified."
> >
> > TIA
>
>

FullTextSearch

Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified
."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290...122120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:

> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290...122120121120120 f
or
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>