Stored Procedures Return Value

JaedenRuiner · #1 (**permalink**) 10-10-2008, 10:09 AM

Stored procedures seem to have a return value on the VB side of things, but I wasn't to sure on the SQL Server side of things, as well I've seen help on how ExecuteScalar on a Stored procedure can return a single value (like the ID of the item just entered or whatnot.

How would I write the Stored procedure to do that? Can I just add a Return {X} at the end of the procedure or is it more complicated than that?

here's the proc:

Code:

ALTER PROCEDURE [dbo].[InsertEmail] 
	-- Add the parameters for the stored procedure here
	@custid nvarchar(6) = '', 
	@qty int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @Num int
	DECLARE @ID nvarchar(10)
	SET @Num = dbo.NextEmailID(@custid) 
	SET @ID = ((right(@custid,(3))+'-')+right('0000'+CONVERT([nvarchar](5),@Num,(0)),(5)))
	INSERT INTO [dbo].[tbl_Email] (Num, Customer, Email_ID, ExpQty) VALUES (@Num, @custid, @ID, @qty)
END

The Value in that proc I would like returned is @ID, but would that mean I should convert it to a function instead of a procedure?

Thanks

ss7thirty · #2 (**permalink**) 10-10-2008, 12:58 PM

You can just make this an OUTPUT variable. There is a property of SQLParameter object and I think it is called direction and it is an enum with three values. Input, Output, Both. Google this 'T-SQL Stored Proc Output Parameters VB.NET' that should get the job done.

JaedenRuiner · #3 (**permalink**) 10-10-2008, 2:59 PM

Quote:

Originally Posted by ss7thirty

You can just make this an OUTPUT variable. There is a property of SQLParameter object and I think it is called direction and it is an enum with three values. Input, Output, Both. Google this 'T-SQL Stored Proc Output Parameters VB.NET' that should get the job done.

Yea,

I found that via Google about 30 seconds after posting this question. Procs only return ints, where Functions can return whatever type you want. As a matter of curiosity, when I set up the command via the DataSource Designer (for Strongly Typed Datasets) it did something interesting. In the SQL Manager where I created the Procedure, I set the OUTPUT parameter to OUTPUT. However, VB Express interpreted that as an InputOutput parameter which proceeded to complain when I tried to execute the SqlCommand like this: (Output Parameter is Parameter(3))
[code]
cmd.Parameters(1).value = CustID
cmd.Parameters(2).value = -1
try
cmd.ExecuteNonQuery()
catch
end try
[code]

I fixed it by simply adding the line:

Code:

cmd.parameters(3).Value = ""

My Curiosity is whether the InputOutput determined type of that Parameter is that VB's doing or is that necessary for the interface with the Stored Proc on the DB, or can i manually (through the designer) change that parameter to Output Only and then eliminate that parameter initializer in my code?

Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)