syntax

Question

syntax

smknox

Hall of Fame

Points: 3275
More actions
September 3, 2007 at 12:46 pm

#176373

I have the following syntax within an SP:
Declare @CompleteHP as varchar(5000)
SELECT
t2.PE1
, t2.PE2
, t2.ProgIntro
FROM PClinical t2
WHERE t2.PartyID=36008
SET @CompleteHP= t2.ProgIntro
I get an error that t2 is not recognized as an alias. What am I missing in my syntax?
THanks,
Sam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can't use a select to return data and set a variable at the same time. You must do this in 2 steps.

Also you must consider that the select can return more than 1 line and that you'll never know which line will be saved in the variable (it's always the last one, but unless you use order by, then there's no way to garantee that).

smknox Hall of Fame Points: 3275 More actions · Answer 2

Thanks,

Next question is if the return data is null, how do I set the declared variable to '' (empty string)?

Here's what I have so far that's not working:

SELECT @FHComm=p.TextBody

FROM PartyText p

WHERE DataPointID=167 And p.PartyID=@PartyID

CASE WHEN @FHComm Is Null THEN ''

ELSE @FHComm END

I have a whole list of variables that I am concantenating to make a complete note. But nothing shows up if even a single piece of data is missing (null).