September 23, 2008 at 8:17 am
have this sproc I need to make so if I have a null value to insert a blank space
I migrating from an access database to an sql database, sql database will allow null values
GO
/****** Object: StoredProcedure [dbo].[hotlsp_ContactsInsert] Script Date: 09/23/2008 09:34:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===============================================================================================================
--Description:Updates Contact table and selects the newly updated record.
--
--WHOWHENWHAT
----------------------------------------------------------------------------------------------------------------
--SAC04/21/2008expanded BusinessName field from 30 to 60 and changed IATA field from int to nvarchar(20)
-- ===============================================================================================================
ALTER PROCEDURE [dbo].[hotlsp_ContactsInsert]
(
@EId int,
@Title nvarchar(10),
@LastName nvarchar(30),
@FirstName nvarchar(25),
@Address1 nvarchar(35),
@Address2 nvarchar(35),
@City nvarchar(25),
@State nvarchar(25),
@ZIP nvarchar(25),
@Country nvarchar(25),
@PhoneHome nvarchar(25),
@PhoneWork nvarchar(25),
@PhoneFax nvarchar(25),
@BusinessName nvarchar(60),
@GuestNote ntext,
@HKNote ntext,
@Email nvarchar(75),
@EmailPermission bit,
@MailPermission bit,
@Category int,
@Flag1 bit,
@Flag2 bit,
@Flag3 bit,
@Flag4 bit,
@Flag5 bit,
@Flag6 bit,
@OccasionDate1 datetime,
@OccasionDate2 datetime,
@OccasionDate3 datetime,
@OccasionDate4 datetime,
@Occasion1 int,
@Occasion2 int,
@Occasion3 int,
@Occasion4 int,
@Interest1 int,
@Interest2 int,
@Interest3 int,
@Interest4 int,
@IATA nvarchar(20),
@AgentCommission float,
@Active bit,
@deleted bit
)
AS
SET NOCOUNT OFF;
Declare @CId int
Declare @Venue int
Execute dbo.hotlsp_ContactsVenueNewId
@NewId = @CId OUTPUT,
@Venue = @Venue OUTPUT
/* Put the INSERT and hotlsp_InvoiceHistoryInsert in a transaction so we
* log the proper row version */
INSERT INTO dbo.Contact(CId, EId, Title, LastName, FirstName, Address1, Address2, City, State, ZIP, Country, PhoneHome, PhoneWork, PhoneFax, BusinessName, GuestNote, HKNote, Email,
EmailPermission, MailPermission, Category, Flag1, Flag2, Flag3, Flag4, Flag5, Flag6, OccasionDate1, OccasionDate2, OccasionDate3, OccasionDate4, Occasion1, Occasion2, Occasion3,
Occasion4, Interest1, Interest2, Interest3, Interest4, IATA, AgentCommission, Active, CreatedOn, Servervenue, deleted)
VALUES (@CId, @EId, @Title, @LastName, @FirstName, @Address1, @Address2, @City, @State, @ZIP, @Country, @PhoneHome, @PhoneWork, @PhoneFax, @BusinessName, @GuestNote, @HKNote, @Email,
@EmailPermission, @MailPermission, @Category, @Flag1, @Flag2, @Flag3, @Flag4, @Flag5, @Flag6, @OccasionDate1, @OccasionDate2, @OccasionDate3, @OccasionDate4, @Occasion1, @Occasion2, @Occasion3, @Occasion4, @Interest1, @Interest2, @Interest3, @Interest4,
@IATA, @AgentCommission, @Active, GetDate(), @Venue, @deleted);
SELECT CId, EId, Title, LastName, FirstName, Address1, Address2, City, State, ZIP, Country, PhoneHome, PhoneWork, PhoneFax, BusinessName, GuestNote, HKNote, Email, EmailPermission, MailPermission, Category, Flag1, Flag2, Flag3, Flag4, Flag5, Flag6, OccasionDate1, OccasionDate2, OccasionDate3, OccasionDate4, Occasion1, Occasion2, Occasion3, Occasion4, Interest1, Interest2, Interest3, Interest4,
IATA, AgentCommission, Active, ts, deleted
FROM dbo.Contact
WHERE (CId = @CId)
/* Store this to audit trail */
EXEC dbo.hotlsp_ContactHistoryInsert @CId
September 23, 2008 at 8:25 am
You can Check IsNULL or you could use Case Statement to insert a space when the value is NULL.
-Roy
September 23, 2008 at 8:29 am
Well,
First the simple answer. You can use the coalesce function to test a parameter and provide an aternate value if it is null.
Coalesce(@Address2, '') will yield an empty string if @Address2 is null. So, in the Values clause of the Insert statement, you can apply the function to each of your parameters that are 'nullable'. See Books On Line for more information on the various uses of Coalesce.
I think you have a more fundamental problem you need to address. I understand that you are moving from Access to SQL Server, but in either environment your data model appears to be basically a flat file. The presence of fields similar to Occassion1, Occassion2, ... or Interest1, Interest2, ... etc is the real tip off.
You should learn a bit about normalization and then consider redesigning your data model. You will find that a properly normalized set of tables will give you much more flexibility when retrieving data from the database.
Good luck.
September 23, 2008 at 8:52 am
are there any books on this.
September 23, 2008 at 8:53 am
Any books on normalization
September 23, 2008 at 9:16 am
There are many many books on this topic. It is probably the most fundamental aspect of database design and has drawn a legion of authors, opinions, hacks, heros, and charlatans 🙂
The relational law giver is Dr. EF Codd and any of his writings will be informative. The first prophet is CJ Date and his writings are used in many college level texts. In the 25+ years since these two started it all there have been scores of disciples. Some of my favorites (though I don't always agree with them) are Ken Henderson and Joe Celko.
The titles come and go very quickly and many don't stay in print very long even when they are very good.
I recommend you start with Google looking for Database Normalization, Database design, etc. Wikipedia is also a good start. Once you have familiarized you self with those you should be in a better position to choose a couple of books. Don't be put off by the date of publication. Almost all of the controversies, techniques, and tools, applicable to relational theory and normaliztion were identified many years ago and are still hotly debated by those who like debates.
Good luck.
BTW Joe Celko's "Thinking in Sets" is a bit advanced for a beginner but if you can get through it you will have a whole new world view 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply