Returning a value from dynamic SQL?!

• WebTechie

  SSChampion

  Points: 11405

  More actions

  July 17, 2009 at 6:27 pm

  #385010

  Hello,

  I've written a stored procedure that has a while loop. I don't like using cursors.

  The loop helps me to get retrieve variables. I have a insert statement that uses these variables. Instead of making all of this dynamic sql, I thought I would just retrieve the values of select statements. For example:

  --Initialize counter

  --******************

  set @counter = 1

  set @loopcounter = isnull((select count(*) from db_control),0)

  set @q = ''''

  --Begin Main loop

  --loops for each interid

  --**********************

  While @counter <= @loopcounter BEGIN set @mCompany = (select interid from db_control where id = @counter)
  set @mGap = (select gap - 1 from db_control where id=@counter)
  set @mBegin = (select min(row_id) from ce..gl20000)
  set @mEnd = (select max(row_id) from ce..gl20000)

  Insert into myControl_table (db_control_id, beg_row_id, end_row_id, InsertTime,processed_flag)
  values (@counter, @mBegin, @mEnd, getdate(),'N')

  set @counter = @counter + 1

  END

  Works great. But I need to make this dynamic. I need it to be:

  set @mBegin = (select min(row_id) from @CompanyName..@GLTable)

  I'm trying to use sp_ExecuteSQL, but I'm not getting anywhere with this. I'm looking at websites but I am not getting the syntax.

  set @sql = N'select @myMinValueOUT = min(row_id) from @CompanyName..@GLTable)';

  SET @ParmDefinition = N'@CompanyName varchar(5),' +

  N'@GLTable varchar(8),' +

  N'@myMinValueOUT Int OUTPUT';

  set @mCompanyName = 'CE'

  set @AcctTable = '20000'

  EXECUTE sp_executesql

  @sql,

  @ParmDefinition,

  @CompanyName = @mCompanyName,

  @GLTable = @AcctTable,

  @myMinValueOUT=@mLow OUTPUT;

  *************************************************************

  Error message: Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

  **************************************************************

  How in the world do I just return the min and max of a dynamic SQL statement without doing cursors or creating a template table and populating that table with select statements? Won't sp_executeSQL work?

  Thanks.

  Tony

  Things will work out.  Get back up, change some parameters and recode.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  July 20, 2009 at 2:12 am

  #1026792

  Looks like you declare @sql of type varchar rather than nvarchar.

  Didn't you?

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• WebTechie

  SSChampion

  Points: 11405

  More actions

  July 20, 2009 at 4:28 am

  #1026841

  Gianluca,

  Thanks for that. I made the change and it got past that error message. Then I was able to see a couple of other things that were wrong in the syntax.

  However, I was able to get it to work. Thanks for seeing that for me!

  Tony

  😀

  Things will work out.  Get back up, change some parameters and recode.