If Then in Stored Procedure

Transact-SQL
1

Using SQL 2008, can you tell be a better way to do this in a Stored Procedure...

Basically, I am passing in @RAG which is a single char, and I need to set @RAGScore based on the char in @RAG. I know this must be horrible, so can someone tell me the best way of doing this?

Thanks, Phil

 If @RAG = 'R'
 Begin
	Set @RAGScore = 3
 End ELse
	If @RAG = 'A'
	Begin
		Set @RAGScore = 2
	End Else Begin
		If @RAG = 'G'
		Begin
			Set @RAGScore = 1
		End Else Begin
			If @RAG = 'C'
			Begin
				Set @RAGScore = 0
			End ELse Begin
				Set @RAGScore = -1
			End
		End	
	End
end
2

The following is more compact.

SET @RagScore =
	CASE @RAG
		WHEN 'R' THEN 3
		WHEN 'A' THEN 2
		WHEN 'G' THEN 1
		WHEN 'C' THEN 0
		ELSE -1 
	END;
1 Like
3

Much nicer; I nearly got to that version but had my Set @ in the wrong place. Another lesson learnt :smile:

Thanks

Phil

4

You are still writing Pascal or BASIC, but you are you are just using T-SQL to do it. SQL is a declarative language, so we do not use control flow. No loops; no if-then_else.

We have a CASE expression:

SET @rag_score
= CASE @rag
WHEN 'A' THEN 2
WHEN 'G' THEN 1
WHEN 'C' THEN 0
ELSE -1 END;

We also hate local variables, so we would use a text editor to replace your @rag_score with the expression. The optimizer will now be able to use it and you do not have to access the disk.

Get a book on functional languages to get an idea of the differences in the mindset. They run orders of magnitude faster than procedural code. :grinning: