How to get a variable into my query from a WITH statement

Question

Post reply

How to get a variable into my query from a WITH statement

peter2501

SSChasing Mays

Points: 601
More actions
May 10, 2013 at 7:31 am

#273840

Hello,
I have a simple question, but cannot get to a solution yet.
The following query is fine. this gives me result
---------------
WITH naam AS
(
SELECT ROW_NUMBER() OVER(ORDER BY APPNAME) as RowNum, APPNAME
FROM hsx_datasources
)
SELECT appname FROM Naam where rownum=1
---------------
But I want to use the info I get for a variable.
The query I want is something like this. (where the #tblsize is already defined.)
The bold part is the point where I am stuck. what do I do wrong, or should I create another temp table to hold the data.
----------------
Declare @appnaam varchar(30);
Declare @appnaam2 varchar(30);
WITH Naam AS
(
SELECT ROW_NUMBER() OVER(ORDER BY APPNAME) as RowNum, APPNAME
FROM hsx_datasources
)
SET @appnaam= (SELECT appname FROM Naam where rownum=1)
SET @appnaam2=@appnaam +'_%'
SELECT @appnaam AS application,
SUM(Rows) as TotalRows,
SUM(Reserved) as Reserved,
SUM(Data) as Data,
SUM(index_size) as Index_Size,
SUM(unused) as Unused
FROM #tblSize where Name like @appnaam2
---------------
The data from hsx_datasources is something like this, but I want to have each table entry into a variable so I can run the script in one go without first checking what the info in this table is. therefor I first get the row in front of it.
appname
---------------
Comma
SNL
SNLCop30
SNLCop36
SNLCop39
SNLCop40
SNLCop4b
SNLTrain
SNLUAT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's just a syntax issue, where you are doubling up the SET and SELECT unnecessarily;

I think you want to simply assign the variable directly in the SELECT:

SELECT

@appnaam= appname

FROM Naam

where rownum=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

peter2501 SSChasing Mays Points: 601 More actions · Answer 2

Ah thanks,

That worked.

Just was too focused into getting a SET statement, but doing it directly in a SELECT is way easier.

Peter