Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help: assigning result set to a variable

Posted on 2008-11-14
31
Medium Priority
?
918 Views
Last Modified: 2012-05-05
Hi,

I am trying to assign a result set to a variable and I am wondering how to go about it.  The code snippet is attached.

I need to reassign the new value of @strCurrDt to @strCurrDt.

Thanks,
JD
Declare @strCurrDt varchar(8),
		@strScript varchar(300)	
 
set @strCurrDt = (select convert(varchar(8),dateadd(month, -1, (cast(convert(varchar(8), max(LOAD_DT_ID)) as datetime))), 112) 
				from TBL_DETL)	
 
Set @strCurrDt = 'SET @strScript = (SELECT 
convert(varchar(8),dateadd(month, -1, (cast( ' + @strCurrDt + ' as datetime))), 112) as DATE)'
 
exec @strCurrDt

Open in new window

0
Comment
Question by:JLD2008
  • 18
  • 6
  • 5
29 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22961653
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.
0
 

Author Comment

by:JLD2008
ID: 22962044
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962743
Again, you declare @strScript, and you use it in exec @strScript statement but you never set it! It's empty!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962748
Again, you declare @strScript, and you use it in exec @strScript statement but you never set it! It's empty!
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962775
What type is column LOAD_DT_ID?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962776
Is it a date of a varchar?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962883
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962886
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

Open in new window

0
 

Author Comment

by:JLD2008
ID: 22962898
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962904
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962916
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22962930
<< 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.
0
 

Author Comment

by:JLD2008
ID: 22962976
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))')

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22963044
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.


0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22963072
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)

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22963080
That above is equivalent to x = x - 1 in your terms.


0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22963091
Sorry the convert code should have been 112 instead of 121.
0
 

Accepted Solution

by:
TroyCrowe earned 1000 total points
ID: 22963159
First let me say that your code is confusing.  You should be able to cut your select statement down to:

set @strCurrDt = (select convert(varchar(8),max(LOAD_DT_ID),112) from MTHLY_DETL)

You should be able to get the same result with it being written as is above.

Next you do not show the increment of your while loop either.

set counter = counter + 1 is needed to increment your loop.

If you are wanting @strCurrDt to decrement 12 times you need this code.
Declare @strCurrDt varchar(8),
	@strScript varchar(300)	
 
-- gets max date from the table
 
set @strCurrDt = (select convert(varchar(8),max(LOAD_DT_ID),112) from MTHLY_DETL)
 
set counter = 1
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, -' + counter + ', cast( ' + @strCurrDt + ' as datetime)), 112) as DATE)'
 
exec @strScript
 
set counter = counter + 1 
end

Open in new window

0
 

Author Comment

by:JLD2008
ID: 22963238
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.
0
 

Expert Comment

by:TroyCrowe
ID: 22963284
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.

0
 

Expert Comment

by:TroyCrowe
ID: 22963354
which post helped?
0
 

Author Comment

by:JLD2008
ID: 22963375
TroyCrowe's helped.  I marked it as the solution.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22964135
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.
0
 

Expert Comment

by:TroyCrowe
ID: 22975797
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22977234
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.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22977283
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.
0
 

Expert Comment

by:TroyCrowe
ID: 22980298
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 22980808
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.
0
 

Expert Comment

by:TroyCrowe
ID: 22981673
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.  
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question