execute command - urgent!!

[shubhangi]

Hi,

Please check following code.
In below code I don't want to display results of "execute (@sql)", but want @cntA to be displayed. Is there any command to stop display of execute command as per situation.


DECLARE @YEAR1 VARCHAR(10),@MONTH1 VARCHAR(10),@SQLJOINYR VARCHAR(50),@SQLJOINMN VARCHAR(50),
@CS VARCHAR(1), @SQL VARCHAR(1000),@CSTYPE VARCHAR(50),@cntA int
SET @YEAR1 = '2004'
SET @MONTH1 = '04'
SET @CS = 'P'
SET @SQLJOINYR = ' AND year(A.joining_date)= (''' + @year1 + ''')'
SET @SQLJOINMN = ' and month(A.joining_date)= (''' + @month1 + ''')'
SET @CSTYPE = ' AND B.CS_TYPE = (''' + @CS + ''') '

SET @SQL = 'select a.employee_id from UGAM_ERM..employee_master a, emp_dept b where
a.employee_id = b.emp_id '
SET @SQL = @SQL + @SQLJOINYR + @SQLJOINMN + @CSTYPE
--PRINT (@SQL)

set nocount on
execute (@sql)
set @cntA = @@rowcount

SELECT @cntA

[galexyus]

hey,

see if this is gonna work for you:

DECLARE @YEAR1 NVARCHAR(10),@MONTH1 NVARCHAR(10),@SQLJOINYR NVARCHAR(50),@SQLJOINMN NVARCHAR(50),
@CS NVARCHAR(1), @SQL NVARCHAR(1000),@CSTYPE NVARCHAR(50),@cntA int
SET @YEAR1 = '2004'
SET @MONTH1 = '04'
SET @CS = 'P'
SET @SQLJOINYR = ' AND year(A.joining_date)= (''' + @year1 + ''')'
SET @SQLJOINMN = ' and month(A.joining_date)= (''' + @month1 + ''')'
SET @CSTYPE = ' AND B.CS_TYPE = (''' + @CS + ''') '
 
SET @SQL = 'select @cnt=COUNT(a.employee_id) from UGAM_ERM..employee_master a, emp_dept b where 
a.employee_id = b.emp_id '
SET @SQL = @SQL + @SQLJOINYR + @SQLJOINMN + @CSTYPE
--PRINT (@SQL)
 
set nocount on
EXEC sp_executesql @sql, N'@cnt INT OUTPUT', @cntA OUTPUT
set @cntA = @@rowcount
 
SELECT @cntA