Help: assigning result set to a variable

What are you trying to achieve? It seems that you have the same query twice, once as regular query and second as a dynamic query.

The @strScript variable is not used. I think you intended to store the query string (second query) to @strScript and not to @strCurrDt, which is only set to 8 characters as varchar(8).

Clear you mind and come again.

I am trying to run a loop here, which I did not include in the snippet.  

So, initially the @strCurrDt is assigned the value of max(Load Dt) from table, and when the loop begins, it does some processing for that date and has to go back one month on the @strCurrDt (loop runs 12 times).  So, I need to store the value of new @strCurrDt .  I am able to assign the result set to the variable with the first statement (which gets max date and runs only once in the procedure), but not with the second (gets previous month).

Here is the corrected snippet..

Declare @strCurrDt varchar(8),
	@strScript varchar(300)	
 
-- gets max date from the table
 
set @strCurrDt = (select convert(varchar(8),(cast(convert(varchar(8), max(LOAD_DT_ID)) as datetime)),112) from MTHLY_DETL)
	
--Print @strCurrDt
 
while counter < 13
begin
 
-- processing statements goes here, uses @strCurrDt
 
--this is not working - assigning previous month to @strCurrDt
 
Set @strCurrDt = 'SET @strScript = (SELECT 
convert(varchar(8),dateadd(month, -1, (cast( ' + @strCurrDt + ' as datetime))), 112) as DATE)'
 
exec @strScript
 
end

Select allOpen in new window

Again, you declare @strScript, and you use it in exec @strScript statement but you never set it! It's empty!

Again, you declare @strScript, and you use it in exec @strScript statement but you never set it! It's empty!

What type is column LOAD_DT_ID?

Is it a date of a varchar?

Ok I got it.

First you change the date variable to datetime type and you don't need all that fuss of converting as the LOAD_DT_ID is already a date.
Make the @strScript 8000 chars long to make sure is big enough.
You need a counter variable @counter as integer

You can add to a date a number of years, quarters, months, weeks, days, hours, minutes, seconds and milliseconds using the DATEADD function with a parameter that tells you add months (mm) and the number -1. It adds -1 months to the date. (details here: http://msdn.microsoft.com/en-us/library/ms186819.aspx)

 Increment the loop counter with 1 and assign the new value to your date variable.

Sorry, I forgot the code:

Declare 
	@strCurrDt datetime,
	@strScript varchar(8000),
	@counter int
 
-- gets max date from the table
select 
	@counter=1,
	@strCurrDt = max(LOAD_DT_ID)
from 
	MTHLY_DETL
        
--Print @strCurrDt
 
while @counter < 13
begin
 
	-- processing statements goes here, uses @strCurrDt
 
 
	exec @strScript
 
	-- increment counter and reset the @strCurrDt to one month before
	select 
		@counter=@counter+1,
		@strCurrDt = dateadd(mm, -1, @strCurrDt)
end

Select allOpen in new window

Sorry, yes, that was mistake - one of those days.

LOAD_DT_ID is a int in the table.

I tried a different approach as well, but I get a error saying "Must declare the scalar variable "@strCurrDt" for the second statement.

Script is attached.

Declare @strCurrDt varchar(8),
	@strScript varchar(300)	
							
 
--this executes succesfully
 
select @strCurrDt = convert(varchar(8),(cast(convert(varchar(8), max(LOAD_DT_ID)) as datetime)),112) from PBANKDW.BANKDW.vw_IMPX_ACCT_MTHLY_DETL
 
print @strCurrDt
 
-- this does not!!
 
Set @strScript = ('SELECT @strCurrDt = (convert(varchar(8),dateadd(month, -1, (cast(' + '''' + @strCurrDt + '''' + ' as datetime))), 112))')
 
exec(@strScript)
 
print @strScript
 
--following runs successfuly; please note that the trouble is if I 'set' the result set to the variable
 
--Declare @strCurrDt varchar(8)
--SELECT @strCurrDt = (convert(varchar(8),dateadd(month, -1, (cast('20081031' as datetime))), 112))
--print @strCurrDt

Select allOpen in new window

Here a better version with print for the variable to test:

