469,125 Members | 1,249 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

How to pass var value outside from exec command string environment

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.
Jun 20 '10 #1
2 1598
ck9663
2,878 Expert 2GB
You can directly insert the record with storing it... Something like:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE @DateFrom as varchar(10),@DateTo as varchar(10)
  3.  
  4. set @DateFrom ='20100101' ;
  5. set @DateTo='20100107'; 
  6.  
  7. INSERT INTO tb_StudentAnalysis 
  8. (select 'Total No of Students',
  9. ISNULL(COUNT(*),0) 
  10. FROM tb_StudentMaster 
  11. WHERE Student_doj>=CONVERT(varchar(10),@DateFrom,121) AND 
  12.       Student_doj<=CONVERT(varchar(10),@DateTo,121))
  13.  
  14.  
Happy Coding!!!

~~ CK
Jun 22 '10 #2
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)
Jun 22 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jimmy Jim | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.