Stored procedure question 1

[orion2347]

Hi!!

I've got this problem with stored procedures in SQL Server.
I have this table that has 3 fields. The 3rd field is supposed to store the first letter of the 2 other fields. How do I go about doing that using a stored procedure? Also I've never seen IF statements in stored procedures so I'm assuming it can't be done. Is this right?

 

[bperry]

IF statements can definitely be used inside SPs. A litle example:

IF @@type = 3
do some code
ELSE
BEGIN
do this
do that
END
---------------
So, re: your other issue, it sounds like you might to do a test for that, it that right? I could show you a few ideas for looking at that. It sort of depends on what you want to do:

Report the fields where Field3 is wrong.

Select Field1, Field2, Field3
From YourTable
Where LEFT(Field3,2) <> LEFT(Field1,1) + LEFT(Field2,1)


Maybe count how many Field3 values are wrong:

Select COUNT(*) as Fld3Cnt
from YourTable
Where LEFT(Field3,2) <> LEFT(Field1,1) + LEFT(Field2,1)


or perhaps Update Field3:

Update YourTable
Set Field3 = LEFT(Field1,1) + LEFT(Field2,1)
------------------

Does any of this help what you needed to know?

bperry

 

[orion2347]

Hi!! Thanks for your speedy reply. Well, I was thinking of implementing a SP to pull out the initials of a person. The 3 fields are FirstName, Surname and Initial. I want to populate the Initial field with the first character of the other 2 fields. Now the catch is that some surnames start with Mc (McGraw) or O' (O'Riley). In these cases, I want to take the 3rd character instead.

My idea was to use an Update, coupled with some IF conditions. Would that work? Or must I SELECT the 2 name fields, do the comparison and then update the table.

Eg. SELECT firstname, surname...
IF LEFT(surname, 2) = &quot;O'&quot; OR LEFT(surname, 2) = &quot;Mc&quot;
THEN
BEGIN
UPDATE YourTable
Set Initial = LEFT(firstname,1) +
SUBSTRING(surname,2,1)
END
ELSE
BEGIN
UPDATE YourTable
Set Initial = LEFT(firstname,1) +
LEFT(surname,1)
END
END IF

Haha... I'm lousy at SQL, but we all have to start somewhere, right?
Orion

 

[bperry]

Hi,
I am tied up right now, but can look at again later today for you.

 

[cmmrfrds]

You could use a CASE statement and do it all in one sql statement. An illustration.

UPDATE YourTable
Set Initial = CASE
When LEFT(surname, 2) = &quot;O'&quot;
THEN LEFT(firstname,1) + SUBSTRING(surname,2,1)
When LEFT(surname, 2) = &quot;MC&quot;
THEN LEFT(firstname,1) + SUBSTRING(surname,2,1)
ELSE
LEFT(firstname,1) + LEFT(surname,1)
END
WHERE your criteria

 

[bperry]

Hi again.
I like cmmrfrds' approach. Nice job.

If I could mention these few small repairs:

- All those double quotes won't work (you were probably thinking ASP?)

- Around where I live, we have lots of MCGraws and MACgraws, so I added an extra test for that situation (delete if you don't agree)

- Also around here, we have some folks with surname D'Arcy, so it might be better to check for just a ' in the second postion, rather than O'. CHAR(39) = '

Here is cmmrfrd's code, with those minor changes:
[tt]
UPDATE YourTable
Set Initial =
CASE
When SUBSTRING(surname,2,1) = CHAR(39)
THEN LEFT(firstname,1) + SUBSTRING(surname,3,1)
When LEFT(surname,2) = 'MC'
THEN LEFT(firstname,1) + SUBSTRING(surname,3,1)
When LEFT(surname,3) = 'MAC'
THEN LEFT(firstname,1) + SUBSTRING(surname,4,1)
ELSE
LEFT(firstname,1) + LEFT(surname,1)
END
WHERE your criteria here if any
[/tt]
--------------
bperry

 

[orion2347]

Thanks a lot guys!! I'll check back again if I have any other problems.

 

[cmmrfrds]

Thanks for cleaning up the code bperry. On the &quot;, it was a short term memory thing. I noticed at first glance, but forgot to change before finishing.

 

[dtqui]

Hi,

is there another approach to this like using if else instead of case? I've tried the codes below but it gave error.

Update myTable
Set initial =
IF SUBSTRING(surname,2,1) = CHAR(39)
LEFT(firstname,1) + SUBSTRING(surname,3,1)
ELSE
IF LEFT(surname,2) = 'MC'
LEFT(firstname,1) + SUBSTRING(surname,3,1)
ELSE
LEFT(firstname,1) + LEFT(surname,1)

 

[bperry]

There is an IF statment in T-SQL, but you can't use it like that as a predicate in a Select statement. (In a Select, you must use the CASE structure).

You would use IF when you are writing a batch script or a stored procedure, in a way similar to other languages, as shown in my first response. Strangely enough, you don't use CASE in scripts, which I agree can be confusing.

 

[tlbroadbent]

IF is used to control execution in SQL. CASE is a function that returns a value. Thus CASE can be used in the body of a SELECT, INSERT, DELETE, or UPDATE statement while IF is used to control the execution of statements. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.