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
----------------------------------------------------------------- 3 2268
On Sep 27, 3:36 pm, bobc <bcana...@fmbnewhomes.comwrote:
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
-----------------------------------------------------------------
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
bobc (bc******@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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Sep 27, 5:38 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rhino |
last post by:
I am trying to verify that I correctly understand something I saw in the DB2
Information Center.
I am running DB2 Personal Edition V8.2.1 on Windows. I came across the
following in the Info...
|
by: orencs |
last post by:
Hi,
I am using Datareader and stored procedure in C# ADO.NET.
When I am running the stored procedure in the SQL Query Analyzer and
recieve two rows (as I hace expected)
col1 col2
0 1
0 ...
|
by: Peter |
last post by:
Hi, there
I have created an stored procedure using the DDL below for my MS Access
Database and no error occurs. Also it can create an stored procedure if I
changed the parameter from "" to...
|
by: Fir5tSight |
last post by:
Hi All,
I have a C#.NET code as follows:
private void ScanInput_KeyPress(object sender,
System.Windows.Forms.KeyPressEventArgs e)
{
try
{
Row lRow = this.Connection.InsertScannedFile(ID);
|
by: Mick Walker |
last post by:
Hi Everyone,
I am stumped here. I have the following stored proceedure:P
CREATE PROCEDURE .
@SupplierSKU varchar(50),
@RetVal int
AS
Select @Retval = count(*) from dbo.ImportLines
Where =...
| |
by: bobc |
last post by:
In my stored procedure, I want to parse @ArrayOfDays into @d1 through
@d5.
@ArrayOfDays is a varchar input parameter containing,
for example, "1.7.21.25.60." - five elements.
Most active...
|
by: Lin100 |
last post by:
Access ADP and Stored Procedure Did not Return Any Records
Access 2002 and SQL 2000 Server
I have a form named "Selector", and it have four combo boxes and a subform named...
|
by: E11esar |
last post by:
Hi there.
I have written a C# web service that calls an Oracle stored procedure. The SP is a simple select-max query and the table it is getting the value from has about 2.8 million rows in it. ...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |