using sp_executesql

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  May 14, 2007 at 6:13 am

  #173605

  I essentially am using this for Reporting Services.  But we have a table that actually stores SQL statements to grab data for use in a report.  This is a summary report that has about 250 different line items.  I have created the following:

  DECLARE

  @SQL nvarchar(4000)

  SET

  @SQL=(SELECT AdHocSQL

  FROM

  RptValueTypeMap

  WHERE

  RptValueTypeMap.SectionCd in ('ITEM0010'))

  exec

  sp_executesql @SQL

  This works just fine, however there is no column name understandably.  I have tried using the 'AS' statement to create an alias, however this just generates an error.  What would I need to do to return something so this field is named and can be placed on the report?

  Thanks for the information.

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  May 14, 2007 at 6:17 am

  #706185

  Can you please provide some of the SQL statements from the table?

  Where did you try to insert the "AS" keyword?

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  May 14, 2007 at 6:25 am

  #706188

  For now, it is just one simple select count(*) statement that is being returned from the table:

  SELECT COUNT(*) FROM CADINV

  I am just using that select for test purposes only.

  I have tried using the as right after the EXEC sp_executesql statement and most other places in the SQL. 

• Brad Allison

  Hall of Fame

  Points: 3550

  More actions

  May 14, 2007 at 6:30 am

  #706192

  I got it.  I tried putting the alias in the select statement in the table and it is returning now with the field name alias.

  Thanks for your help.  I am newer and still learning T-SQL.