Solved

if exists

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 32
How to share SSIS Package? 6 37
Find results from sql within a time span 11 33
Job - date manual 1 22
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

832 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