[SQL Server] How to get a variable into my query from a WITH statement

---

How to get a variable into my query from a WITH statement Posted: 10 May 2013 12:31 AM PDT 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, APPNAMEFROM 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.----------------[b]Declare @appnaam varchar(30);Declare @appnaam2 varchar(30);WITH Naam AS(SELECT ROW_NUMBER() OVER(ORDER BY APPNAME) as RowNum, APPNAMEFROM hsx_datasources)SET @appnaam= (SELECT appname FROM Naam where rownum=1)[/b]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 UnusedFROM #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---------------CommaSNLSNLCop30SNLCop36SNLCop39SNLCop40SNLCop4bSNLTrainSNLUAT

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610