Solved

if exists

Posted on 2013-06-11
2
164 Views
Last Modified: 2013-06-13
I want to check if SID exists in table sms_messages first, if it does not, I want to insert my data into sms_messages. I also want a status to say, if 1, it is inserted to a table, if 0 then no. I only want the result to return 0 or 1.


create procedure Insert_msg

@sid              varbinary(50),
@date_received      smalldatetime,
@direction      char(1),
@send_from      varchar(14),
@status              varchar(1),
@body            varchar(164)

as




if not exists
(select [sid] from  sms_messages where sid = @sid)

    insert into sms_messages (sid, date_received, direction, sent_from, [status], body) values
    (@sid, date_received, direction, sent_from, @status, @body)

    status = 1

else

   status = 0
0
Comment
Question by:VBdotnet2005
2 Comments
 
LVL 23

Accepted Solution

by:
nemws1 earned 250 total points
ID: 39239394
Here's the new proc.  I added a new parameter, @inserted, that is an OUTPUT variable.  Pass in another variable (of TYPE int) when you call this stored procedure and that var will have a zero or 1 in it after the procedure returns.

To do compound statements with IF..ELSE, use BEGIN..END blocks.

CREATE PROCEDURE Insert_msg @sid VARBINARY(50)
	, @date_received SMALLDATETIME
	, @direction CHAR(1)
	, @send_from VARCHAR(14)
	, @status VARCHAR(1)
	, @body VARCHAR(164)
	, @inserted INT OUTPUT
AS
BEGIN
	SET @inserted = 0;
	
	IF NOT EXISTS (
			SELECT TOP 1 [sid]
			FROM sms_messages
			WHERE sid = @sid
			)
	BEGIN
		INSERT INTO sms_messages (
			sid
			, date_received
			, direction
			, sent_from
			, [status]
			, body
			)
		VALUES (
			@sid
			, date_received
			, direction
			, sent_from
			, @status
			, @body
			);
			
		SET @inserted = 1
	END
	ELSE
	BEGIN
		SET @inserted = 0
	END
END
GO

Open in new window

0
 
LVL 5

Assisted Solution

by:DOSLover
DOSLover earned 250 total points
ID: 39239879
Here is another way of doing it using system variabbles for error and inserted-record-count:
It sets return code of 'E' in case of any error in insert statement.
CREATE PROCEDURE Insert_msg 
	( @sid VARBINARY(50)
	, @date_received SMALLDATETIME
	, @direction CHAR(1)
	, @send_from VARCHAR(14)
	, @status VARCHAR(1)
	, @body VARCHAR(164) 
	, @returnCode varchar(1) OUTPUT
	)
AS
BEGIN

DECLARE @InsertCountVar INT;
DECLARE @ErrorVar INT;

    insert into sms_messages (sid, date_received, direction, sent_from, status, body) 
	       values (@sid, @date_received, @direction, @sent_from, @status, @body)
	where not exists (select sid from  sms_messages where sid = @sid);

	SELECT @ErrorVar = @@ERROR, @InsertCountVar = @@ROWCOUNT;

	IF @ErrorVar <> 0
		BEGIN
			SET @returnCode = 'E'
		END
	else
		IF @InsertCountVar <> 0
			BEGIN
				SET @returnCode = '1'
			END
		ELSE
			BEGIN
				SET @returnCode = '0'
			END
...
...
...

END

Open in new window

0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now