Posted by Erland Sommarskog on 09/27/07 21:38

bobc (bcanavan@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, 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

• Next in forum: Re: TDS and character encoding
• Prev in forum: Re: Apparent DB engine bug in SQL Server 2005
• Thread view: Re: I want to return a string to a wrapper from a subordinate stored procedure

[Reply to this message]