How to pass var value outside from exec command string environment

[romiverma]

hi,
am tring to store the result of query in a variable using exec query string syntax. but i am not able to save this var value in a table because it is out of scope of exec environment. here is the code

SET @Query='DECLARE @TotRegistered as Int,@DateFrom as Datetime,@DateTo as Datetime;
set @DateFrom='+'''01/01/2010 00:00:00'''+' ;
set @DateTo='+'''01/07/2010 23:59:59'''+';
SELECT @TotRegistered=ISNULL(COUNT(*),0) FROM tb_StudentMaster WHERE Student_doj>=CONVERT(varchar(10),@DateFrom,121) AND Student_doj<=CONVERT(varchar(10),@DateTo,121);prin t @TotRegistered'

exec(@Query)

INSERT INTO tb_StudentAnalysis VALUES
('Total No of Students',@TotRegistered)

this code is printing the var value but its not inserting in table bcoz it is out of exec work environment. plzz help how to do this.

[ck9663]

You can directly insert the record with storing it... Something like:

 
DECLARE @DateFrom as varchar(10),@DateTo as varchar(10)
 
set @DateFrom ='20100101' ;
set @DateTo='20100107'; 
 
INSERT INTO tb_StudentAnalysis 
(select 'Total No of Students',
ISNULL(COUNT(*),0) 
FROM tb_StudentMaster 
WHERE Student_doj>=CONVERT(varchar(10),@DateFrom,121) AND 
      Student_doj<=CONVERT(varchar(10),@DateTo,121))
 
 

Happy Coding!!!

~~ CK

[romiverma]

hi,
thx for reply. i know i can do this but my problem is that tb_StudentAnalysis has many columns like Male, Female, Category etc. so am storing count for all these fields from tb_studentmaster in variables and then i'll use these variables to insert in tb_studentAnalysis once at a time as single record. i have to get counts using exec(@Query) bcoz i have to use same query with different filter strings. so i'll be grateful if u can tell me how to retreive the var value using the exec(@Query)