Load variable with dynamic sql executed value

• DavidCl

  Mr or Mrs. 500

  Points: 588

  More actions

  January 18, 2006 at 7:31 am

  #91383

  I am working on a moderately sophisticated product pricing algorithm.  A part of the solution is to take a dynamically built formula execute it to get a value that can be used in other parts of the algorithm.  I have seen this done before some years ago, but I have not been able to get the syntax.

  Here is a sample of what I need to do:

  declare @val as money

  declare @sql as varchar(1000)

  set @sql = 'select (329.02 * 1.2) * 1.20'

  exec @val = (@SQL)

  But this SQL server thinks I am looking for a procedure with this syntax.  I have tried a couple of variations to no avail.

  Thanks in advance for your help.

  David

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  January 18, 2006 at 7:54 am

  #615490

  You can try with sp_ExecuteSql:

  DECLARE @sql nvarchar(4000),  @val money

            

     SELECT @sql = 'select @val=(329.02 * 1.2) * 1.20'

     EXEC sp_executesql @sql, N'@Val money OUTPUT', @val OUTPUT

     SELECT @val

  Hope this may help you.

  Cheers,
  Prithiviraj Kulasingham

  http://preethiviraj.blogspot.com/

• DavidCl

  Mr or Mrs. 500

  Points: 588

  More actions

  January 18, 2006 at 10:08 am

  #615548

  That works for what I am doing. 

  Thank you Preethiviraj Kulasingham for your help.