Stored Procedure  Not returning Correct Info

VitaminD
VitaminD used Ask the Experts™
on
I am trying to pass 2 parameters and get back the result of my select statement
When I pass in the tablename no result set is returned

If I pass in null - I want to get back all

If I specify Database and table then I want to get back the database specified and the table specified

Any help would be greatly appreciated - My parameters are not exact it is just an example I am running the real procedure on  a production box




USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_SuggestMissingIndexes_Test]    Script Date: 05/22/2012 18:10:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SuggestMissingIndexes_Test] 'AdventureWorks', 'HumanResources.Employee'

@DatabaseName varchar(200) = NULL,
@TableName Varchar(200)= NULL
AS
BEGIN


--Declare @pDatabaseName varchar(200)
--Declare @pTableName varchar(200)
--set @pTableName = @TableName
--set @pDatabaseName = @DatabaseName

--If @DatabaseName is not null
--then
--set @pDatabaseName = 'use ' + @DatabaseName + ' ;'
--else
--set @DatabaseName = @DatabaseName

Declare @DBName varchar(100)
if @DatabaseName is not null
set @DBName = 'use  ' + @DatabaseName +'; '
else
set @DatabaseName = @DatabaseName


SELECT

migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM  sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
where (DB_NAME(mid.database_id) = @DatabaseName or @DatabaseName is null)  
and (OBJECT_NAME(mid.object_id) = @TableName or @TableName is null)  
and migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


end
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
your way of filtering data is not clear.
1. Why are you declaring @DBName if you are not using it.
2.
   Change the below in where clause

      where (DB_NAME(mid.database_id) = isnull(@DatabaseName,DB_NAME(mid.database_id))  
      and (OBJECT_NAME(mid.object_id) = isnull(@TableName,OBJECT_NAME(mid.object_id))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial