How can I Execute an insert statement that is dynamically created?

• matsinopoulos

  Ten Centuries

  Points: 1049

  More actions

  January 7, 2009 at 3:42 pm

  #96880

  [font="Comic Sans MS"]

  Hi All,

  I would like to know how can I execute an insert statement that is dynamically created?

  I know that there is a system sp "sp_sqlexec". I call it using a piece of code like the following:

  declare @my_final_sql varchar(MAX);

  set @my_final_sql = 'insert into tablea (col1, col2) select col1, col2 from '

  declare @my_view varchar(MAX);

  set @my_view = 'view1';

  set @my_final_sql = @my_final_sql + @my_view;

  exec sp_sqlexec @my_final_sql;

  However, this gives me the error:

  Only functions and extended stored procedures can be executed from within a function.

  So, is there any solution to my problem?

  Note: I forgot to say that I call the above piece of code from within a function. Is the problem around there?

  [/font]

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  January 7, 2009 at 3:53 pm

  #922979

  Yes. There is a problem.

  User defined functions does not allow dynamic execution of SQL.

  I think you will have to use CLR or work around with other options.

  Regards,

• matsinopoulos

  Ten Centuries

  Points: 1049

  More actions

  January 7, 2009 at 4:42 pm

  #922998

  [font="Comic Sans MS"]

  Thank you for the answer.

  I will try CLR. BTW, what do you have in your mind when saying about other options?

  [/font]

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  January 7, 2009 at 5:14 pm

  #923010

  I will see if i really need to use UDF.

  Else i will call the code from a stored proc.

  Regards,

• matsinopoulos

  Ten Centuries

  Points: 1049

  More actions

  January 7, 2009 at 5:27 pm

  #923012

  Ok. Thanks a lot for your answers.

  BR

  Panayotis Matsinopoulos