Declare 
	@strCurrDt datetime,
	@strScript varchar(8000),
	@counter int
 
-- initialize @counter and get max date from the table
select 
	@counter=1,
	@strCurrDt = max(LOAD_DT_ID)
from 
	MTHLY_DETL
        
 
while @counter < 13
begin
 
	-- processing statements goes here, uses @strCurrDt
 
	Print @strCurrDt
 
 
--	exec @strScript
 
	-- increment counter and reset the @strCurrDt to one month before
	select 
		@counter=@counter+1,
		@strCurrDt = dateadd(mm, -1, @strCurrDt)
end

Select allOpen in new window

You probably don't need a loop. It is better to avoid loops whenever possible. Maybe if you explain what you want to do I can show you.

<< LOAD_DT_ID is a int in the table >>

So how do you want to convert an int to a date? Give me some data samples from that column.

I am converting into to varchar(8) - yyyymmdd format -  in the script and it works fine.  But, that is not the issue nor the loop.

The issue is how can I use a variable inside a sql statement and then assign the result set to the same variable.  something similar to x = x + 1, but on a SQL statement result set.

Please see this:
Set @strScript = ('SELECT @strCurrDt = (convert(varchar(8),dateadd(month, -1, (cast(' + '''' + @strCurrDt + '''' + ' as datetime))), 112))')

You don't need to do that convert to varchar in order to work.
I already showed you how to change the date. That is not the way to go. What you want is to modify the current date to a month before date. You do that with

        select
                @strCurrDt = dateadd(mm, -1, @strCurrDt)

There are things in SQL that should be done in a certain way and that is what I am trying to show you. You want to use a dynamic query to to change a value in a variable which definetely you don't have to do. Even if you want to keep the varchar you are not suppose to use dynamic query, is totally unnecessary for this purpose.

Let me understand what int you have in that column that you can turn in yyyymmdd format, I am confused.

If you really want to keep your varchar(8) then you use:

select 
	@strCurrDt = convert(varchar(8), dateadd(mm, -1, cast(@strCurrDt as datetime)), 121)

Select allOpen in new window

That above is equivalent to x = x - 1 in your terms.

Sorry the convert code should have been 112 instead of 121.

Great!! that works perfect.   thanks much!  :)

I don't how so many conversations came into the code - my brain is addled a bit too much of coding.  Thanks for pointing out..

Anyways, thanks.  I have the loop in place, but I just got stuck with this here and was not able to go forward.

Sorry the (while counter > 13) should be (while counter < 13) I was going a different way about it at first.  And just as Guru has stated the @strScript is never being set.  I left it that way without thinking.  This should be exec @strCurrDt instead.  The only thing is that your procedure only fires once.  The values would need to be put into a temp table if you are wanting to collect them and then return them by selecting from the temp table.  I have a feeling that there is something missing to all this however.

which post helped?

TroyCrowe's helped.  I marked it as the solution.

That solution will never help because it will NEVER work:

1. The counter in the while will generate error as it is not a column(you cannot use one in while) nor a variable as it wasn't declared any as a counter.

2. This statement:
et @strCurrDt = 'SET @strScript = (SELECT convert(varchar(8),dateadd(month, -' + counter + ', cast( ' + @strCurrDt + ' as datetime)), 112) as DATE)'
is useless because @strCurrDt is a varchar(8), 8 character long, so if you assign to it that string it will be truncated to the first 8 character and all you'll get in strCurrDt will be 'SELECT '

3. exec @strScript will do nonthing as the @strScript is empty, not set with any value after the declare statement

4. set counter = counter + 1  will fail for the same reason the while will fail, counter is not a variable nor a column.

Lets make the following changes to correct the errors Zberteoc has stated.
The corrected code below will need some futher changes to be used; it's most likly a piece of
a bigger picture.  If not one solution to follow would be to take the values in @strScript and insert them into a temp table and then pass it back to the application that would use the data.

Declare @strCurrDt varchar(8),
        @strScript varchar(400),
        @counter int  
 
set @strCurrDt = (select convert(varchar(8),max(LOAD_DT_ID),112) from MTHLY_DETL)
 
set counter = 1
while counter > 13
begin
 
Set @strScript = 'SET @strScript = (SELECT convert(varchar(8),dateadd(month, -' + counter + ', cast( ' + @strCurrDt + ' as datetime)), 112) as DATE)'
 
exec @strScript
 
set counter = counter + 1 
end

Select allOpen in new window

TroyCrowe,

Your solution still WILL NOT work, WILL FAIL, because when you execute a script with EXEC statement the server generates a different scope where the @strScript or @strCurrDt are UNKNOWN variables as they were not declared inside the dynamic script!!! The outside code and the inside code from a dynamically generated SQL batch are in two different scopes that DON"T SHARE access to the same variables! If anybody tried your code would have failed with:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@strScript".

Let's say you declared the variables inside that dynamic script, what the author wanted, as he specified, was an equivalent to

x=x-1   and not     x=x-counter  

as you gave. Your solution will do

date - 1 month i first iteration
date - 2 months i second iteration
date - 3 months in third iteration

was this what the author was really looking for?


By far the solution for this should have been the one I gave as:

select
        @strCurrDt = convert(varchar(8), dateadd(mm, -1, cast(@strCurrDt as datetime)), 112)

or maybe, which I doubt

select
        @strCurrDt = convert(varchar(8), dateadd(mm, -@counter, cast(@strCurrDt as datetime)), 112)

No absurd scripts and EXEC (script) when totally unneeded. This is a correct solution from SQL perspective.

And even if you did declate the variable inside:

Set @strScript = 'DECLARE @strScript varchar(300); SET @strScript = (SELECT convert(varchar(8),dateadd(month, -' + counter + ', cast( ' + @strCurrDt + ' as datetime)), 112) as DATE)'
 
exec @strScript

It still wouldn't do anything as the 2 @strScript variables are in different scopes and totally different for that matter and the EXEC will just set the value for the @strScript variable within the inside code but the result will never be  accessible to the outside code.

Ok.  Let get this cleared up sinse it is such the big deal.  As Zberteoc has stated, the @strScript in the dynamic query will not work.  It is not declared inside the dynamic query.  And yes the dynamic query is not needed, however that is the direction the author was presenting what the author wanted to do.  I did not feel I needed to rewrite it for the author.  So for the sake of the grevance of this situation.  Let's rewrite it so that it is usable.  With the solution the date will decrement, as Zberteoc has stated, which is what I believe the author wanted sinse he accepted the solution immediately after I added it to the solution.  As I mentioned before, I would not use the dynamic query, but instead create a temp table before the loop and insert the @strScript value 1 at a time through the loop and return a table instead.  I will not take the time to code that out for this posting.  This posting is still following the original layout the author coded.

Declare @strCurrDt varchar(8),
        @strScript varchar(400),  This is the declaration
        @counter int  
 
set @strCurrDt = (select convert(varchar(8),max(LOAD_DT_ID),112) from MTHLY_DETL)
 
set counter = 1
while counter < 13  --as mentioned earlier by me the > needs to be <
                      which I have changed for this post.
begin
 
Set @strScript = '(SELECT convert(varchar(8),dateadd(month, -' + counter + ', cast( ' + @strCurrDt + ' as datetime)), 112) as DATE)'
 
exec @strScript
 
set counter = counter + 1 
end

Select allOpen in new window

This doesn't change the value in the @strCurrDt with the iterations.Tthe  EXEC@strScript  will just return whatever the select statement inside the dynamic query will return with no effect in the outside code whatsoever.

If the author only wanted to return those values trough the iteration, which again doesn't make any sense at all, why not just run that select without the dynamic query?

I don't think you guys really understand how the dynamic queries work and when and how they should be used.

I understand that.  Again, I am sticking with the orginal structure that the author started with.  The dynamic query will return a date the is modified from its' original value dicrementing the month according to the incremention value.  I also understand that the EXEC @strScript will only return the value @strScript holds.  Again.  I am following the structure the author started with.  You are correct.  It does not make any sense.  The point when you get down to it is that it helped the author identify how to do whatever it truly is that he / she wanted to accomplish.