Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

if exists

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
VBdotnet2005
Asked:
VBdotnet2005
2 Solutions
 
nemws1Commented:
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
 
DOSLoverCommented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now