ALTER PROC SP_AUDIT_TABLE @Table_Name varchar(150)
as
declare @SqlString varchar(8000),
	    @Total numeric

set @SqlString = 'set ' + @Total + '= (select count(*) from ' + @Table_Name+ ')'

print @SqlString
exec (@Sqlstring)
print @Total


exec SP_AUDIT_TABLE 'Supplier_Table'

quote:

---

Originally posted by Kristen

set @SqlString = 'set ' + @Total + '= (select count(*) from ' + @Table_Name+ ')'
...
exec (@Sqlstring)

note that @Total in the outer layer (your Sproc) will be out of scope to the inner layer (the code executing inside your EXEC statement).

sp_ExecuteSQL gets around that by being able to pass parameters in, and back out again - as my willing & able assistant friend Harsh has just demonstrated

Kristen

---

quote:

---

Originally posted by Kristen

Friends get paid less than assistants!!

---

ALTER PROC SP_AUDIT_TABLE @Table_Name varchar(150)
as
declare @SqlString varchar(8000),
	    @Total int

set @SqlString = N'select @Total = count(*) from ' + @Table_Name

Exec sp_executesql @SqlString, N'@Total int output', @Total Output

select @Total

exec SP_AUDIT_TABLE 'Supplier_Table'

--exec SP_AUDIT_TABLE 'Supplier_Table'

ALTER PROC SP_AUDIT_TABLE
@Table_Name varchar(150)
as
declare @SqlString nvarchar(4000),
		@Column_name varchar(150),
		@Counter numeric,
	    @Total int,
		@Max int

--Create Audit Table if not exists
If Not Exists (Select NAME From Sysobjects WHERE NAME = 'Table_Audit')
Begin
	Create Table Table_Audit
	(
		Table_Name varchar(200),
		Total_Count numeric,
		Column_Name varchar(200), 
		Blank_Quantity int, 
		Percentage_Blank float, 
		Distinct_Count int, 
		Percentage_Distinct float,
		Min_Length int, 
		Max_Length int, 
		Min_value nvarchar(400), 
		Max_Value nvarchar(400), 
		Nulls int,
		Date_run datetime
	)
end

--truncate table...
truncate table Table_Audit

set @SqlString = N'select @Total = count(*) from ' + @Table_Name
Exec sp_executesql @SqlString, N'@Total int output', @Total Output

set @SqlString = N'select @Max = count(*) from information_schema.columns where table_name = ''' + @Table_Name + ''''
Exec sp_executesql @SqlString, N'@Max int output', @Max Output

set @Counter = 1

while @Counter <= @Max
	Begin
		set @SqlString = N'select @Column_name =  Column_Name from information_schema.columns where table_name = ''' + @Table_Name + ''' and Ordinal_position = ' +@Counter
		Exec sp_executesql @SqlString, N'@Column_name varchar(150) output', @Column_name Output
		set @Sqlstring = '
		select '''+@Table_Name+'''as Table_Name, 
		count(*) as Total_Count,
		''' +@Column_Name+ '''as Column_Name, 
		sum(case when cast([' +@Column_Name+ '] as varchar) is not null and cast([' +@Column_Name+ '] as varchar) <> '''' then 0 else 1 end) as Blank,
		left(cast(sum(case when cast([' +@Column_Name+ '] as varchar) is not null and cast([' +@Column_Name+ '] as varchar) <> '''' then 0 else 1 end) as numeric)
		/ ' + cast(@Total as varchar) +' * 100,5) as Percentage_Blank,
		count(distinct [' +@Column_Name+ ']) as [Distinct],
		left(cast(count(distinct [' +@Column_Name+ ']) as numeric)/' + cast(@Total as varchar) +' * 100,5) as Percentage_Distinct,
		min(len(cast([' +@Column_Name+ '] as varchar))) as Min_length, 
		max(len(cast([' +@Column_Name+ '] as varchar))) as Max_Length,
		cast(min([' +@Column_Name+ ']) as varchar) as Min_Value,
		cast(max([' +@Column_Name+ ']) as varchar) as Max_Value,
		sum(case when [' +@Column_Name+ '] is null then 1 else 0 end) as [Nulls],
		getdate() as Date_Run
		from ' + @Table_Name
		--print @Sqlstring
		insert into Table_Audit
		exec sp_executesql @Sqlstring
		set @Counter = @Counter + 1
	End

select * from Table_Audit

set @SqlString = N'select @Column_name =  Column_Name from information_schema.columns where table_name = ''' + @Table_Name + ''' and Ordinal_position = ' +@Counter

select @Column_name =  Column_Name 
from information_schema.columns
where table_name = @Table_Name
and Ordinal_position = @Counter