Loading ...
Sorry, an error occurred while loading the content.

4548Calling UDF function on linked server with parameters

Expand Messages
  • Noman Aftab
    Aug 4, 2010
    • 0 Attachment
      Hi,
      I am trying to call a function on a linked server with the following sql:


      --declarations
      declare @prefix varchar(1)
      declare @Code varchar(10)
      declare @number varchar(10)
      declare @cost decimal(22,8)
      declare @str1 varchar(max)

      --values
      set @prefix = 'V'
      set @Code = 'A100'
      set @number = 'P90'


      --first attempt (concatenating a string and passing it to openquery)
      set @str1 = 'select DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''',
      '''+@number+''') as COST'
      SELECT @cost = COST from OpenQuery(SERVER1, @str1)
      --error on first attempt (Incorrect syntax near '@str1'.)

      --second attempt (directly running the function)
      select SERVER1.DB1.dbo.WML_fn_get_BD_POCost(@prefix + @Code, @number) as COST
      --SERVER1 is the name of the linked server
      --error on second attempt (Invalid column name 'SERVER1'.)

      --third attempt (concatenation without variable in openquery)
      SELECT @cost = COST from OPENQUERY(SERVER1, 'select
      DB1.dbo.WML_fn_get_BD_POCost('''+@prefix + @Code+''', '''+@number+''') as COST')
      --error on third attempt (Incorrect syntax near '+'.)

      --fourth attempt (which works, if values are hard coded in the string)
      SELECT @cost = COST from OPENQUERY(SERVER1, 'select
      DB1.dbo.WML_fn_get_BD_POCost(''VA100'', ''P90'') as COST')


      Please Help!



      Best Wishes,
      Noman Aftab
    • Show all 13 messages in this topic