hi all. can i create sql function can return more than a paramet?

____________________________
Please help out.

I don't know if this is exactly what you mean but in SQL Server you can create a stored procedure with input and output parameters. Input parameters are passed to the function, output parameters are returned by the stored procedure.

here's an example of a SP definition:

CREATE PROCEDURE test(@input1 integer,@output1 integer OUTPUT, @output2 varchar(13) OUTPUT,@output3 smallint OUTPUT) AS SELECT @output1=@input1+1 SELECT @output2='HELLO' SELECT @output3=10 SELECT * FROM My_Table

This SP returns three output parameters and takes one for input, it also returns a recordset...

hope this helps

____________________________
AndreaVB

can i used your store procedure like this
dim output1 as integer
dim output2 as string
dim output3 as byte
con.execute "Exec  test 1," & output1 & "," & output2 & "," & output3

can output1=1
      output2=hello
      output3=10


____________________________
Please help out.

Are you using ADO to connect?

____________________________
AndreaVB

anyway here's the ADO code

Private Sub Command1_Click()     Const myServerName = "MYSERVER"     Const myUID = "sa"     Const myPassword = "mypassword"     Const myDatabase = "myDB"          Dim conn As ADODB.Connection     Dim sp As ADODB.Command     Set conn = New ADODB.Connection     conn.Open "driver={SQL Server};server=" & myServerName & ";UID=" & myUID & ";PWD=" & myPassword & ";database=" & myDatabase     Set sp = New ADODB.Command     Set sp.ActiveConnection = conn     sp.CommandText = "{Call test(?,?,?,?)}"     'set the input parameters     sp.Parameters(0) = 1     sp.Parameters(1) = 0     sp.Parameters(2) = ""     sp.Parameters(3) = 0     sp.Execute     MsgBox "Param 1:" & sp.Parameters(1) & vbCrLf & _         "Param 2:" & sp.Parameters(2) & vbCrLf & _         "Param 3:" & sp.Parameters(3)     conn.Close     Set conn = Nothing End Sub

[Edited by admin on 17-05-2006 at 01:08 PM GMT]

____________________________
AndreaVB