quote:

---

Originally posted by madhivanan

You need to use dynamic sql

declare @RunningPeriod int, @sql varchar(8000)
set @RunningPeriod=28

set @sql='select (SUM(ISNULL(Actual_Deb'+cast(@RunningPeriod as varchar(10))+'))) AS GLAmt FROM _bvGLAccountsFinancial
where Master_Sub_Account = '+cast(@MstSubAC as varchar(100))
exec(@sql)


But make sure to read this article before applying it

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by SteveT9182

quote:

---

Originally posted by madhivanan

You need to use dynamic sql

declare @RunningPeriod int, @sql varchar(8000)
set @RunningPeriod=28

set @sql='select (SUM(ISNULL(Actual_Deb'+cast(@RunningPeriod as varchar(10))+'))) AS GLAmt FROM _bvGLAccountsFinancial
where Master_Sub_Account = '+cast(@MstSubAC as varchar(100))
exec(@sql)

But make sure to read this article before applying it
------------------------------------------------------------------
Thank you so much for trying to help!

But when I tried the code you suggested above I got :
Incorrect syntax near '+cast(@RunningPeriod as varchar(10))+'
What am I doing wrong?
I also read the article you suggested, and figure I may have to use sp_executesql somehow, but could not find any example where the writer dynamically accessed an existing column name in the SQL lookup in the way I need to do it - with a variable (such as period number) passed so it can be 'added' to part of the column name.

What I am trying to do is send a call to a stored procedure in SQL2008 from 'client side', i.e. from VBA in Excel 2007, which will return GLAmt for the specified period to Excel so I can put it in the spreadsheet.

Can you show me code to do this?

SteveT9182

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

---

---

-- *** Test Data ***
CREATE TABLE #t
(
	Actual_Deb01 money NOT NULL
	,Actual_Deb02 money NOT NULL
	,Actual_Deb03 money NOT NULL
)
INSERT INTO #t
VALUES (11, 12, 13)
	,(21, 22, 23)
	,(31, 32, 33);
-- *** End Test Data ***

WITH Normalized
AS
(
	SELECT DebStr, Actual_Deb
	FROM
	(
		SELECT Actual_Deb01, Actual_Deb02, Actual_Deb03
		FROM #t
	) S
	UNPIVOT
	(
		Actual_Deb FOR DebStr IN
		(
			Actual_Deb01, Actual_Deb02, Actual_Deb03
		)
	) U
)
SELECT SUM(Actual_Deb) AS GLAmt
FROM Normalized
WHERE DebStr = 'Actual_Deb02';