test - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

test

Author

Topic

Alessandro
Starting Member

14 Posts

Posted - 2010-05-06 : 14:53:34

can someone help me?

Write a stored procedure for Microsoft SQL Server that will accomplish the following:
We have a script that runs on the mail server and processes emails that are bounced back
to us (incorrect email address, account inactive, etc.). The script will return the email
address and the reason the email failed (SMTP code). We then need to use the email
address and flag the corresponding account from a database table named 'accounts'. Write
a stored procedure that takes as input the following: 1) email address, and 2) SMTP failure
code. This stored procedure should then return the following result codes: 1 (account was
removed from DB successfully), or 0 (an error occurred and the account was not removed).
Here are the DB specifics:
DB Name: online_db
Affected tables:
Table Fields
accounts id, name, email, status, smtp_code
account_status id, code (where id = 1/code = active),
2/unsubscribed, 3/flagged)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 15:19:02

Show us how far you got first. We are willing to help with homework or tests, however the poster needs to show us some effort first.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Alessandro
Starting Member

14 Posts

Posted - 2010-05-06 : 15:56:31

I think I need use "using Return"
http://www.sqlteam.com/article/stored-procedures-returning-data

but sincerely I did not understand exactly what they are looking for.

the first part it should be:
CREATE PROCEDURE dbo.spReturn

(
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(MAX)
)

AS

but then?
I I understand well
I should retrieve 1 or 0 then
write status = 3 (flagged)

that's it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-06 : 16:15:15

I am not clear on what the teacher wants. Does he/she want you to delete data?

Yes you can use RETURN to return 0/1. You could alternatively use an OUTPUT parameter. I use RETURN to indicate success/failure of the stored procedure, where failure is not necessarily an error. I use OUTPUT parameters to return scalar data back to the client.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 07:52:52

do you think this could be the solution?

ALTER PROCEDURE dbo.sp_remEmail

(
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(50)
)

AS
SET @email_address = (SELECT email
FROM accounts
WHERE (email = @email_address))
IF (@email_address is NULL) AND (@SMTP_failure_code is NULL)
RETURN 0
ELSE
RETURN 1

if not, can you explain me yours?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-07 : 08:19:35

quote:
Originally posted by Alessandro
SET @email_address = (SELECT email
FROM accounts
WHERE (email = @email_address))

I don't think this is what you want to do. This will do one of three things.

If @email_address does not exist in the account table, it will SET @email_address = NULL.

If @email_address exists only once in the account table, it will do nothing. (It will actually SET @email_address equal to itself.)

If @email_address exists more than once in the account table, it will generate an error.

If you simple want to return a 0 when @SMTP_failure_code IS NULL AND when @email_address exists in the Accounts table, otherwise return 1, then do this instead:

ALTER PROCEDURE dbo.sp_remEmail
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(50)
AS
	IF @SMTP_failure_code is NULL 
		AND EXISTS (
			SELECT email
			FROM accounts
			WHERE email = @email_address)
		RETURN 0
	ELSE
		RETURN 1

You should also consider the possibility that @SMTP_failure_code may be 0 if there is no failure, rather than NULL.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 08:20:22

maybe that's is better ...
do you think this could be the solution?
if not, can you explain me yours?

ALTER PROCEDURE dbo.sp_remEmail

(
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(50)
)

AS
SET @email_address = (SELECT email
FROM accounts
WHERE (email = @email_address))
IF ((@email_address is NULL) OR (@email_address = '') OR (@email_address = 0))
AND ((@SMTP_failure_code is NULL) OR (@SMTP_failure_code = '') OR (@SMTP_failure_code = 0))
RETURN 0
ELSE
RETURN 1

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 09:27:21

thank you

so at the end the solution it could be this one?

ALTER PROCEDURE dbo.sp_remEmail
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(50)
AS
IF (@SMTP_failure_code is NULL) or (@SMTP_failure_code = 0) or (@email_address = 0)
AND EXISTS (
SELECT email
FROM accounts
WHERE email = @email_address)
RETURN 0
ELSE
UPDATE accounts
SET status=3, smtp_code=@SMTP_failure_code
WHERE email=@email_address
RETURN 1

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 09:46:51

excuse me, pardon


ALTER PROCEDURE dbo.sp_remEmail
@email_address nvarchar(50),
@SMTP_failure_code nvarchar(50)
AS
	IF (@SMTP_failure_code is NULL) or (@SMTP_failure_code = 0)
		AND EXISTS (
			SELECT email
			FROM accounts
			WHERE email = @email_address)
		RETURN 0
	ELSE
			UPDATE accounts
			SET status=3, smtp_code=@SMTP_failure_code
			WHERE email=@email_address
		RETURN 1

but I have only a problem,
when I put string in @SMTP_failure_code nvarchar(50)
the error is

Running [dbo].[sp_remEmail] ( @email_address = email@email.com, @SMTP_failure_code = gvdfl rgkfglf ).

Conversion failed when converting the nvarchar value 'gvdfl rgkfglf' to data type int.
Conversion failed when converting the nvarchar value 'gvdfl rgkfglf' to data type int.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 
Finished running [dbo].[sp_remEmail].

and my row smtp_code in accounts is nvarchar(50)

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 12:48:45

any help?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-07 : 13:02:56

You are mixing your data types, hence the error.

1. You need to put single quotes around the data values that you are inputting.
2. @SMTP_failure_code = 0 <-- this is why you are getting the error

I can't tell what the instructor wants, otherwise I'd tell you how to fix it. The instruction is badly worded.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Alessandro
Starting Member

14 Posts

Posted - 2010-05-07 : 13:37:49

thank you so much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-07 : 13:38:39

You're welcome, glad to help. Tell your instructor to be more clear next time!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources