T-SQL : Fetch Data Into Variables

This is tested on MSSQL.

Assuming you have a database called 'TestDBTest'

And a table called 'TBL_Employee'

USE [TestDBTest]
GO
/****** Object:  Table [dbo].[TBL_Employee]    Script Date: 10/25/2009 10:45:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_Employee](
 [recID] [bigint] NOT NULL,
 [cUsername] [varchar](50) NULL,
 [cNationalID] [varchar](50) NULL,
 CONSTRAINT [PK_TBL_Employee] PRIMARY KEY CLUSTERED 
(
 [recID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Use the following T-SQL to fetch data into variables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

declare @query nvarchar(max), @mainCursor cursor

set @query = " set @cursor = cursor for SELECT cUserName,cNationalID FROM 
[TestDBTest].[dbo].[TBL_Employee] WHERE deletedDate is null"
+ " open @cursor"

exec sp_executesql @query,N'@cursor cursor output',@mainCursor output

declare @cUserName as varchar(50),@cNationalID as varchar(50)

fetch next from @maincursor into @cUserName,@cNationalID
while (@@fetch_status <> -1) 
BEGIN 

print @cUserName + ':' + @cNationalID

fetch next from @maincursor into @cUserName,@cNationalID
END

You need to use Cursor and SP_Executesql