January 20, 2010 at 12:42 pm
I know that the following code is wrong, it gives me the error in the title. Me = rookie. I don't know how to accomplish what I am trying to do!
DECLARE
@aerialStatus varchar(6)
SELECT * INTO p FROM Prospects WHERE ProID = @ProID
IF p.AerialBy is null SET @aerialStatus = 'red'
ELSE SET @aerialStatus = 'green'
How can I pull a row from a table and then check the various fields that are returned?
Thank you,
Wes
January 20, 2010 at 12:47 pm
Try this:
SELECT @aerialStatus = case when p.AerialBy IS_MEMBER null then'red' else 'green' end FROM Prospects p WHERE ProID = @ProID ;
select @aerialStatus;
January 20, 2010 at 12:59 pm
Thank you Lynn, I think that would work, but I'm trying to avoid using multiple SELECT statements. I'm setting a total of 11 status variables by checking approximately 40 fields from the "prospects" table.
Is there any way to save the SELECT results into a local variable so that I can check the various fields quickly?
Here is more of my code so you can get a sense of what I'm trying to do:
DECLARE
@aerialStatus varchar(6),
@phoneStatus varchar(6),
@streetStatus varchar(6),
@followupStatus varchar(6),
@docsStatus varchar(6),
@schedsiteStatus varchar(6),
@siteStatus varchar(6),
@creditStatus varchar(6),
@contractStatus varchar(6),
@preinstallStatus varchar(6),
@installStatus varchar(6)
SELECT * INTO p FROM Prospects WHERE ProID = @ProID
IF p.AerialBy is null SET @aerialStatus = 'red'
ELSE SET @aerialStatus = 'green'
IF p.CallDate is null SET @phoneStatus = 'red'
ELSE IF p.CallApproval is null SET @phoneStatus = 'yellow'
ELSE SET @phoneStatus = 'green'
IF p.StreetBy is null SET @streetStatus = 'red'
ELSE SET @streetStatus = 'green'
IF p.CallEmailSentDate is null SET @followupStatus = 'red'
ELSE SET @followupStatus = 'green'
IF (p.DocBillsLink is null AND p.DocAssocLink is null AND p.DocInsurLink is null AND p.DocAuditLink is null) SET @docsStatus = 'red'
ELSE IF (p.DocBillsSignOff = 'approved' AND (p.DocAssocSignOff = 'approved' or p.CallAssociation = 'n') AND p.DocInsurSignOff = 'approved' AND p.DocAuditSignOff = 'approved') SET @docsStatus = 'green'
ELSE SET @docsStatus = 'yellow'
/* And so on....
...
*/
January 20, 2010 at 1:13 pm
See a pattern below:
SELECT
@aerialStatus = case when p.AerialBy IS_MEMBER null
then 'red'
else 'green'
end,
@docStatus = case when p.DocBillsLink is null
and p.DocAssocLink is null
and p.DocInsurLink is null
and p.DocAuditLink is null
then 'red'
when p.DocBillsSignOff = 'approved'
and (p.DocAssocSignOff = 'approved'
or p.CallAssociation = 'n')
and p.DocInsurSignOff = 'approved'
and p.DocAuditSignOff = 'approved'
then 'green'
else 'yellow'
FROM
Prospects p
WHERE
ProID = @ProID
January 20, 2010 at 1:26 pm
The error is because table names and aliases are scoped at the statement level. In your if statement you referenced p.AerialBy but, since the IF is a separate statement to the select above, the reference to p doesn't exist. There's no table p defined anywhere within the IF statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2010 at 2:18 pm
Thank you Lynn, that worked superbly. "IS_MEMBER null" caused an error (An expression of non-boolean type specified in a context where a condition is expected), but when I replaced it with "is null" it worked fine.
Gila, thanks for the explanation of why my code was wrong, I'm trying to pick this stuff up as quick as I can...
Cheers,
Wes
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply