I need to store count of ROWS in table to variable in which value of a column called 'procossed' =0, if value of variable >0 will execute next step

Question

create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
declare @sql1 VARCHAR(1000)
declare @counts numeric(4)
set @counts=0;
SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT '+@counts+' = count(*) FROM '+@TableName+' where processed=0"';
EXEC MASTER..XP_CMDSHELL @sql1;
IF @ COUNTS>0
SET @SQL='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
EXEC MASTER..XP_CMDSHELL @SQL;
END;

Error occured is:

Msg 8115, Level 16, State 6, Procedure tablepass12, Line 9 Arithmetic overflow error converting varchar to data type numeric.

Answer 1

Using xp_cmdshell for this kind of tasks is a very bad idea in my opinion. However, assuming that you have a good reason to be doing things this way, this is a possible solution:

CREATE PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
 DECLARE @output TABLE (output nvarchar(max));
 DECLARE @SQL VARCHAR(8000);
 declare @sql1 VARCHAR(1000);
 declare @counts numeric(4);
 set @counts=0;
 SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -h -1 -E -Q "SET NOCOUNT ON; SELECT count(*) FROM '+@TableName+' where processed=0"';
 INSERT @output
 EXEC MASTER..XP_CMDSHELL @sql1;
 SELECT @counts = CAST([output] AS int) FROM @output WHERE [output] IS NOT NULL;
 IF @COUNTS>0
 BEGIN
 SET @SQL='SQLCMD -S SQLCLP01\SQL2012 -d tempdb -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
 EXEC MASTER..XP_CMDSHELL @SQL;
 END
END;

• sachin thana: Thanks it works, i have to call this procedure from SSIS,so i use this SQLCMD
• Aaron Bertrand: Might be better though to use EXISTS and return 1 or 0, rather than having to scan the entire table. You only care if there are zero rows or at least 1 row, so in the case where at least 1 row exists, short circuiting can be more efficient.