August 3, 2010 at 8:55 am
declare @sql as varchar(max)
Declare @CostCenterCode varchar (max)
Set @CostCenterCode ='123'
print @CostCenterCode
set @SQL ='SELECT FundingCostCenterId
FROM gtl_srf_funding C
JOIN F_Adv_iter_charlist_to_tbl( , DEFAULT) s
ON C.FundingCostCenterId in(s.nstr)'
exec @sql
Print @sql
i m not able to parse this d sql can anyone help? thanks
August 3, 2010 at 9:07 am
Your dynamic SQL is producing what you've told it to produce.
DECLARE @sql AS VARCHAR(MAX)
DECLARE @CostCenterCode VARCHAR (MAX)
SET @CostCenterCode ='123'
PRINT @CostCenterCode
SET @SQL ='SELECT FundingCostCenterId ' + 'FROM gtl_srf_funding C ' +
'JOIN F_Adv_iter_charlist_to_tbl( , DEFAULT) s ' +
'ON C.FundingCostCenterId in(s.nstr)'
--EXEC @sql
PRINT @sql
123
SELECT FundingCostCenterId FROM gtl_srf_funding C JOIN F_Adv_iter_charlist_to_tbl( , DEFAULT) s ON C.FundingCostCenterId in(s.nstr)
The syntax error is : -
( , DEFAULT)
What were you trying to do?
August 3, 2010 at 9:11 am
actually see this code.
declare @sql as varchar(Max),@CostCenterCode varchar(Max)
set @CostCenterCode ='123'
set @SQL ='SELECT FundingCostCenterId
FROM gtl_srf_funding C
JOIN F_Adv_iter_charlist_to_tbl'(' +@CostCenterCode+ ', ''DEFAULT'')' s
ON C.FundingCostCenterId in(s.nstr)'
exec @sql
Print @sql
it gives Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ' +@CostCenterCode+ '.
i m not able to adjust these commas in this dynamic sql. i m joining with one function and trying to supply the value for costcenterid
August 3, 2010 at 10:01 am
dallas13 (8/3/2010)
it gives Msg 102, Level 15, State 1, Line 5Incorrect syntax near ' +@CostCenterCode+ '.
i m not able to adjust these commas in this dynamic sql. i m joining with one function and trying to supply the value for costcenterid
Apologies, didn't spot your reply until just now.
Try this: -
DECLARE @sql AS VARCHAR(MAX),
@CostCenterCode VARCHAR(MAX)
SET @CostCenterCode ='123'
SET @SQL ='SELECT FundingCostCenterId ' + 'FROM gtl_srf_funding C ' +
'JOIN F_Adv_iter_charlist_to_tbl(' + @CostCenterCode +
', ''DEFAULT'') s ' + 'ON C.FundingCostCenterId in(s.nstr)'
EXEC @sql
PRINT @sql
August 3, 2010 at 10:08 am
Thanks for the reply. I can parse this statement successfully but when i hit execute i can see this
Msg 911, Level 16, State 1, Line 9
Could not locate entry in sysdatabases for database 'SELECT FundingCostCenterId FROM gtl_srf_funding C JOIN F_Adv_iter_charlist_to_tbl(123, 'DEFAULT') s ON C'. No entry found with that name. Make sure that the name is entered correctly.
Do I need to specify 4 part naming convention in front of my function name??
August 3, 2010 at 10:16 am
dallas13 (8/3/2010)
Thanks for the reply. I can parse this statement successfully but when i hit execute i can see thisMsg 911, Level 16, State 1, Line 9
Could not locate entry in sysdatabases for database 'SELECT FundingCostCenterId FROM gtl_srf_funding C JOIN F_Adv_iter_charlist_to_tbl(123, 'DEFAULT') s ON C'. No entry found with that name. Make sure that the name is entered correctly.
Do I need to specify 4 part naming convention in front of my function name??
My bad, didn't check my syntax. Should have been like this: -
DECLARE @sql AS VARCHAR(MAX),
@CostCenterCode VARCHAR(MAX)
SET @CostCenterCode ='123'
SET @SQL ='SELECT FundingCostCenterId ' + 'FROM gtl_srf_funding C ' +
'JOIN F_Adv_iter_charlist_to_tbl(' + @CostCenterCode +
', ''DEFAULT'') s ' + 'ON C.FundingCostCenterId in(s.nstr)'
EXEC (@sql)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply