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
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.