Posted by markc600 on 10/01/78 11:40

> 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

[Back to original message]