How to retrieve a declared variable in sql server

Question

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);

Variables are not stored because they are not persistent. That means if you want to do something more permanent then you need to do it in your code. — Sean Lange, Feb 25 '16 at 20:34
You're not creating a variable, you're generating a result set. `@p_NumberOfRows` is a variable, but it has exactly one value. Here's how to export to CSV for a one-time event: http://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008. To do it in code for repeatability: http://dba.stackexchange.com/questions/23566/writing-select-result-to-a-csv-file — Tom H, Feb 25 '16 at 20:37

score 1 · Accepted Answer · answered Feb 25 '16 at 20:42

1

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.

answered Feb 25 '16 at 20:42

HLGEM

94,695
15
113
186

Or you could wrap in a stored procedure and return an output parameter. – David T. Macknet Feb 25 '16 at 22:13
@DavidTMacknet, yes that too, just don't put it into a return parameter as those are really mean for error codes. – HLGEM Feb 25 '16 at 22:49
Absolutely, @hlgem - return parameters are pretty much evil, in my book. – David T. Macknet Feb 26 '16 at 16:03

How to retrieve a declared variable in sql server

1 Answers1