VB.NET
 

  How to Write Stored procedure With function

Asked By mani prabhakar
20-Nov-09 05:48 AM
Earn up to 0 extra points for answering this tough question.
Reply
How to write a function for retrieving data from database.  I want to write a stored procedure please help me?

  check this

Jack jack replied to mani prabhakar
20-Nov-09 06:12 AM
Reply

CREATE FUNCTION dbo.Function1
   (
   /*
   @parameter1 datatype = default value,
   @parameter2 datatype
   */
   )
RETURNS /* datatype */
AS
   BEGIN
      /* sql statement ... */
   RETURN /* value */
   END

U CAN USE THIS FUNCTION IN SP LIKE THIS>>

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_by_author' AND type = 'P')
   DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS

SELECT * FROM dbo.Function1

 

GO

  Re - Write Stored procedure With function

Lalit M. replied to mani prabhakar
20-Nov-09 06:17 AM
Reply
Use this code sample

Public Class Dac

  Public Shared Function ExecuteDataTable(ByVal storedProcedureName _
        As String, _
        ByVal ParamArray arrParam() As SqlParameter) As DataTable
    Dim dt As DataTable

    ' Open the connection
    Using cnn As New SqlConnection(
           "Data Source=.\sqlexpress;Initial Catalog=AcmeRentals;
                                    Integrated Security=True;")
        cnn.Open()

        ' Define the command
        Using cmd As New SqlCommand
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedProcedureName

            ' Handle the parameters
            If arrParam IsNot Nothing Then
                For Each param As SqlParameter In arrParam
                    cmd.Parameters.Add(param)
                Next
            End If

            ' Define the data adapter and fill the dataset
            Using da As New SqlDataAdapter(cmd)
                dt = New DataTable
                da.Fill(dt)

            End Using
        End Using

    Return dt
  End Function

End Class

More details how to create store procedure

show this articles more info Retrieving Data Using Stored Procedures using Linq

  function which execute sp and return a value

Venkat K replied to mani prabhakar
20-Nov-09 10:33 AM
Reply

public static int InsertUser(string username, string passcode, string userrole, out int ErrCode)

{

using (SqlConnection sqlCon = new SqlConnection(ConfigurationSettings.AppSettings["SqlConn"].ToString()))

{

using (SqlCommand sqlCmd = new SqlCommand("spInsertUser", sqlCon))

{

int result;

sqlCon.Open();

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 20));

sqlCmd.Parameters["@username"].Value = username;

sqlCmd.Parameters.Add(new SqlParameter("@passcode", SqlDbType.VarChar, 20));

sqlCmd.Parameters["@passcode"].Value = passcode;

sqlCmd.Parameters.Add(new SqlParameter("@userrole", SqlDbType.VarChar, 20));

sqlCmd.Parameters["@userrole"].Value = userrole;

sqlCmd.Parameters.Add(new SqlParameter("@ErrCode", SqlDbType.Int));

sqlCmd.Parameters["@ErrCode"].Direction = ParameterDirection.Output;

result = (int)sqlCmd.ExecuteNonQuery();

ErrCode = (int)(sqlCmd.Parameters["@errcode"].Value.ToString() == String.Empty ? -1 : sqlCmd.Parameters["@errcode"].Value);

return result;

}

}

}

Thanks,

  OK. Let me be as precise as possible. Follow these two steps.
[)ia6l0 iii replied to mani prabhakar
22-Nov-09 11:57 AM
Reply
First write an SP!. Your SP should look like this:

CREATE PROCEDURE [dbo].[sp_Retrieve_EmployeesData]
(
@GroupID int
)
AS

SELECT [Group].[GroupID], [Role].[RoleID], [Role].[RoleName] FROM [Group]
WHERE GroupID = @GroupID
RETURN
GO

Note:sp_Retrieve_EmployeesData is the dummy Stored Procedure name that i have used...and replace the SELECT query inside that with your actual one. Also, note that the stored procedure takes the GroupID as input parameter. Replace it with your own, or remove it if you don't need it. 

Second, write a function that talks to the database, executes the stored procedure, returns you the result.

Public Shared  Function RetrieveEmployeesData(ByVal groupID As Integer) As DataSet

 Dim myConn As New SqlConnection = New SqlConnection('Place your connection string here.')
 Dim myCmd As New SqlCommand
 Dim spname As String = "sp_Retrieve_EmployeesData"
 Dim ds As DataSet = New DataSet()
 Try
  myConn.ConnectionString = 
  myConn.Open()

  myCmd.Connection = myConn 
  myCmd.CommandType = CommandType.StoredProcedure
  myCmd.CommandText = spname 
  myCmd.Parameters.Add("@GroupID ", SqlDbType.Int)
  myCmd.Parameters("@GroupID ").Direction = ParameterDirection.Input

  Dim myDA As New SqlDataAdapter(myCmd)
  
  myDA.Fill(ds, spname)
 Catch ex As Exception
  'Handle the error.
 Finally
   myConn.Close()
 End Try
 Return ds
End Function

Note the usage of the stored procedure name, and the input parameter. If you get that right, then all this is pretty easy. 

Hope this helps.
  How to Write Stored procedure
Abhishek Nayak replied to mani prabhakar
20-Jan-10 01:54 AM
Reply

create proc sp_insert_name

@item_nm varchar(500),
@Price varchar(500),
@Status varchar(500),
@category varchar(500),
@Note varchar(500),
@image_name varchar(50)
as

INSERT INTO item_master
(item_nm, Price, Status, category, Note,image_name)
VALUES (@item_nm,@Price,@Status,@category,@Note,@image_name)

take this example for creating new store procdure for insert.

Create New Account