SSRS Report not showing field names

• a2zwd

  SSCrazy

  Points: 2356

  More actions

  October 20, 2011 at 6:13 am

  #244923

  Hi

  I have a stored procedure which has 4 parameters.

  Based on the parameters supplied, I am dynamically forming the sql query which is my result query to be executed.

  Its all done.

  I am executing the query using EXEC(@SqlQry) at the end sproc.

  When I execute this sproc in SSMS, executing very well.

  I want to create a report that calls this sproc. After selecting the sproc and clicking the Fields tab, I am getting no fields.

  What is the problem?

  Ex:

  DECLARE @SqlQry NVARCHAR(2000), @SqlQry1 NVARCHAR(500) = ''

  SET @SqlQry1 = CASE WHEN @StartDate IS NOT NULLTHEN 'b.StartDate >= ''' + CAST(@StartDate AS VARCHAR(10)) + ''' AND b.EndDate <= ''' + CAST(@EndDate AS VARCHAR(10)) + '''' ELSE '' END

  SET @SqlQry1 = @SqlQry1 + CASE WHEN @SqlQry1 <> '' AND @BookingName IS NOT NULL THEN ' AND b.BookingName = ''' + @BookingName + ''''

  WHEN @SqlQry1 = '' AND @BookingName IS NOT NULL THEN ' b.BookingName = ''' + @BookingName + '''' ELSE '' END

  SET @SqlQry1 = @SqlQry1 + CASE WHEN @SqlQry1 <> '' AND @HotelId IS NOT NULL THEN ' AND bd.ServiceId = ' + CAST(@HotelId AS VARCHAR(10))

  WHEN @SqlQry1 = '' AND @HotelId IS NOT NULL THEN ' bd.ServiceId = ' + CAST(@HotelId AS VARCHAR(10)) ELSE '' END

  SET @SqlQry = N'SELECT* FROM mytable' + CASE WHEN @SqlQry1 <> '' THEN ' WHERE ' + @SqlQry1 END

  EXEC(@sqlQry)

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 20, 2011 at 6:15 am

  #1397269

  Try running it once with valid parameters and then hit refresh for the fields list.

  If that doesn't work it may be a bug I've once hit but the circumstances seem very different...