Posted by Erland Sommarskog on 10/01/60 11:40

Bill Bob (nospam@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]