Returning Variables with Stored Procedures

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:

Code:

---

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:

Code:

---

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.
:confused: 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?

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

Quote:

---

Originally posted by zellian


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

Code:

---

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.
:confused: 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