Returning Values from a Dynamic SQL

[Bill Bob]

I have an accounting database which contains data from various years.
The frontend is a VB.Net program. At the year end, the program creates
new voucher and transaction tables and creates new stored procedures for
them.

I just append the 'new year' at the end and create them

ie, Vouchers2001, Vouchers2002, Vouchers2003
Similarly Transactions2001, Transactions2002.

The data for all the years is in the same database.

Also, I maintain a table called 'Books' which contains the Years for
which data is present in the Database. The Structure of the Books table
is

BookID BookYear
1 2001
2 2002
3 2003
4 2004


My Problem is that i need to know the current balance of any ledger for
any year. The method to calculate the balance for any year is to start
from the Minimum year in the Books table and continue upto the required
year. The SQL is as follows.
DECLARE @iLedgerID AS INT --will be passed as parameter
DECLARE @iYear as INT --will be passed as parameter
DECLARE @CurrentBalance as MONEY

SET @iLedgerID =1
DECLARE @MinBook as INTEGER
DECLARE @String nVarchar(4000)

SELECT @MinBook = Min(BookYear)
FROM Books

WHILE @MinBook <= @iYear
BEGIN

SET @String = ' DECLARE @TT Money ' + char(13) +
' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'
+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +
'WHERE (LedgerID = @iLedgerID)'
EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */

SET @MinBook = @MinBook + 1
END

Now this is just a sample code. It may have a few glitches. My question
is

a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time. If No then
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.

TIA



*** Sent via Developersdex http://www.developersdex.com ***

[Bill Bob]

Oops, I missed out the last line in the string varible. The last line
returns the calculated variable. The SQL docmentation says that Return
can only return integer types. so how do i return a money value. I
cannot insert into a temporary table because the application is
Multi-User and may fail.
WHILE @MinBook <= @iYear
BEGIN

SET @String = ' DECLARE @TT Money ' + char(13) +
' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @iLedgerID)' + char(13) +
' Return @TT'
EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID

SET @MinBook = @MinBook + 1
END

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Bill Bob (no****@devdex.com) writes:

Oops, I missed out the last line in the string varible. The last line
returns the calculated variable. The SQL docmentation says that Return
can only return integer types. so how do i return a money value. I
cannot insert into a temporary table because the application is
Multi-User and may fail.

1) You would have a much simpler task, if you made the year a key in the
Vouchers and Transactions table, rather than having one table per year.

2) So you don't have the possibility to do that, but then create views:

CREATE VIEW Vouchers AS
SELECT Year = '2000', * FROM Vouchers2000
UNION ALL
SELECT '2001', * FROM Vouchers2001
UNION ALL
...

3) That you cannot use temp tabls in a multi-user environment is a mis-
understanding. Temp tables are visible for the local connection only.

4) However, ther prefer method for getting scalar data back from
sp_executesql is output parameters. For a quick example, see
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[markc600]

a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time.
Yes
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.

You can use OUTPUT parameters in sp_executesql

SET @String = ' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) -
ISNULL(Credit,0) ),0 )' +
' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID' +
' = dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + '.VoucherID ' + 'WHERE
(LedgerID = @iLedgerID)' + char(13) +
' Return @TT'

DECLARE @TT Money
EXEC sp_executesql @String, N'@iLedgerID Int, @TT Money', @iLedgerID ,
@TT OUTPUT

[markc600]

.... should be this

EXEC sp_executesql @String, N'@iLedgerID Int, @TT Money OUTPUT',
@iLedgerID ,
@TT OUTPUT

[Bill Bob]

1) You would have a much simpler task, if you made the year > a key in

the Vouchers and Transactions table, rather than > having one table per
year.

My intial problem was something else. So, I had to switch to different
table for different years.

Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
VoucherNo. The VoucherNo must be unique for a vouchertype and within a
financial year (1st April - 31st March).

SQL Server cannot create a unique index with this criteria. Hence, I had
to split the tables.

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Bill Bob (no****@devdex.com) writes:

Each Voucher Table contains a VoucherDate and a VoucherTypeID and a
VoucherNo. The VoucherNo must be unique for a vouchertype and within a
financial year (1st April - 31st March).

SQL Server cannot create a unique index with this criteria. Hence, I had
to split the tables.

Of course it can! You would add Year as a column in the table, and the
primary key would be (Year, VoucherTypeID, VoucherNo).

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx