Check if exists Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Check if exists Stored Procedure

Author

Topic

maltman
Starting Member

8 Posts

Posted - 2011-02-25 : 17:11:39

Hey all,

I am having an issue with a Stored Procedure that I am running to check if a record exists before entering it into my database. However, it is not doing the checks right. It is adding everything each time I run my application. Here is my Stored Procedure.

Create Proc [dbo].[AddFileInfo]
@FileInfoID int Output,
@FileName nvarchar(50) = NULL,
@FolderPath nvarchar(100) = NULL,
@ServerName nvarchar(50) = NULL,
@DateCreated datetime = NULL,
@DateModified datetime = NULL,
@FileSize int = NULL,
@DateTime datetime,
@FullPath nvarchar(150) = NULL

As
Begin
Set NOCOUNT ON

If @FileInfoID IS NOT NULL

if exists(Select * from FileInfo where FileInfoID = @FileInfoID)

update FileInfo
Set
[FileName] = @FileName,
FolderPath = @FolderPath,
ServerName = @ServerName,
DateCreated = @DateCreated,
DateModified = @DateModified,
FileSize = @FileSize,
[DateTime] = @DateTime,
FullPath = @FullPath

where FileInfoID = @FileInfoID

Else

Insert Into dbo.FileInfo
([FileName],
FolderPath,
ServerName,
DateCreated,
DateModified,
FileSize,
[DateTime],
FullPath)

VALUES
(@FileName,
@FolderPath,
@ServerName,
@DateCreated,
@DateModified,
@FileSize,
@DateTime,
@FullPath)

Set @FileInfoID = SCOPE_IDENTITY()

END

I can not figure out what I am missing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-25 : 17:23:04

Why not use an UPSERT instead?

http://www.sergeyv.com/blog/archive/2010/09/10/sql-server-upsert-equivalent.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

maltman
Starting Member

8 Posts

Posted - 2011-02-25 : 18:01:49

Thank you for the response.

I think I will go back through and use that to clean up my stored procedure.

My issue, though, happened to be in my application.

I created my ID as Output but did not set it as Output in my application. After setting the parameterdirection to Output it worked fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-25 : 18:03:30

Glad you got it sorted out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources