syntax for a function | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

syntax for a function

Hello all, I am looking for syntax help on a function. I have a select that returns 2 fields and usually 1-6ish rows. What i want to do is have a variable that will change its value based on whats in each fields. Kind of like a flag. The function in the end returns a char(1) here’s my code:
declare @Per char(1)
set @Per = ‘N’ –using hardcoded numbers at the moment for testing purposes eg:47545 will be a param
SELECT case when 47545 = vicid then @Per = ‘V’ when 47545 = perid then @Per = ‘P’ –i didn’t include the rest of it because it returns the apropriate rows i just need help –with transforming the returned data.
to give an example of what the code returned before i changed the select to try to change the variable this is what i would expect: vicid perid
32432 43333
24234 43333
52322 43333 so presumably the code should step through the result like reading a book from left to right then top to bottom. So it will take a look at the first vicid and if it eqauls the sent param then assign @per a ‘V’ if it doesn’t then do nothing. then it moves on to the perid field and does the same check only if it equals it gets a ‘P’. Assuming none of the values match it will keep an ‘N’ value. btw vicid will usually always be unique and typically perid will always be the same value. It is expected to overwrite itself if the same id is in there twice. so my question is how do i code the select statment to set the variable? I have been struggling with it for a bit now. thank you in advance
ps. never get one of those damn mousepad picture things. I wrote up this email in its entirety and the mouse out of a complete fluke jumped to the red X at top right just as i was clicking to post. My wife got it for me, its our wedding picture, sad but those pads are completely useless with optical mice.
ok so this is what i have so far, it is giving results but not behaving the way i want it to. Here is my code:
declare @Per char(1)
set @Per = ‘N’
select @per = case when 47548 = vicid then ‘V’ when 47548 = perid then ‘P’ end basically only the last row is counting. The variable isn’t holding previous values. So say row 2 assigns a ‘V’ to @per and row 3 has no matches it will go blank. Here is the behaviour i am looking for: The variable starts with the value ‘N’. If nothing matches i should still have an ‘N’ at the end of the list. If at any point in the check it the number matches an ID the variable will be reasigned the value of either ‘V’ or ‘P’. The variable will keep that value until another ‘V’ or ‘P’ come along to change it. It can never go back to ‘N’ after being a ‘V’ or ‘P’. All help very appreciated
Try the following… declare @Per char(1)
select @per = case
when 47548 = vicid then ‘V’
when 47548 = perid then ‘P’
else ‘N’
I have resolved my issue. Thank you for your time