Posted by bobc on 09/28/07 14:56

On Sep 27, 5:38 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bobc (bcana...@fmbnewhomes.com) writes:
> > The Wrapper:
> > -----------------------------------------------------------------
> > CREATE PROCEDURE wrapper
> > @lString varchar(255) OUT
> > AS
>
> > 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
>
> > SELECT @var1 =
> > (SELECT count(mycolumn)
> > FROM mytable
> > WHERE condition=1)
>
> > SELECT @var2 =
> > (SELECT count(mycolumn)
> > 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
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> 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

• Next in forum: Re: Comparing two tables
• Prev in forum: nt authority\system?
• Thread view: Re: I want to return a string to a wrapper from a subordinate stored procedure

[Reply to this message]