472,145 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

How to pass var value outside from exec command string environment

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'


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 1645
2,878 Expert 2GB
You can directly insert the record with storing it... Something like:

Expand|Select|Wrap|Line Numbers
  2. DECLARE @DateFrom as varchar(10),@DateTo as varchar(10)
  4. set @DateFrom ='20100101' ;
  5. set @DateTo='20100107'; 
  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))
Happy Coding!!!

~~ CK
Jun 22 '10 #2
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 Saiars | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.