asked on

Converting MS Access Query to Stored Procedure

I am working on converting some of my queries in MS Access to stored procedures in SQL Server 2014 but I can't seem to figure out how to convert this part of my query. Can anyone help me figure this one out?

MS Access Query: IIf(IsNull([MaterialRoundness])," ",Format([MaterialRoundness],".0000##")) AS MaterialRoundness1

How would I write that in a stored procedure. That is just one part of my stored procedure that I cannot figure out. So if the [MaterialRoundness] IsNull then I want to leave 2 blank spaces, otherwise load the value that is in the [MaterialRoundness] field.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

Thanks Dale! Appreciate the options!

Lawrence Salvucci

ASKER

Dale....I forgot about the formatting part. What can I do to keep that formatting the same when I add this to a stored procedure? That is critical for when the data is written to the table via this proc. It needs to be .0000## in the proc to load the value correctly.

Jim Horn

As an aside if you're doing a lot of Access SQL to SQL Server T-SQL conversion work I wrote this article which will help .. Migrating your Access Queries to SQL Server Transact-SQL.

The article is five years old so if you come across anything with newer versions that I haven't covered please comment in the article so I can make it better.

Good luck.

Dale Fye

Lawrence,

Personally, I dislike trying to format data in the data layer, and prefer to do that in the form or report which is actually presenting the data.

Dale