Solved

if exists

Posted on 2013-06-11
2
168 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

685 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