0

How can I pass the Count(*) to the @FoundFields variable ? Please note that there are 2 records that satisfy this criteria in the OECompany table but thou output for @FoundFields is null :-(

--******************

declare @sql nvarchar(4000)
DECLARE @MyTable nvarchar(50);
DECLARE @SecondaryTargetField nvarchar(100)
DECLARE @FoundFields int

set @sql = N'
select 
 @FoundFields=(count(*))
from 
 sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where 
 c.name LIKE @SecondaryTargetField 
 and t.name= @MyTable'

SET @MyTable='OECompany'
SET @SecondaryTargetField='%PRODUCTGROUP%'

PRINT @sql

EXEC sp_executesql @sql, N' @FoundFields int OUTPUT, @SecondaryTargetField VARCHAR(100), @MyTable nvarchar(50)',
@FoundFields, @SecondaryTargetField, @MyTable  

PRINT @FoundFields
Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
SQLsavvy
  • 25
  • 4

1 Answers1

1

Add OUTPUT keyword to the field you want to return from query.

    EXEC sp_executesql @sql, N' @FoundFields int OUTPUT, @SecondaryTargetField VARCHAR(100), @MyTable nvarchar(50)',
@FoundFields OUTPUT,
@SecondaryTargetField,
@MyTable
Pavel Dmitrenko
  • 310
  • 2
  • 7