SQL Server – Store Row Count in Variable and Execute Next Step

sql serverstored-procedures

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.

Best Answer

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;
Related Question