August 23, 2006 at 2:04 pm
I am trying to create a UDF that looks like this:
CREATE FUNCTION dbo.WAPSnapshotFunc(@TheDate datetime)
RETURNS @TempWAPSnapshot TABLE
(
ProgressiveID int,
CurrentBalancebigint,
NextBalance1 bigint,
NextBalance2bigint,
Activebit,
EPSNamevarchar(21),
BankNamevarchar(21),
ZoneNamevarchar(21),
LegalConfigNumberint,
ConfigurationNamevarchar(50)
)
As
Begin
/*Temp Table to store GSDB names....GSDB's are actually linked servers whose names are stored locally in tblCasino*/
DECLARE @GSDB varchar(30)
DECLARE @Casino TABLE
(
primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!
GSDB varchar(30)
)
INSERT INTO @Casino
SELECT GSDB FROM tblCasino
/*I won't know what GSDB's are until runtime as they are built by the application depending on what servers are talking to it at a certain time*/
DECLARE @item_category_counter INT
DECLARE @loop_counter INT
/* I then create a loop counter to stay away from a cursor for performance issues*/
SET @loop_counter = ISNULL((SELECT COUNT(*) FROM @Casino),0) -- Set the @loop_counter to the total number of rows in the memory table
SET @item_category_counter = 1
/* And in my loop I want to query each linked server (GSDB) as you can see below*/
WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN
DECLARE @LegalConfig varchar(50)
DECLARE @EPSInfovarchar(50)
SELECT @GSDB = GSDB
FROM @Casino
WHERE primary_key = @item_category_counter
/* Set @LegalConfig and @EPSInfo to the full path of the table at the linked server */
SET @LegalConfig = @GSDB + '.Bingo.dbo.LegalConfiguration'
SET @EPSInfo = @GSDB + '.Bingo.dbo.EPSInfo'
INSERT INTO @TempWAPSnapshot
SELECT PV.ID, SUM(PV.CurrentBalance) AS CurrentBalance, SUM(PV.NextBalance1) AS NextBalance1,
SUM(PV.NextBalance2) AS NextBalance2, PC.Active, EI.EPSName, EI.BankName, EI.ZoneName,
LC.SequenceNumber, LC.ConfigurationName
FROM tblProgressiveValue PV INNER JOIN tblProgressiveConfig PC ON PV.ID = PC.ID
INNER JOIN @LegalConfig LC ON LC.Progressive1 = PV.ID
INNER JOIN @EPSInfo EI ON LC.SequenceNumber = EI.LegalConfigNumber
WHERE (RemovalDate > @TheDate)
GROUP BY PV.ID, PC.Active, EI.EPSName, EI.BankName, EI.ZoneName,
LC.SequenceNumber, LC.ConfigurationName
SET @item_category_counter = @item_category_counter + 1
END
RETURN
END
The problem is, after I set the @LegalConfig and @EPSInfo they aren't being recognized as servers in the INNER JOIN's....I get this error:
Server: Msg 137, Level 15, State 2, Procedure WAPSnapshotFunc, Line 55
Must declare the variable '@LegalConfig'.
So my question is how do I declare a variable which represents a full path to a linked server database table and query that in a SELECT statement as above?
I can query the linked servers perfectly fine if I just use the linked server name but I'm doing it this way because the linked servers get created on the fly at runtime by our application and get inserted into tblCasino, so I won't know the names of them until I run this function or query tblCasino.
Thanks!
Steven
August 23, 2006 at 3:23 pm
You might need to use dynamic SQL and use EXEC() to execute the sql.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 23, 2006 at 3:46 pm
Can you give me an example with my function....I'm not sure how to use/create dynamic SQL.
August 23, 2006 at 5:10 pm
If you use dynamic sql you would not be able to use table variables. You need to use either temp tables or physical tables. there are tons of articles about how to use dynamic sql efficiently.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 23, 2006 at 7:16 pm
Move all DECLARE's to the beinning of the function where they belong.... you cannot include DECLARE's in a WHILE LOOP.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 24, 2006 at 12:47 am
You cannot use dynamic T-SQL in a UDF, so convert to a stored procedure:
CREATE PROCEDURE dbo.WAPSnapshotProc(@TheDate datetime)
AS
DECLARE @GSDB varchar(30)
DECLARE @LegalConfig varchar(50)
DECLARE @EPSInfo varchar(50)
DECLARE @SQL carchar(8000)
DECLARE @item_category_counter INT
DECLARE @loop_counter INT
DECLARE @TempWAPSnapshot TABLE
(
ProgressiveID int,
CurrentBalance bigint,
NextBalance1 bigint,
NextBalance2 bigint,
Active bit,
EPSName varchar(21),
BankName varchar(21),
ZoneName varchar(21),
LegalConfigNumber int,
ConfigurationName varchar(50)
)
/*Temp Table to store GSDB names....GSDB's are actually linked servers whose names are stored locally in tblCasino*/
DECLARE @Casino TABLE
(
primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!
GSDB varchar(30)
)
INSERT INTO @Casino
SELECT GSDB FROM tblCasino
/*I won't know what GSDB's are until runtime as they are built by the application depending on what servers are talking to it at a certain time*/
/* I then create a loop counter to stay away from a cursor for performance issues*/
SET @loop_counter = ISNULL((SELECT COUNT(*) FROM @Casino),0) -- Set the @loop_counter to the total number of rows in the memory table
SET @item_category_counter = 1
/* And in my loop I want to query each linked server (GSDB) as you can see below*/
WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN
SELECT @GSDB = GSDB
FROM @Casino
WHERE primary_key = @item_category_counter
/* Set @LegalConfig and @EPSInfo to the full path of the table at the linked server */
SET @LegalConfig = @GSDB + '.Bingo.dbo.LegalConfiguration'
SET @EPSInfo = @GSDB + '.Bingo.dbo.EPSInfo'
SET @SQL = 'SELECT PV.ID, SUM(PV.CurrentBalance) AS CurrentBalance, SUM(PV.NextBalance1) AS NextBalance1,
SUM(PV.NextBalance2) AS NextBalance2, PC.Active, EI.EPSName, EI.BankName, EI.ZoneName,
LC.SequenceNumber, LC.ConfigurationName
FROM tblProgressiveValue PV INNER JOIN tblProgressiveConfig PC ON PV.ID = PC.ID
INNER JOIN '+@LegalConfig+' LC ON LC.Progressive1 = PV.ID
INNER JOIN '+@EPSInfo+' EI ON LC.SequenceNumber = EI.LegalConfigNumber
WHERE (RemovalDate > ''+CONVERT(varchar(25),@TheDate,121)+'')
GROUP BY PV.ID, PC.Active, EI.EPSName, EI.BankName, EI.ZoneName,
LC.SequenceNumber, LC.ConfigurationName'
INSERT INTO @TempWAPSnapshot
EXEC (@SQL)
SET @item_category_counter = @item_category_counter + 1
END
SELECT * FROM @TempWAPSnapshot
GO
Andy
August 24, 2006 at 8:05 am
With that I am getting this error message:
Server: Msg 197, Level 15, State 1, Procedure WAPSnapshotProc, Line 62
EXECUTE cannot be used as a source when inserting into a table variable.
Follow up: I changed @TempWAPSnapshot to a temp #TempWAPSnapshot table and it works but now I'm getting:
An INSERT EXEC statement cannot be nested.......since it's nested in the while loop I'm assuming?
Had some trouble running it based on user permissions and MSDTC settings but everything works good now....Thanks to all who posted for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply