hardcoded into cursor, help plz
-
Hey all,
I have code that I'm kinda confused to transform into a cursor, make it dynamic I guess.
I have procedure that goes like that(part of it):
select @var1 = .... where n.something = 'one'
select @var2 = .... where n.something = 'two'
select @var3 = .... where n.something = 'three'
select @var4 = .... where n.something = 'four'
if @var1 > 0 begin
select @subvar11 = .... where z.something = 'one'
select @subvar12 = ('blablabla ... @var1 ... bla')
end
if @var2 > 0 begin
select @subvar21 = .... where z.something = 'two'
select @subvar22 = ('blablabla ... @var2 ... bla')
end
if @var3 > 0 begin
select @subvar31 = .... where z.something = 'three'
select @subvar32 = ('blablabla ... @var3 ... bla')
end
if @var4 > 0 begin
select @subvar41 = .... where z.something = 'four'
select @subvar42 = ('blablabla ... @var4 ... bla')
end
Anyone can help how to put it into loop? (cursor)
-
This link might be usefull to you.
Dynamic Search Conditions in T-SQL
What are you trying to do exactly?
-
var1, var2 and etc - they check for number of notes for users and if they are > 0 - it sends out emails to them. The problem is we will have more groups (n.something = 'group1') and we can't jsut hardcode more and more of them into procedure. So we need something dynamic.
-
Provide
Schema, Sample Data, Required Results to help us to help you. I don't undersatnd completely what is needed.
Regards,
gova -
Dito... you don't need dynamic sql to do that.
-
Sounds like you need a cursor where your SQL statement would be:
Select n.something, count(*) as Number
from...
group by n.something
This will get you all groups that have records you are tyring to match.
Your processing loop would be something like:
while...
begin
select @var1=..... where z.something=@something
(do more logic)
end
-
So far I am with the others NO CURSOR NEEDED to accomplish that
* Noel -
I'll get you to agree with me someday soon, I'd guess
. -
Most of the time I do
* Noel -
Ok, how can I use WHILE here? SOrry for my stupidity.
-
You're jumping to conclusion without us answering the question yet, we still need this :
Schema, Sample Data, Required Results
-
var1, var2, var3 and etc will determine whether email needs to be sent or not.
so result is to sent email. I use sp_smtp_email for that or something like it.
sample for one:
....
select @var1 = (select COUNT(DISTINCT LogID) AS LogID
FROM Message_log a
INNER JOIN GroupMember b ON a.UserName = b.Member
WHERE Action = 'sent' AND GroupName = 'group1'
and a.dateEntered in (select max(x.dateEntered)
from Message_Log x
where x.logID = a.logid) )
IF @var1 > 0
BEGIN
SELECT @Email = Email FROM UserInfo a INNER JOIN Leave_Building b ON a.UserName = b.Supervisor INNER JOIN UserAuthentication c ON a.UserName = c.UserName WHERE b.Name = 'group1' AND c.AccountIsActive = 1
SELECT @Body = ('blabla ' + @var1 + 'bla')
exec @rc = master.dbo.xp_smtp_sendmail
...
select RC = @rc
SET @Body = ''
END
ELSE PRINT 'Failed to send an Email'
Oh yeah... forget about first select I had in original message... no need in it - only this 2 parts on top.
-
How will you determine how many variables to declare
var1, var2 ... varn
Since you declare the variables already. I don't see a need for a loop to assign values to them.
Provide Schema, SampleData, ExpectedResult and you will see your answer in next minutes
Regards,
gova -
Sorry for bothering everyone =)
I got it to work =) Using CURSOR =)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply