Use Variable Table Name within Function

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

SQL Server Development (2000)

Use Variable Table Name within Function

Author

Topic

fairytalepbgk
Starting Member

1 Post

Posted - 2009-05-11 : 18:08:58

I want to use a Variable Table Name in Select statement within a function, bat i don do it.

In my function i want to get an ID from a table from current year base with join a table from previous year base (i get the privious year base from an other function - this function is already use) and filter (where) on the ID field on a table on previous year base.

I try this:

CREATE FUNCTION DBO.CrntPronID ( @VPREVPRONID BIGINT )
RETURNS BIGINT
AS
BEGIN
DECLARE @VPREVBASE NVARCHAR(60)
DECLARE @TEMPSQL NVARCHAR(4000)
SET @VPREVBASE = (SELECT DBO.PREVBASE('OSDE'))
/* if current base is 'OSDE2009_2' return 'OSDE2007_2' */
EXEC('CREATE TABLE #CrntPronID (MULTI_ID BIGINT)')
SET @TEMPSQL = 'SELECT ISNULL(MAX(PN.PRON_ID),0)
FROM OSDPRON PN
INNER JOIN OSDPOIK PK
ON PN.PRON_ID = PK.POIK_ID_PRON
INNER JOIN OSDLINKBASES
ON PK.POIK_ID = LINKBASES_ID_POIK
INNER JOIN [' + @VPREVBASE + '].DBO.OSDPRON OPN
ON LINKBASES_OLD_PRON_CODE = OPN.PRON_CODE
INNER JOIN [' + @VPREVBASE + '].DBO.OSDPOIK OPK
ON LINKBASES_OLD_POIK_CODE = OPK.POIK_CODE
AND OPN.PRON_ID = OPK.POIK_ID_PRON
WHERE OPN.PRON_ID = ' + @VPREVPRONID + ' '
INSERT #CrntPronID (MULTI_ID) EXECUTE @TEMPSQL
DECLARE @VEXTRACT BIGINT
SET @VEXTRACT =
( SELECT CAST(ISNULL(MAX(MULTI_ID),0) AS BIGINT)
FROM TSP_CrntPronID )
RETURN @VEXTRACT
END

I try to do and used other ways, like DECLARE VARIABLE TABLE and USED TABLE ON CURRENT DATABASE but all ways are proved wrong.

Please Help me..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-11 : 22:23:12

You can't execute dynamic sql or stored procedures inside a user-defined function. You can do this in a stored procedure and return the ID back through an output parameter:

CREATE FUNCTION DBO.CrntPronID ( @VPREVPRONID BIGINT, @VEXTRACT BIGINT OUTPUT) AS
BEGIN
DECLARE @VPREVBASE NVARCHAR(60)
DECLARE @TEMPSQL NVARCHAR(4000)
SET @VPREVBASE = (SELECT DBO.PREVBASE('OSDE'))
/* if current base is 'OSDE2009_2' return 'OSDE2007_2' */
EXEC('CREATE TABLE #CrntPronID (MULTI_ID BIGINT)')
SET @TEMPSQL = 'SELECT ISNULL(MAX(PN.PRON_ID),0)
FROM OSDPRON PN
INNER JOIN OSDPOIK PK
ON PN.PRON_ID = PK.POIK_ID_PRON
INNER JOIN OSDLINKBASES
ON PK.POIK_ID = LINKBASES_ID_POIK
INNER JOIN [' + @VPREVBASE + '].DBO.OSDPRON OPN
ON LINKBASES_OLD_PRON_CODE = OPN.PRON_CODE
INNER JOIN [' + @VPREVBASE + '].DBO.OSDPOIK OPK
ON LINKBASES_OLD_POIK_CODE = OPK.POIK_CODE
AND OPN.PRON_ID = OPK.POIK_ID_PRON
WHERE OPN.PRON_ID = ' + @VPREVPRONID + ' '
INSERT #CrntPronID (MULTI_ID) EXECUTE @TEMPSQL
DECLARE @VEXTRACT BIGINT
SET @VEXTRACT =
( SELECT CAST(ISNULL(MAX(MULTI_ID),0) AS BIGINT)
FROM TSP_CrntPronID )
END

Usage:

DECLARE @ID bigint
EXEC DBO.CrntPronID @VPREVPRONID=10, @ID OUTPUT
SELECT @ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources