USE [wce_sqlexpress]
GO
/****** Object:  StoredProcedure [dbo].[history_proc]    Script Date: 11/23/2008 13:20:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[history_proc]

@notes text,
@subject varchar(50),
@uniqueid varchar(25),
@CreationTime datetime,
@Creator varchar(50),
@contact varchar(50), 
@emailaddress varchar(50), 
@etable varchar(25),
@utable varchar(25),
@htype varchar(20)

AS

declare @entityid varchar(16)
declare @recordmanager varchar(16)
declare @ContactCount int

SET NOCOUNT ON

SET @ContactCount = (select count(*) from wce_contact where emailaddress = @emailaddress)

IF @ContactCount <> 1
BEGIN
	RAISERROR  (50002,16,1)
END


ELSE
BEGIN

set @entityid = (select UNIQUEID from wce_contact where emailaddress = @emailaddress)
set @recordmanager = (select wce_sys..wces_users.UNIQUEID from wce_sys..wces_users where wce_sys..wces_users.WCE_UID = @Creator and wce_sys..wces_users.WCE_ALIAS = 'wce_wirede')

insert into wce_linkto (LEntityID, LETableName, LUniqueID, LUTableName)

VALUES (@entityid, @etable, @uniqueid, @utable)

SET NOCOUNT OFF
 
insert into wce_history (UNIQUEID, RECORDEDTIME, RECORDEDFOR, SUBJECT, NOTES, HTYPE)

VALUES (@uniqueid, @CreationTime, @recordmanager, @subject, @notes, @htype)

	
END

quote:

---

Originally posted by mshsilver

Hi,

I am having an issue with printing an error message if an email address doesn't exist in the database. I'm integrating a CRM system with a 3rd party Outlook tool. I know the error is in the stored procedure cause the outlook tool is well tried and tested and if the email address does exist the integration works fine. If someone could just check that i haven't made a silly school boy error in my code, that would be much appreciated.

I am using SQL Express

USE [wce_sqlexpress]
GO
/****** Object:  StoredProcedure [dbo].[history_proc]    Script Date: 11/23/2008 13:20:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[history_proc]

@notes text,
@subject varchar(50),
@uniqueid varchar(25),
@CreationTime datetime,
@Creator varchar(50),
@contact varchar(50), 
@emailaddress varchar(50), 
@etable varchar(25),
@utable varchar(25),
@htype varchar(20)

AS

declare @entityid varchar(16)
declare @recordmanager varchar(16)
declare @ContactCount int

SET NOCOUNT ON

SET @ContactCount = (select count(*) from wce_contact where emailaddress = @emailaddress)

IF @ContactCount <> 1
BEGIN
	RAISERROR  ('Desired Error Message',10,1)
END


ELSE
BEGIN

set @entityid = (select UNIQUEID from wce_contact where emailaddress = @emailaddress)
set @recordmanager = (select wce_sys..wces_users.UNIQUEID from wce_sys..wces_users where wce_sys..wces_users.WCE_UID = @Creator and wce_sys..wces_users.WCE_ALIAS = 'wce_wirede')

insert into wce_linkto (LEntityID, LETableName, LUniqueID, LUTableName)

VALUES (@entityid, @etable, @uniqueid, @utable)

SET NOCOUNT OFF
 
insert into wce_history (UNIQUEID, RECORDEDTIME, RECORDEDFOR, SUBJECT, NOTES, HTYPE)

VALUES (@uniqueid, @CreationTime, @recordmanager, @subject, @notes, @htype)

	
END

---