Stored Procedures Return Value

[JaedenRuiner]

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:

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]

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]

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))

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