Setting an Parameters in Stored Procedure Using an Alias
-
I created a stored procedure called CustomerDetails, which has fields for EmpLName, EmpFName, EmpMI. I would like to concatenate the fields together to call the parameter field "FullName" based on the following stored procedure:
CREATE Procedure CustomerDetail
(
@CustomerID int,
@EmpNum char(5) OUTPUT,
@EmpLName varchar(25) OUTPUT,
@EmpFName varchar(25) OUTPUT,
@EmpMI char(1) OUTPUT,
@Username varchar(15) OUTPUT,
@Password nvarchar(50) OUTPUT,
@ClassID int,
@StationID int,
@Email varchar(45),
@active bit
)
AS
SELECT
@EmpNum = EmpNum,
@EmpLName = EmpLName ,
@EmpFName = EmpFName,
@EmpMI = EmpMI,
@Username = Username,
@Password = Password,
@ClassID = ClassID,
@StationID = StationID,
@Email = Email,
@active = Active
FROM
Customers
WHERE
CustomerID = @CustomerID
GO
The Customer table was designed to allow fields to be searchable, if necessary.
I know that it can be concatenated using: EmpLName + ', ' + EmpFName + ' ' + EmpMI. But, I'm not sure if this can be done using the above example. Is there a better way to handle this. Thanks.
-
Can you supply an example script with a call to your SP in context?
This may help me understand exactly what you are looking for.
Once you understand the BITs, all the pieces come together
-
Here's the script below. I hope this helps.
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace IBuySpy
'*******************************************************
'
' CustomerDetails Class
'
' A simple data class that encapsulates details about
' a particular customer inside the IBuySpy Customer
' database.
'
'*******************************************************
Public Class CustomerDetails
Public FullName As String
Public Email As String
Public Password As String
End Class
'*******************************************************
'
' CustomersDB Class
'
' Business/Data Logic Class that encapsulates all data
' logic necessary to add/login/query customers within
' the IBuySpy Customer database.
'
'*******************************************************
Public Class CustomersDB
'*******************************************************
'
' CustomersDB.GetCustomerDetails() Method <a name="GetCustomerDetails"></a>
'
' The GetCustomerDetails method returns a CustomerDetails
' struct that contains information about a specific
' customer (name, email, password, etc).
'
' Other relevant sources:
' + <a href="CustomerDetail.htm" style="color:green">CustomerDetail Stored Procedure</a>
'
'*******************************************************
Public Function GetCustomerDetails(ByVal customerID As String) As CustomerDetails
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CustomerDetail", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterCustomerID As SqlParameter = New SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Value = CInt(customerID)
myCommand.Parameters.Add(parameterCustomerID)
Dim parameterFullName As SqlParameter = New SqlParameter("@FullName", SqlDbType.NVarChar, 50)
parameterFullName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFullName)
Dim parameterEmail As SqlParameter = New SqlParameter("@Email", SqlDbType.NVarChar, 50)
parameterEmail.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterEmail)
Dim parameterPassword As SqlParameter = New SqlParameter("@Password", SqlDbType.NVarChar, 50)
parameterPassword.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterPassword)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
' Create CustomerDetails Struct
Dim myCustomerDetails As CustomerDetails = New CustomerDetails()
' Populate Struct using Output Params from SPROC
myCustomerDetails.FullName = CStr(parameterFullName.Value)
myCustomerDetails.Password = CStr(parameterPassword.Value)
myCustomerDetails.Email = CStr(parameterEmail.Value)
Return myCustomerDetails
End Function
'*******************************************************
'
' CustomersDB.AddCustomer() Method <a name="AddCustomer"></a>
'
' The AddCustomer method inserts a new customer record
' into the customers database. A unique "CustomerId"
' key is then returned from the method. This can be
' used later to place orders, track shopping carts,
' etc within the ecommerce system.
'
' Other relevant sources:
' + <a href="CustomerAdd.htm" style="color:green">CustomerAdd Stored Procedure</a>
'
'*******************************************************
Public Function AddCustomer(fullName As String, email As String, password As String) As String
' Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("CustomerAdd", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterFullName As New SqlParameter("@FullName", SqlDbType.NVarChar, 50)
parameterFullName.Value = fullName
myCommand.Parameters.Add(parameterFullName)
Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 50)
parameterEmail.Value = email
myCommand.Parameters.Add(parameterEmail)
Dim parameterPassword As New SqlParameter("@Password", SqlDbType.NVarChar, 50)
parameterPassword.Value = password
myCommand.Parameters.Add(parameterPassword)
Dim parameterCustomerID As New SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)
Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
' Calculate the CustomerID using Output Param from SPROC
Dim customerId As Integer = CInt(parameterCustomerID.Value)
Return customerId.ToString()
Catch
Return String.Empty
End Try
End Function
'*******************************************************
'
' CustomersDB.Login() Method <a name="Login"></a>
'
' The Login method validates a email/password pair
' against credentials stored in the customers database.
' If the email/password pair is valid, the method returns
' the "CustomerId" number of the customer. Otherwise
' it will throw an exception.
'
' Other relevant sources:
' + <a href="CustomerLogin.htm" style="color:green">CustomerLogin Stored Procedure</a>
'
'*******************************************************
Public Function Login(ByVal email As String, ByVal password As String) As String
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CustomerLogin", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterEmail As SqlParameter = New SqlParameter("@Email", SqlDbType.NVarChar, 50)
parameterEmail.Value = email
myCommand.Parameters.Add(parameterEmail)
Dim parameterPassword As SqlParameter = New SqlParameter("@Password", SqlDbType.NVarChar, 50)
parameterPassword.Value = password
myCommand.Parameters.Add(parameterPassword)
Dim parameterCustomerID As SqlParameter = New SqlParameter("@CustomerID", SqlDbType.Int, 4)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)
' Open the connection and execute the Command
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Dim customerId As Integer = CInt(parameterCustomerID.Value)
If customerId = 0 Then
Return Nothing
Else
Return customerId.ToString()
End If
End Function
End Class
End Namespace
-
aturner51, To be truthfull, I do not access my SPs using VBs ExecuteNonQuery(), therefor I do not know the proper VB syntax for doing what you are asking.... however, you should just be able to add @Fullname both as a parameter to the SP, and as part of the SELECT being executed in the SP. Just make @Fullname = {some expression you want}.
Hope this helps.
Once you understand the BITs, all the pieces come together -
Maybe I don't understand what you are asking, but is this what you are looking for?
CREATE Procedure CustomerDetail
(
@CustomerID int,
@FullName varchar(51) OUTPUT,
@EmpNum char(5) OUTPUT,
--@EmpLName varchar(25) OUTPUT,
--@EmpFName varchar(25) OUTPUT,
--@EmpMI char(1) OUTPUT,
@Username varchar(15) OUTPUT,
@Password nvarchar(50) OUTPUT,
@ClassID int,
@StationID int,
@Email varchar(45),
@active bit
)
AS
SELECT
@FullName = EmpFName + ' ' + EmpMI + ' ' + EmpLName
@EmpNum = EmpNum,
--@EmpLName = EmpLName ,
--@EmpFName = EmpFName,
--@EmpMI = EmpMI,
@Username = Username,
@Password = Password,
@ClassID = ClassID,
@StationID = StationID,
@Email = Email,
@active = Active
FROM
Customers
WHERE
CustomerID = @CustomerID
GO
It seems like you already had this answer so I am a bit confused about what you are askin.
steve
-
Thank you, Grasshopper. That's exactly what I needed. I appreciate the time you took to view my thread. Again, thanks so much.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply