Returning Variables with Stored Procedures

[zellian]

I am doing an sql query on a database that has 4 columns. (using sql server)
column names:
Date [smalldatetime],Varnum [smallint],Value [float],TextValue [nvarchar](50).

so I make a stored procedure to run this query, here is the stored procedure:

CREATE PROCEDURE [dbo].[seek]
	(@Date 	[smalldatetime],
	 @VarNum 	[smallint],
	 @Value 	[float] OUTPUT,
	 @TextValue 	[nvarchar](50) OUTPUT
	)
 AS
SELECT @Value=Value, @TextValue=TextValue
FROM datatbl
WHERE Date=@Date and VarNum=@VarNum

RETURN
GO

now, I call this stored procedure in the sql query analyzer like this:

declare @value [float], @textvalue [nvarchar](50)
execute seek @varnum=1, @date='1/1/2001', @value=1, @textvalue=''
print @value
print @textvalue

but the values that I have print out of the variables (last 2 lines) are nothing.
Does anyone know how to get the output variables when calling a stored procedure? If not in sql, then how do I do it in VB?

[Criki]

When returning values, typically, I use a function instead of procedure.

Once you have created a function you call this by selecting from it as you would a table or view.

example:
Let's say there's a function call Thingy and it has 1 parameter that's called Name.
You would call this in VB like this...

Dim sSQL as string
dim RS as Adodb.Recordset

sSql = "select THINGY(""John"") from dual"
RS.Open sSql, db, adOpenStatic, adLockReadOnly

if RS.recordcount <> 0 then
RS.movefirst
msgbox RS.Fields(0).Value
end if

I hope this helps

[rickm]

Originally posted by zellian


now, I call this stored procedure in the sql query analyzer like this:

declare @value [float], @textvalue [nvarchar](50)
execute seek @varnum=1, @date='1/1/2001', @value=1, @textvalue=''
print @value
print @textvalue

but the values that I have print out of the variables (last 2 lines) are nothing.
Does anyone know how to get the output variables when calling a stored procedure? If not in sql, then how do I do it in VB?

Well the correct syntax when you call this stored procedure, if you want to have the @textvalue input as an output variable, the best thing to do would be

select @value = 1,@varnum = 1, @date = '1/1/2001', @textvalue = ''
exec seek @varnum,@date,@value,@textvalue output
print @textvalue

It looks like you are trying to get the @value back too, if you are trying to print it.

Rick