Database

Swaroop Roy wrote:

how to build and execute a query in user defined functions?

create procedure AddNewPerson
  @Name varchar(50)
as
  set nocount on

  insert into StaffAccountTable ( StaffName ) values ( @Name )
  insert into Login ( StaffID, ... ) values ( @@identity, ... )
go

CREATE PROCEDURE InsertStaffAccount
@StaffName VARCHAR(20),
@Gender CHAR(10),
@Address VARCHAR(50),
@Position VARCHAR(20),
@Identity INT OUT
AS
SET NOCOUNT ON

INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
SET @Identity = SCOPE_IDENTITY()
INSERT INTO StaffLogin(StaffID,Password) VALUES (@Identity ,str(@Identity))
GO

@myg@l wrote:

A msgbox pop up with " Procedure or function 'InsertStaffAccount expect parameter '@staffName' which was not supplied. Is that a bug?

declare @iIdentity int<br />
<br />
exec InsertStaffAccount 'John Smith','Male','123 5th Street','CEO',@iIdentity out<br />
<br />
print @iIdentity

@myg@l wrote:

yeah, I'm trying to call it from VB.NET is there any difference?

Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
    "Initial Catalog=mytestdb;" & _
    "Integrated Security=SSPI"

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "InsertStaffAccount"

Dim prm1 As New SqlParameter("@StaffName", SqlDbType.VarChar, 20)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"

Dim prm2 As New SqlParameter("@Gender", SqlDbType.Char, 10)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "MyGender"

Dim prm3 As New SqlParameter("@Address", SqlDbType.VarChar, 50)
prm3.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm3)
prm3.Value = "MyAddress"

Dim prm4 As New SqlParameter("@Position", SqlDbType.VarChar, 20)
prm4.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm4)
prm4.Value = "MyPosition"

Dim prm5 As New SqlParameter("@Identity", SqlDbType.VarChar, 20)
prm5.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm5)

conn.Open()
cmd.ExecuteNonQuery()
conn.Close()

MsgBox("Returned ID=" + prm5.Value)