Output parameters for dynamic SQL

[Nemisis]

Hi everyone,

I am saving a record to SQL and want to return the ID of the record
added as an output parameter, can you do this in dynamic SQL, without
the use of a stored procedure??

i.e.

comm = new sqlcommand
comm.commandtext = "Insert (name) values (@name); select @id =
SCOPE_IDENTITY();

' add parameters to command etc

comm.executeNonQuery()

dim a as integer = ctype(comm.parameters.item("@id").value, integer)

Is this possible?? I know you can do it in SQL but we have over 80
databases that this data layer will connect to and i am not one for
duplicate code over each database, just encase one database had a
different version of the sproc for whatever reason.

 

[Dblood]

Nemisis,

Try this:

Dim oTestCon As SqlConnection
Dim oTestCmd As SqlCommand
Dim result As Int16

oTestCon = New SqlConnection("Data Source=XXXX;Initial Catalog=XXX;User
Id=XXX;Password=XXX;")
oTestCmd = New SqlCommand("INSERT INTO

(field) VALUES (value)
SELECT @@identity", oTestCon)

oTestCon.Open()
result = oTestCmd.ExecuteScalar()
oTestCon.Close()
Response.Write(result)

Should give you what you're looking for (result = newly added rec id).
Let me know if I misunderstood what you needed.

dkb

 

[Dave Sexton]

Hi,

Yes, you can add output parameters to queries:

Dim command As New SqlCommand("SELECT @ID = 55", conn)
command.Parameters.Add("@ID", System.Data.SqlDbType.Int)
command.Parameters.Items("@ID").Direction = System.Data.ParameterDirection.Output

command.ExecuteNonQuery()

Dim id As Integer = CType(command.Parameters.Items("@ID").Value, Integer);