In this post, we will see how to save a Stored Procedure result into a temporary table.
We remember that Temporary tables are tables that exist temporarily on SQL Server and, they are used to store result that need to be elaborate.
We start creating a database called TestBlog where, we will add two tables called User and TypeUser, so defined:
[TYPEUSER]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TypeUser](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TypeUser] PRIMARY KEY CLUSTERED
(
[ID] 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]
GO
[USER]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](100) NOT NULL,
[Password] [nvarchar](100) NOT NULL,
[Type] [int] NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] 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]
GO
ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_TypeUser] FOREIGN KEY([Type])
REFERENCES [dbo].[TypeUser] ([ID])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_TypeUser]
GO
Finally, we run some SQL scripts to add data in the tables:
GO
SET IDENTITY_INSERT [dbo].[TypeUser] ON
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (1, N'User')
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (2, N'Reader')
GO
INSERT [dbo].[TypeUser] ([ID], [Description]) VALUES (3, N'Admin')
GO
SET IDENTITY_INSERT [dbo].[TypeUser] OFF
GO
SET IDENTITY_INSERT [dbo].[User] ON
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (1, N'user1@email.com', N'pass123', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (2, N'user2@email.com', N'pass456', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (3, N'user3@email.com', N'pass789', 1)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (4, N'reader1@email.com', N'psw111', 2)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (5, N'reader2@email.com', N'psw8u6', 2)
GO
INSERT [dbo].[User] ([ID], [Email], [Password], [Type]) VALUES (6, N'admin1@email.com', N'pas987', 3)
GO
SET IDENTITY_INSERT [dbo].[User] OFF
Now, we will create a Stored Procedure called GetUsersByType to get the list of Users by UserType:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUsersByType]
@TypeID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT A.[ID]
,A.[Email]
,A.[Password]
,B.[Description]
FROM [User] A
join [TypeUser] B on A.Type = B.ID
where A.Type = @TypeID
END
GO
If we run the Stored Procedure, this will be the result:
Now, we create a temporary table called Users, using this SQL command:
create table #Users (ID int, Email nvarchar(100), Password nvarchar(100), UserType nvarchar(50))
Obviously, if we run a select on #Users, this will be the result:
Finally, we will run twice the Stored Procedure passing different TypeIDs and then, we will insert the results in #Users:
insert into #Users EXEC GetUsersByType @TypeID=1
insert into #Users EXEC GetUsersByType @TypeID=2
We have done and now, if we run some selects on #Users, these will be the results:
