CODEWITHSUNDEEP
sqlsql-server
oivemaria
oivemaria

Reputation: 463

How to retrieve a declared variable in sql server

Using the code below (which I found here), a variable with 20 million rows is being created. Where do I find the stored variable. I was able to locate the tables I've created but this particular variable isn't visible.

My end goal is to export this variable and its values in a CSV format. How do I make that happen.

Declare @p_NumberOfRows Bigint

--We need 20 million rows

Select @p_NumberOfRows=20000000;

With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C
From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n
From Expand
)
Select n from Nums Where n<=@p_NumberOfRows

--Remove Maximum Recursion level constraint

OPTION (MaxRecursion 0);

Upvotes: 0

Views: 173

Answers (1)

HLGEM
HLGEM

Reputation: 96552

well depending on how you are handling the result set in your application that is going to create the csv, there seem to be several ways to go.

First add the variable count to every row returned in the select

Select n, @p_NumberOfRows as NumberOfRows from Nums Where n<=@p_NumberOfRows

Or you could return two results sets, one with the data and one with the number of rows variable

Select n from Nums Where n<=@p_NumberOfRows
select @p_NumberOfRows as NumberOfRows

Or you could store the number of rows variable in a table with a run_Instance_Id and a date and then pull from that. This is only if you need to see the number of rows at some future date that was sent on a particular run.

Upvotes: 1

Related Questions