stephenlecomptejr
asked on
SQL Server Stored procedure to add an existing user if not found in table
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
USE [eCatalist]
GO
/****** Object: StoredProcedure [dbo].[Ensure_User_Exists]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: Name
-- Create date:
-- Description:
-- ==========================
CREATE PROCEDURE [dbo].[Ensure_User_Exists]
-- 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
-- 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