Insert Into Table with Exec Sproc

Question

Insert Into Table with Exec Sproc

Pat Snow 111

How do you insert data into a table variable or temp table by calling a stored procedure?

Pat Snow 111

CREATE TABLE [dbo].[Address](
	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[AddressLine1] [nvarchar](60) NOT NULL,
	[AddressLine2] [nvarchar](60) NULL,
	[City] [nvarchar](30) NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Address] ON 
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (1, N'1970 Napa Ct.', NULL, N'Bothell', 79, N'9aadcb0d-36cf-483f-84d8-585c2d4ec6e9', CAST(N'2007-12-04T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (2, N'9833 Mt. Dias Blv.', NULL, N'Bothell', 79, N'32a54b9e-e034-4bfb-b573-a71cde60d8c0', CAST(N'2008-11-30T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (3, N'7484 Roundtree Drive', NULL, N'Bothell', 79, N'4c506923-6d1b-452c-a07c-baa6f5b142a4', CAST(N'2013-03-07T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (4, N'9539 Glenside Dr', NULL, N'Bothell', 79, N'e5946c78-4bcc-477f-9fa1-cc09de16a880', CAST(N'2009-02-03T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (5, N'1226 Shoe St.', NULL, N'Bothell', 79, N'fbaff937-4a97-4af0-81fd-b849900e9bb0', CAST(N'2008-12-19T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (6, N'1399 Firestone Drive', NULL, N'Bothell', 79, N'febf8191-9804-44c8-877a-33fde94f0075', CAST(N'2009-02-13T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (7, N'5672 Hale Dr.', NULL, N'Bothell', 79, N'0175a174-6c34-4d41-b3c1-4419cd6a0446', CAST(N'2009-12-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (8, N'6387 Scenic Avenue', NULL, N'Bothell', 79, N'3715e813-4dca-49e0-8f1c-31857d21f269', CAST(N'2008-12-17T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (9, N'8713 Yosemite Ct.', NULL, N'Bothell', 79, N'268af621-76d7-4c78-9441-144fd139821a', CAST(N'2012-05-30T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [rowguid], [ModifiedDate]) VALUES (10, N'250 Race Court', NULL, N'Bothell', 79, N'0b6b739d-8eb6-4378-8d55-fe196af34c04', CAST(N'2008-12-02T00:00:00.000' AS DateTime))
GO

Pat Snow 111

CREATE PROCEDURE [dbo].[GetAddress](@City varchar(50),@StateProvinceID int)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

select * 
from [dbo].[Address]
where [City] = @City
or [StateProvinceID] = @StateProvinceID
order by [City],[StateProvinceID]

END

DECLARE @Address TABLE (
	[AddressID] [int],
	[AddressLine1] [nvarchar](60),
	[AddressLine2] [nvarchar](60),
	[City] [nvarchar](30),
	[StateProvinceID] [int],
	[rowguid] [uniqueidentifier],
	[ModifiedDate] [datetime]
) 
GO

DECLARE @sql VARCHAR(1000) = 'exec [dbo].[GetAddress] @City = ''Atlanta'',@StateProvinceID = 80'
insert @tbl
exec(@sql)

Pat Snow 111 Reputation points

2023-05-02T23:57:30.73+00:00

CREATE PROCEDURE [dbo].[GetAddress](@City varchar(50),@StateProvinceID int) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; select * from [dbo].[Address] where [City] = @City or [StateProvinceID] = @StateProvinceID order by [City],[StateProvinceID] END DECLARE @Address TABLE ( [AddressID] [int], [AddressLine1] [nvarchar](60), [AddressLine2] [nvarchar](60), [City] [nvarchar](30), [StateProvinceID] [int], [rowguid] [uniqueidentifier], [ModifiedDate] [datetime] ) GO DECLARE @sql VARCHAR(1000) = 'exec [dbo].[GetAddress] @City = ''Atlanta'',@StateProvinceID = 80' insert @tbl exec(@sql)

Answer 1

Naomi 7,361

This should work fine:

insert @Address exec [dbo].[GetAddress] @City = 'Atlanta',@StateProvinceID = 80  SELECT * FROM @Address

Pat Snow 111

DECLARE @Address TABLE (
	[AddressID] [int],
	[AddressLine1] [nvarchar](60),
	[AddressLine2] [nvarchar](60),
	[City] [nvarchar](30),
	[StateProvinceID] [int],
	[rowguid] [uniqueidentifier],
	[ModifiedDate] [datetime]
) 
GO
insert @Address exec [dbo].[GetAddress] @City = 'Atlanta',@StateProvinceID = 80  
SELECT * FROM @Address

Msg 1087, Level 15, State 2, Line 11

Must declare the table variable "@Address".

Msg 1087, Level 15, State 2, Line 12

Must declare the table variable "@Address".

PercyTang-MSFT 11,876 Reputation points Microsoft Vendor

2023-05-03T01:24:54.4666667+00:00

Hi @Pat Snow

Delete 'GO'. The script seems to work.
Pat Snow 111 Reputation points

2023-05-03T11:12:14.6266667+00:00

Error: INSERT EXEC statement cannot be nested

Maybe because it's Azure?
Erland Sommarskog 92,956 Reputation points

2023-05-03T22:06:19.9833333+00:00

No, that is a general limitation. You can only do INSERT-EXEC in one level.

Although, it's a little strange that you would get this error, since the procedure you posted did not have INSERT-EXEC. Then again, we don't know what you are running at this point.

Insert Into Table with Exec Sproc

0 additional answers

Activity