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 [at] 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 [at] 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
[at] lString varchar(255) OUT
AS

EXEC [at] lString = sub_proc [at] CommCode, [at] lString OUT
GO
------------------------------------------------------------ -----

The subordinate procedure:
------------------------------------------------------------ -----
CREATE PROCEDURE sub_proc
[at] lString varchar(255) OUT

AS

DECLARE [at] var1 int,
[at] var2 int

SELECT [at] var1 =
(SELECT count(mycolumn)
FROM mytable
WHERE condition=1)

SELECT [at] var2 =
(SELECT count(mycolumn)
FROM mytable
WHERE condition=2)

/* If [at] var1 returns 5 and [at] var2 returns 7, Then [at] lString below would
be "5. 7." */

SELECT [at] lString = STR( [at] var1) + '.' + STR( [at] var7) + '.'
GO
------------------------------------------------------------ -----

On Sep 27, 3:36 pm, bobc <bcana... [at] 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 [at] 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 [at] 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
> [at] lString varchar(255) OUT
> AS
>
> EXEC [at] lString = sub_proc [at] CommCode, [at] lString OUT
> GO
> ------------------------------------------------------------ -----
>
> The subordinate procedure:
> ------------------------------------------------------------ -----
> CREATE PROCEDURE sub_proc
> [at] lString varchar(255) OUT
>
> AS
>
> DECLARE [at] var1 int,
> [at] var2 int
>
> SELECT [at] var1 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=1)
>
> SELECT [at] var2 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=2)
>
> /* If [at] var1 returns 5 and [at] var2 returns 7, Then [at] lString below would
> be "5. 7." */
>
> SELECT [at] lString = STR( [at] var1) + '.' + STR( [at] var7) + '.'
> GO
> ------------------------------------------------------------ -----

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

EXEC [at] lString = sub_proc [at] lString OUT

It's been a long day. -BobC

bobc (bcanavan [at] fmbnewhomes.com) writes:
> The Wrapper:
> ------------------------------------------------------------ -----
> CREATE PROCEDURE wrapper
> [at] lString varchar(255) OUT
> AS
>
> EXEC [at] lString = sub_proc [at] CommCode, [at] lString OUT
> GO

Remove " [at] lString =". The return value from a stored procedure is
always integer, and customary you use it to return success/failure
indication, with 0 meaning success.

> DECLARE [at] var1 int,
> [at] var2 int
>
> SELECT [at] var1 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=1)
>
> SELECT [at] var2 =
> (SELECT count(mycolumn)
> FROM mytable
> WHERE condition=2)

Rather you can do:

SELECT [at] lString =
ltrim(str(SUM(CASE condition WHEN 1 THEN 1 ELSE 0 END)) + '.' +
ltrim(str(SUM(CASE condition WHEN 2 THEN 1 ELSE 0 END)) + '.' +
...
ltrim(str(SUM(CASE condition WHEN 7 THEN 1 ELSE 0 END))
FROM mytable
WHERE mycolumn IS NOT NULL
AND condition BETWEEN 1 AND 7

--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

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

On Sep 27, 5:38 pm, Erland Sommarskog <esq... [at] sommarskog.se> wrote:
> bobc (bcana... [at] fmbnewhomes.com) writes:
> > The Wrapper:
> > ------------------------------------------------------------ -----
> > CREATE PROCEDURE wrapper
> > [at] lString varchar(255) OUT
> > AS
>
> > EXEC [at] lString = sub_proc [at] CommCode, [at] lString OUT
> > GO
>
> Remove " [at] lString =". The return value from a stored procedure is
> always integer, and customary you use it to return success/failure
> indication, with 0 meaning success.
>
> > DECLARE [at] var1 int,
> > [at] var2 int
>
> > SELECT [at] var1 =
> > (SELECT count(mycolumn)
> > FROM mytable
> > WHERE condition=1)
>
> > SELECT [at] var2 =
> > (SELECT count(mycolumn)
> > FROM mytable
> > WHERE condition=2)
>
> Rather you can do:
>
> SELECT [at] lString =
> ltrim(str(SUM(CASE condition WHEN 1 THEN 1 ELSE 0 END)) + '.' +
> ltrim(str(SUM(CASE condition WHEN 2 THEN 1 ELSE 0 END)) + '.' +
> ...
> ltrim(str(SUM(CASE condition WHEN 7 THEN 1 ELSE 0 END))
> FROM mytable
> WHERE mycolumn IS NOT NULL
> AND condition BETWEEN 1 AND 7
>
> --
> Erland Sommarskog, SQL Server MVP, esq... [at] sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks very much, Erland! -BobC