|
|
Posted by bobc on 09/27/07 19:36
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
-----------------------------------------------------------------
[Back to original message]
|