January 31, 2022 at 10:07 pm
I need to create a SQL server stored procedure to insert records with an ID column that is an identity. After the insert if complete, I need to return all the records that were inserted by ID only. I need ALL inserted records. This table has stored data. There is a datetime column, but it's not listed as a column in my stored procedure. Can I do this without a datetime column?
Here are my instructions:
SP to Log data to CallLog Table: this should Insert into the CallLog Table
a. Parameters
1. Accession No
2. Comments
3. User
b. Return
1. RecID for inserted records
Here is the table schema:
USE [Research]
GO
/****** Object: Table [WEB].[CallLog] Script Date: 1/31/2022 4:06:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WEB].[CallLog](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[AccessionNo] [varchar](30) NULL,
[Comments] [varchar](max) NULL,
[CreateDateTime] [datetime] NOT NULL,
[CreateUser] [varchar](150) NOT NULL,
[LastUpdateDateTime] [datetime] NOT NULL,
[LastUpdateUser] [varchar](150) NOT NULL,
CONSTRAINT [PK_CallLog] PRIMARY KEY CLUSTERED
(
[RecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [WEB].[CallLog] ADD CONSTRAINT [DF_CallLog_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime]
GO
ALTER TABLE [WEB].[CallLog] ADD CONSTRAINT [DF_CallLog_LastUpdateDateTime] DEFAULT (getdate()) FOR [LastUpdateDateTime]
GO
Here is the sp to insert, but I don't know where to start to get a list of everything that was inserted by ID. I am not inserting into a #tmp table that gets dropped everytime I add data. If I can't meet this r
CREATE PROCEDURE [WEB].[sp_InsertCasesintoCallLog]
@Accession_no varchar(30) = '',
@Comments varchar(max) = '',
@User varchar(15) = '',
AS
/*************** 01/31/2022 - Created by Asha Richardson - Stored Procedure used to insert cases to WEB.CallLog
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************/
--exec WEB.sp_GetCasesfromCallLog @StartDate = '01/01/2022', @EndDate = '01/30/2022'
BEGIN
SET NOCOUNT ON;
INSERT INTO [WEB].[CallLog]
([AccessionNo]
,[Comments]
,[CreateUser])
VALUES
(@Accession_no
,@Comments
,@user)
ENDS
January 31, 2022 at 10:51 pm
Since sp_InsertCasesintoCallLog is only inserting one row at a time, you can just use SCOPE_IDENTITY() to get the inserted RecID, and then use that in the where clause for the select to return the inserted row.
If you want to create a stored procedure that inserts multiple rows at once, then you'll need to use OUTPUT to push the inserted RecIDs into a table variable or temp table, and then join that to CallLog to return all the inserted CallLogs
USE [Research]
GO
/****** Object: Table [WEB].[CallLog] Script Date: 1/31/2022 4:06:12 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [WEB].[CallLog](
[RecID] [bigint] IDENTITY(1,1) NOT NULL,
[AccessionNo] [varchar](30) NULL,
[Comments] [varchar](max) NULL,
[CreateDateTime] [datetime] NOT NULL,
[CreateUser] [varchar](150) NOT NULL,
[LastUpdateDateTime] [datetime] NOT NULL,
[LastUpdateUser] [varchar](150) NOT NULL,
CONSTRAINT [PK_CallLog] PRIMARY KEY CLUSTERED
(
[RecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [WEB].[CallLog] ADD CONSTRAINT [DF_CallLog_CreateDateTime] DEFAULT (getdate()) FOR [CreateDateTime]
GO
ALTER TABLE [WEB].[CallLog] ADD CONSTRAINT [DF_CallLog_LastUpdateDateTime] DEFAULT (getdate()) FOR [LastUpdateDateTime]
GO
--Here is the sp to insert, but I don't know where to start to get a list of everything that was inserted by ID. I am not inserting into a #tmp table that gets dropped everytime I add data. If I can't meet this r
CREATE PROCEDURE [WEB].[sp_InsertCasesintoCallLog]
@Accession_no varchar(30) = '',
@Comments varchar(max) = '',
@User varchar(15) = '',
AS
/*************** 01/31/2022 - Created by Asha Richardson - Stored Procedure used to insert cases to WEB.CallLog
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************/--exec WEB.sp_GetCasesfromCallLog @StartDate = '01/01/2022', @EndDate = '01/30/2022'
BEGIN
SET NOCOUNT ON;
DECLARE @RecID BIGINT;
INSERT INTO [WEB].[CallLog]
([AccessionNo]
,[Comments]
,[CreateUser])
VALUES
(@Accession_no
,@Comments
,@user)
SET @RecID = SCOPE_IDENTITY();
SELECT *
FROM [WEB].[CallLog]
WHERE RecID = @RecID;
END -- not ENDS
January 31, 2022 at 10:51 pm
Does that just return the last inserted ID?
January 31, 2022 at 10:52 pm
Here is what I came up with to return a list of all inserted ID's:
USE [Research]
GO
/****** Object: StoredProcedure [WEB].[sp_GetCasesFromPowerPath] Script Date: 1/31/2022 3:09:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [WEB].[sp_InsertCasesintoCallLog]
@Accession_no varchar(30) = '',
@Comments varchar(max) = '',
@User varchar(15) = ''
AS
/*************** 01/31/2022 - Created by Asha Richardson - Stored Procedure used to insert cases to WEB.CallLog
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************
******************************************************************************************************************/
BEGIN
SET NOCOUNT ON;
CREATE TABLE #RowsInserted
(RecID BIGINT);
INSERT INTO [WEB].[CallLog]
([AccessionNo]
,[Comments]
,[CreateUser])
OUTPUT INSERTED.RecID
INTO #RowsInserted
VALUES
(@Accession_no
,@Comments
,@user)
SELECT RecID from #RowsInserted
drop table #RowsInserted
END
GO
January 31, 2022 at 11:08 pm
Yes, OUTPUT is the way you would handle multiple rows.
But that procedure, as written, will never insert more than one row. It is using individual parameters correlating to columns, not a TVP, and INSERT... VALUES() with a single VALUES clause. That, by definition, will only insert one row.
NOTE: If you use "Insert/edit code sample" instead of just copying & pasting your TSQL directly into the edit window, your code will be much more readable. The formatting with extra space between lines make it more difficult to read contiguous code.
February 1, 2022 at 2:19 pm
Thank you for your feedback.
If I wanted to insert multiple rows at one time with this stored procedure how would I modify the updated stored procedure to do that?
February 1, 2022 at 9:24 pm
Thank you for your feedback.
If I wanted to insert multiple rows at one time with this stored procedure how would I modify the updated stored procedure to do that?
Pass in multiple values using a table valued parameter.
Also, the prefix "sp_" is reserved in SQL for system procs. Your code will look in the master database first for this proc, and then the user database.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply