Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / September 2007

Tip: Looking for answers? Try searching our database.

I want to return a string to a wrapper from a subordinate stored procedure

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bobc - 27 Sep 2007 20:36 GMT
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
-----------------------------------------------------------------
bobc - 27 Sep 2007 20:44 GMT
> Using SQL Server 2000...
>
[quoted text clipped - 66 lines]
> 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
Erland Sommarskog - 27 Sep 2007 22:38 GMT
> The Wrapper:
> -----------------------------------------------------------------
[quoted text clipped - 4 lines]
> EXEC @lString = sub_proc @CommCode, @lString OUT
> GO

Remove "@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 @var1 int,
>      @var2 int
[quoted text clipped - 8 lines]
>      FROM mytable
>      WHERE condition=2)

Rather you can do:

  SELECT @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

Signature

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

bobc - 28 Sep 2007 15:56 GMT
> > The Wrapper:
> > -----------------------------------------------------------------
[quoted text clipped - 38 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks very much, Erland!  -BobC
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.