Conditionally inserting from a stored procedure

Question

I am trying to teach myself SQL. I have a web matrix project I am working on to edit and display posts backed by a SQL server Datatabase. A work colleague suggested I use a Stored Procedure to commit the post rather than writing the sql inline.

So far the procedure looks ok but I would like to check if the url slug already exists, and if so return something to say so (The url slug should be unique). I'm struggling with how I am supposed to check before the insert. I have also read that it is bad practice to return from a stored procedure, but I thought it would be a good idea to return something to let the caller know the insert did not go ahead.

Any help would be very much appreciated.

 -- =============================================
 -- Author:     Dean McDonnell
 -- Create date: 05/12/2011
 -- Description:    Commits an article to the database.
 -- =============================================

 CREATE PROCEDURE CommitPost

 @UrlSlug VARCHAR(100),
 @Heading VARCHAR(100),
 @SubHeading VARCHAR(300),
 @Body VARCHAR(MAX)

 AS

 INSERT INTO Posts(UrlSlug, Heading, SubHeading, Body, Timestamp)

 VALUES(@UrlSlug, @Heading, @SubHeading, @Body, GETDATE())

This is what I have so far.

score 2 · Accepted Answer · answered Dec 05 '11 at 21:41

2

 CREATE PROCEDURE CommitPost

 @UrlSlug VARCHAR(100),
 @Heading VARCHAR(100),
 @SubHeading VARCHAR(300),
 @Body VARCHAR(MAX)

 AS
IF NOT EXISTS (SELECT * FROM Posts WHERE UrlSlug = @UrlSlug)

 INSERT INTO Posts(UrlSlug, Heading, SubHeading, Body, Timestamp)
 VALUES(@UrlSlug, @Heading, @SubHeading, @Body, GETDATE())
SELECT @@ROWCOUNT

answered Dec 05 '11 at 21:41

Umut Derbentoğlu

1,146
5
18
39

How about calling RAISERROR instead of making them check the rowcount? – Jonathan Allen Dec 05 '11 at 21:58

okrumnow · Answer 2 · 2011-12-05T21:52:33.263

To check for existance, do a SELECT COUNT like so:

CREATE PROCEDURE CommitPost

 @UrlSlug VARCHAR(100),
 @Heading VARCHAR(100),
 @SubHeading VARCHAR(300),
 @Body VARCHAR(MAX)

 AS

 DECLARE @count INT

 SELECT @count = COUNT(*) FROM  Posts WHERE UrlSlug = @UrlSlug

 IF @count = 0 THEN
 BEGIN

   INSERT INTO Posts(UrlSlug, Heading, SubHeading, Body, Timestamp)
   VALUES(@UrlSlug, @Heading, @SubHeading, @Body, GETDATE())
 END

You may set an unique index on UrlSlug to make the database reject insertions of urls already in the database, but nonetheless you should check before inserting.

If your caller wants to know if the row was inserted, return the @count value. If it's 0 then the line was inserted, else not. I'm not aware of a "bad practice" regarding to return values from a SP. As a SP does not have a result, though, you need to use an out parameter.

score 0 · Answer 3 · edited May 23 '17 at 12:27

If you do just one SQL statement like this insert you could just use paratemerized query i.e. I assume that you are using .NET.

If you want to return values I would suggest that you use a FUNCTION instead of a STORED PROCEDURE. You can return either tables or whatever you want from a function.

There are some limitations though. You can dig a little deeper into the differences to see what is used when. Here's a link that can help you out get started:

Function vs. Stored Procedure in SQL Server

If you want to use stored procedure anyway, you can either return a single row, single column result set, using SELECT, or just use an output parameter.

If you want to do actions depending of whether the column exists or not I would suggest that you look into MERGE statement.That way you would perform only one query to the database instead of two or more(doing SELECT and then INSERT).

There are also other ways to use database access like various ORMs on top of the database in the code that will make your life easier, like LINQ-to-SQL etc. There are a lot of possibilities out there. You need to determine what's best in a given situation.

Conditionally inserting from a stored procedure

3 Answers3