sp_executesql

• Mark F-428640

  Hall of Fame

  Points: 3659

  More actions

  June 4, 2008 at 1:56 pm

  #192005

  I need a fresh set of eyes can anyone tell me what is wrong with this script:

  DECLARE @SQLString nvarchar(50);

  DECLARE @ParmDefinition nvarchar(100);

  DECLARE @Trig_ID_Out VARCHAR(10);

  SET @SQLString = N'spiTriggerControl';

  SET @ParmDefinition = N'@Trig_ID VARCHAR(10) OUTPUT';

  EXECUTE sp_executesql @SQLString, @ParmDefinition, @Trig_ID=@Trig_ID_Out OUTPUT;

  SELECT @Trig_ID_Out

  I am getting the following syntax error:

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near 'spiTriggerControl'.

  Thanks,

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  June 4, 2008 at 2:02 pm

  #824501

  I think you need this:

  SET @SQLString = N'spiTriggerControl';

  to be this:

  SET @SQLString = N'Exec spiTriggerControl';

  I'm not sure why you are using sp_executesql based on the information you have provided. You could just do:

  DECLARE @Trig_ID_Out VARCHAR(10);

  Exec spiTriggerControl @Trig_ID_Out OUTPUT;

  SELECT @Trig_ID_Out

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question