Rowcount of Views

[CarlSpackler]

I want to create a stored procedure where I pass the name of a view, the number of rows in the view is determined. If the number of rows is zero, I want the sp to fail with an error code that would bring the DTS that executed the sp down. Here is the sp so far. Note that I am EXECuting a sql string because the FROM clause cannot accept a passed variable. Problem is I need the value of my counter variable(@resultcount) to be available after the EXEC statement. Any ideas on how to do this?


CREATE PROCEDURE CheckForEmpty
@tablename varchar(50)
AS
declare @resultcount integer
declare @sSql varchar(255)
--SP to check for rowcount in passed table name. Useful in DTS steps to stop load process if no rows in sending table.

set @sSql = 'set @resultcount = (select count(*) from ' + @tablename + &#39'

exec (@sSql)

if @resultcount = 0 RETURN(-99) else return(0)

------------
CarlSpackler at 3/26/01 3:32:28 PM

I want to create a stored procedure where I pass the name of a view, the number of rows in the view is determined. If the number of rows is zero, I want the sp to fail with an error code that would bring the DTS that executed the sp down. Here is the sp so far. Note that I am EXECuting a sql string because the FROM clause cannot accept a passed variable. Problem is I need the value of my counter variable(@resultcount) to be available after the EXEC statement. Any ideas on how to do this?


CREATE PROCEDURE CheckForEmpty
@tablename varchar(50)
AS
declare @resultcount integer
declare @sSql varchar(255)
--SP to check for rowcount in passed table name. Useful in DTS steps to stop load process if no rows in sending table.

set @sSql = 'set @resultcount = (select count(*) from ' + @tablename + &#39'

exec (@sSql)

if @resultcount = 0 RETURN(-99) else return(0)