Posted by bobc on 09/27/07 19:44

On Sep 27, 3:36 pm, bobc <bcana...@fmbnewhomes.com> wrote:
> Using SQL Server 2000...
>
> I wrote a wrapper to call a sub proc (code provided below). The
> intended varchar value returned in the output parameter of each proc
> is a string implementation of an array.
> (The string separates elements by adding a period after each value.
> e.g. 1. 2. 3. 4. 5. etc., although my simplified example only creates
> two elements.)
> My vb.net calling code parses the returned string into individual
> elements.
>
> I TESTED BOTH PROCS FIRST:
> The wrapper returns 'hello' when I test it by inserting
> SELECT @lString='hello'
> before the GO, so I believe it is called properly.
>
> The sub_proc returns the "array" I want when I call it directly.
>
> THE PROBLEM: When I call the wrapper, and expect it to call sub_proc,
> it returns a zero.
> In fact, when I assign a literal (like 'hello') to @lString in
> sub_proc, 'hello' is not returned.
> So the wrapper is not calling the sub_proc, or the sub_proc is not
> returning an output value.
> OR...I have read about some issues with OUTPUT string parameters being
> truncated or damaged somehow when passed. I doubt this is the
> problem, but I'm open to anything.
>
> I want to use the wrapper because, when it's finally working, it will
> call several sub_procs and
> return several output values.
>
> Any thoughts? Thanks for looking at it! - Bob
>
> The Wrapper:
> -----------------------------------------------------------------
> CREATE PROCEDURE wrapper
> @lString varchar(255) OUT
> AS
>
> EXEC @lString = sub_proc @CommCode, @lString OUT
> GO
> -----------------------------------------------------------------
>
> The subordinate procedure:
> -----------------------------------------------------------------
> CREATE PROCEDURE sub_proc
> @lString varchar(255) OUT
>
> AS
>
> DECLARE @var1 int,
> @var2 int
>
> SELECT @var1 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=1)
>
> SELECT @var2 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=2)
>
> /* If @var1 returns 5 and @var2 returns 7, Then @lString below would
> be "5. 7." */
>
> SELECT @lString = STR(@var1) + '.' + STR(@var7) + '.'
> GO
> -----------------------------------------------------------------

Correction: delete "@CommCode," from the EXEC statement in wrapper.
Should read as follows:

EXEC @lString = sub_proc @lString OUT

It's been a long day. -BobC

[Back to original message]