In this article we are going to learn save dynamic query output in a variable.

For that first i need to create a table. So you can find script of creating table.

Table Name : InvoiceLocations

CREATE TABLE [dbo].[InvoiceLocations](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](5) NULL,
    [Value] [nvarchar](50) NULL
) 


INSERT [dbo].[InvoiceLocations] ([Code], [Value]) VALUES (N'BFC', N'BFC')
INSERT [dbo].[InvoiceLocations] ([Code], [Value]) VALUES (N'BRH', N'BRH')
INSERT [dbo].[InvoiceLocations] ([Code], [Value]) VALUES (N'BRP', N'BRP')
INSERT [dbo].[InvoiceLocations] ([Code], [Value]) VALUES (N'BCC', N'BCC')

Let’s write a query for select all records and see the output.

Select * from InvoiceLocations

Output:

Now i am going to write a dynamic query where i just want a row count.

Declare @SQL nvarchar(max)
Declare @Code nvarchar(10)='BFC'
Set @SQL = 'Select Count(*) as TotalCount from InvoiceLocations where Code=@Code'
Execute sp_executesql @SQL, N'@Code nvarchar(10)', @Code

Output:

This works fine for me but what i want that i just want to store that value in a variable so that i can use that output somewhere else. So i have to change the code. Let’s do that and see the output.

Declare @SQL nvarchar(max)
Declare @Code nvarchar(10)='BFC'
Declare @Count int
Set @SQL = 'Select @Count=Count(*) from InvoiceLocations where Code=@Code'
Execute sp_executesql @SQL, N'@Code nvarchar(10), @Count int output', @Code, @Count output
Select @Count as TotalCount

Output:

Hope this article is helpful.