Getting a Return Value from a Stored Procedure


Author
Message
Terry Bottorff
Terry Bottorff
StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)StrataFrame VIP (1.1K reputation)
Group: Forum Members
Posts: 448, Visits: 12K
I have the following Stored Proc (I am not sure it is correct). I just am trying to see if there are any duplicates in the table.

CREATE PROCEDURE [dbo].[sp_CountSRStockforDups] 
   -- Add the parameters for the stored procedure here
   @myRowCount int = 0 OUTPUT
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

    -- Insert statements for procedure here
   select stock, count(stock) as pen from dbo.SRstocks where stock > 0 group by stock having (count(stock)) > 1
   select @myRowCount = @@ROWCOUNT
   return @myRowCount
END

I know I have 2 records that repeat but with the following code it returns zero(0).?
        Dim nret As Integer = 1000
                 Dim cmd As New SqlCommand("sp_CountSRStockforDups")
                cmd.CommandType = Data.CommandType.StoredProcedure
                ' OutPut Parameter
                cmd.Parameters.Add("@myRowCount", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output
                nret = CType(cmd.Parameters("@myRowCount").Value, Integer)
Please understand I am not sure any of this is correct except the attached picture seems to indicate that my stored proc is working but any help would be appreciated. TIA



Attachments
countingstk.png (190 views, 6.00 KB)
Reply
Edhy Rijo
E
StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)StrataFrame VIP (6.4K reputation)
Group: StrataFrame Users
Posts: 2.4K, Visits: 23K
Charles R Hankey (11/29/2010)
(... reading that over it probably doesn't make any sense unless you've been playing with TVPs but for anybody who has, the TVP as empty table if no param is passed came as a surprise to me and somebody may find it useful )

Hi Charles,

I have not worked with a TVP yet.  I have a consultant "Uri Dimant" which is a MS SQL MVP which helps me with those deep needs and even though he is pretty good explaining this type of things with MS-SQL, sometimes I don't have the luxury to pay to much of attention to details.  He has created some complex stored procedures to deal with millions of records and re-factored those couple of times to take advantage of new functionality of MS-SQL 2008 R2.  In case you or anyone needs serious help with any MS-SQL problem, feel free to contact him.


Edhy Rijo

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Threaded View
Threaded View
Terry Bottorff - 15 Years Ago
Ivan George Borges - 15 Years Ago
Terry Bottorff - 15 Years Ago
Terry Bottorff - 15 Years Ago
             You're welcome. :cool:
Ivan George Borges - 15 Years Ago
Edhy Rijo - 15 Years Ago
Trent L. Taylor - 15 Years Ago
Charles R Hankey - 15 Years Ago
Edhy Rijo - 15 Years Ago
                     >> cmd.CommandText= String .Format(...
Charles R Hankey - 15 Years Ago
                         Hi Charles, I had to do the test this time, since it is not...
Edhy Rijo - 15 Years Ago
                             This looks good. Also, if you pass a comma separated list of fields to...
Charles R Hankey - 15 Years Ago
                                 [quote][b]Charles R Hankey (11/29/2010)[/b][hr]This looks good. Also,...
Edhy Rijo - 15 Years Ago
                                     I think your idea of an array for multiple fields is very good,...
Charles R Hankey - 15 Years Ago
                                         [quote][b]Charles R Hankey (11/29/2010)[/b][hr](... reading that over...
Edhy Rijo - 15 Years Ago
Ger Cannoll - 15 Years Ago
Edhy Rijo - 15 Years Ago
Terry Bottorff - 15 Years Ago
Edhy Rijo - 15 Years Ago
Terry Bottorff - 15 Years Ago
Terry Bottorff - 15 Years Ago
Edhy Rijo - 15 Years Ago
Terry Bottorff - 15 Years Ago
Edhy Rijo - 15 Years Ago
Terry Bottorff - 15 Years Ago
Edhy Rijo - 15 Years Ago

Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search