Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Stored procedure to add an existing user if not found in table

Posted on 2009-05-19
4
Medium Priority
?
200 Views
Last Modified: 2012-05-07
I have the following stored procedure called sp_Ensure_User_Exists and I want to alter it to be smart enough to know that if there is no information for the SELECT statement - SELECT UserID FROM Users WHERE [UserID] = @sUser, to go ahead and INSERT the sUser if it doesn't exist.

Thus there will always be a record shown for the stored procedure.  I do not know what SYNTAX I need to alter the stored procedure.

Thanks in advance.  EE has always been good to me and this is my first time learning stored procedures!

Stephen
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE sp_Ensure_User_Exists( @sUser varchar(20) )
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT UserID FROM Users WHERE [UserID] = @sUser
END
GO

Open in new window

0
Comment
Question by:stephenlecomptejr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24425667
CREATE PROCEDURE sp_Ensure_User_Exists( @sUser varchar(20) )
      -- Add the parameters for the stored procedure here
     
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      INSERT INTO Users
      SELECT @sUser, otherValues
      WHERE NOT EXISTS (  SELECT NULL  FROM Users WHERE [UserID] = @sUser )
END
GO
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 24425764
aneeshattinagal,

I get an error on this line item with the highlight on otherValues-

SELECT @sUser, otherValues

Msg 207, Level 16, State 1, Procedure sp_Ensure_User_Exists, Line 18
Invalid column name 'otherValues'.
Msg 213, Level 16, State 1, Procedure sp_Ensure_User_Exists, Line 17
Column name or number of supplied values does not match table definition.

Please forgive me because I'm completely new to SQL Server and TSQL - so I know that I should change it to something else besides otherValues....but I'm not sure what...

If you had to create the table from scratch it would be as the following CREATE script as listed below -
 
But as you can see the only value I really need to insert is UserID - so how I can line item SELECT @sUser, otherValues into something the computer will agree with?


CREATE TABLE [dbo].[Users](
      [UserID] [varchar](15) NOT NULL,
      [FirstName] [varchar](50) NULL,
      [LastName] [varchar](50) NULL,
      [Usr_email] [varchar](100) NULL,
      [Usr_cell] [varchar](50) NULL,
      [Preferred_Form] [numeric](18, 0) NULL,
      [LastFormUsed] [numeric](18, 0) NULL,
      [DfaultScreenInd] [numeric](18, 0) NULL,
      [Preferred_Proj] [numeric](18, 0) NULL,
      [Usr_Type] [numeric](18, 0) NULL,
      [RoleID] [numeric](18, 0) NULL,
      [Usr_Pricing] [bit] NULL,
      [UserUniqID] [numeric](18, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24425849
You have to enter the values for the other fields there,
for example if you have a field named UserPassword,the sp should look like

CREATE PROCEDURE sp_Ensure_User_Exists(  ----------------------------------its not a good practice to prefix the sps with 'sp_'  which stands for system procedures
@sUser varchar(20)
,@UserPassword  varchar (50)  -----------------------------------------------------check this
 )
      -- Add the parameters for the stored procedure here
     
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
      INSERT INTO Users (UserID, UserPassword)  -----------------------------------------
      SELECT @sUser,@UserPassword  ------------------------------------------------------
      WHERE NOT EXISTS (  SELECT NULL  FROM Users WHERE [UserID] = @sUser )
END
GO
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 24437200
Why not do it like this?

USE [eCatalist]
GO

/****** Object:  StoredProcedure [dbo].[Ensure_User_Exists]    Script Date: 05/20/2009 18:36:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:            Name
-- Create date:
-- Description:      
-- =============================================
CREATE PROCEDURE [dbo].[Ensure_User_Exists]( @sUser varchar(20))
      -- Add the parameters for the stored procedure here
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      
            
    -- Insert statements for procedure here
      -- Insert statements for procedure here
      IF NOT EXISTS( Select UserID from Users where UserID =@sUser)
      BEGIN
            INSERT INTO Users (UserID,RoleID)
            VALUES(@sUser,4 )
      END  
     
      SELECT UserID, FirstName, LastName, Usr_email, Usr_cell, Preferred_Form, LastFormUsed, DfaultScreenInd, Preferred_Proj, Usr_Type, RoleID, Usr_Pricing
      FROM Users WHERE [UserID] = @sUser

END
GO



0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach 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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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