March 28, 2010 at 7:52 am
I have a procedure in which I'm having a problem with the proper syntax of passing parameters within the sp_executesql command. Looked at BOL and tried different variations but keep getting msg back when attempting to run proc that a scalar variable must be declared. Proc is intended to substitute different values for data retreival from the DMV, dm_db_index_physical_stats for info to determine index candidates for rebuild.
This is first time using subbing parameters into this type of construct so any assist would be appreciated. thks
USE [DBA]
GO
if exists (select * from dba.dbo.sysobjects where id =
object_id(N'[dbo].[sp_IndexDefragAndRebuild_TEST]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_IndexDefragAndRebuild_TEST
go
/****** Object: StoredProcedure [dbo].[sp_IndexDefragAndRebuild_test] Script Date:
03/12/2010 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[sp_IndexDefragAndRebuild_TEST]
@dbname nvarchar(100)
AS
SET NOCOUNT ON
DECLARE @FragPercentVAR int,
@PageCountVAR int,
@database_name nvarchar(128),
@cmd nvarchar(1500),
@tbltypeVAR nvarchar(15),
@indxidVAR int,
@parmdef1 NVARCHAR(500),
@parmdef2 NVARCHAR(500),
@parmdef3 NVARCHAR(500),
@parmdef4 NVARCHAR(500)
-- Set Frag Analysis Parameters
set @parmdef1 = N'@fragpercent int'
set @parmdef2 = N'@indxid int'
set @parmdef3 = N'@TblType varchar(30)'
set @parmdef4 = N'@PageCount int'
set @indxidVAR = 0
SET @FragPercentVAR = 10 -- specify the desired fragmentation level
SET @PageCountVAR = 10 -- specify page count to consider large tables only
SET @tbltypeVAR = 'Base Table'
print @dbname
SET @Cmd = 'USE [' + @DBName + '];
SELECT db_name(ind.database_id)DBName,
tab.TABLE_SCHEMA,
tab.TABLE_NAME,
ind.page_count,
ind.avg_fragmentation_in_percent,
ind.index_id
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) ind
join INFORMATION_SCHEMA.TABLES tab on object_name(ind.object_id)= tab.TABLE_NAME
WHERE ind.avg_fragmentation_in_percent > @FragPercent
AND ind.index_id > @indxid
AND tab.TABLE_TYPE = '+ char(39) + '@TblType ' + CHAR(39) + '
AND ind.page_count > @PageCount
ORDER BY ind.avg_fragmentation_in_percent DESC '
print @cmd
EXECUTE sp_executesql @cmd,@parmdef1,@parmdef2,@parmdef3,@parmdef4,@FragPercent=@FragPercentVAR,@indxid=@indxidVar,@tbltype=@tbltypeVAR,@PageCount=@PageCountVAR
March 28, 2010 at 8:20 am
Maybe a simple example will help:
CREATE TABLE #Sample (A INTEGER NULL, B VARCHAR(3) NULL);
DECLARE @Param1 INTEGER,
@Param2 VARCHAR(3)
SELECT @Param1 = 2,
@Param2 = 'DEF';
EXECUTE sp_executesql
@stmt = N'INSERT #Sample (A, B) VALUES (@P1, @P2)',
@params = N'@P1 INTEGER, @P2 VARCHAR(3)',
@P1 = @Param1, @P2 = @Param2;
SELECT S.A,
S.B
FROM #Sample S;
DROP TABLE #Sample;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply