Stored Procedure with Optional paramenter
-
I have a stored procedure with optional parameter. It run fine. while i have the
this asp.net code.
dpName =
New SqlDataAdapter(selectCom)
selectCom.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dtB
selectCom.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = dtE
dpName.Fill(ds, "TrainingName")
but after I added one more parameter. it did not have row result. While I used the stored procedure debug to check the stored procedure out. using the same value as just two paramenter input, I got the error.
input value is
@DateFrom 9/1/2005 or #9/1/2005#
@DateTo datetime 10/10/2005 #10/10/2005#
@mName as null or 'Part I'
I tried to set
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Thanks.myconn.Open()
myComm.Parameters.Add("@DateFrom", SqlDbType.DateTime).Value = dtB
myComm.Parameters.Add("@DateTo", SqlDbType.DateTime).Value = dtE
myComm.Parameters.Add("@mName ", SqlDbType.VarChar).Value = GetSubjectLine(strReport)
Dim dpName As New SqlDataAdapter(myComm)
dpName.Fill(ds, "TrainingName")
Dim i = 0 'ds.Tables("TrainingName").Rows
For Each rowName In ds.Tables("TrainingName").Rows
GetNameSelect(i) = rowName("DoctorName")
i = i + 1
Next
CREATE PROCEDURE [dbo].[get_phy_list]
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
AS
begin
declare @moduleName varchar(50)
if @mName is null
begin
SELECT * from dbo.TrainingName
INNER JOIN
(SELECT DISTINCT LicenseNumber
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
UNION
SELECT DISTINCT [License Number] as LicenseNumber
FROM dbo.[Doctor Quiz Log]
Where (dbo.[Doctor Quiz Log].[Access Quiz Time] >= @DateFrom and dbo.[Doctor Quiz Log].[Access Quiz Time] < @DateTo)) p
ON dbo.TrainingName.[LicenseNumber] = p.[LicenseNumber]
end
if @mName ='PartI'
BEGIN
set @moduleName ='Part I'
SELECT DISTINCT LicenseNumber , DoctorName
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and dbo.[Doctor Module Log].[Module Name] ='+ @moduleName ' order by DoctorName
END
End
-
some comments
*replace
and dbo.[Doctor Module Log].[Module Name] ='+ @moduleName '
with
and dbo.[Doctor Module Log].[Module Name] =@moduleName
*SELECT * from dbo.TrainingName
Don't use select *, name your fields
*ADD SET NOCOUNT ON
after CREATE PROCEDURE [dbo].[get_phy_list]
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
AS
to avoid an additional network roundtrip
* ON dbo.TrainingName.[LicenseNumber] = p.[LicenseNumber]
/*add RETURN statement, to skip the if @mName ='PartI'
part */
end
-
Thanks. I changed the
if @mName ='PartI'
BEGIN
set @moduleName ='Part I'
SELECT DISTINCT LicenseNumber , DoctorName
FROM dbo.[Doctor Module Log]
Where (dbo.[Doctor Module Log].[Access Module Time] >= @DateFrom and dbo.[Doctor Module Log].[Access Module Time] < @DateTo)
and dbo.[Doctor Module Log].[Module Name] like @moduleName order by DoctorName
END
return
It still get the error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
-
*does GetSubjectLine(strReport) returns unicode?
*Is Access Module Time a datetime field?
If you create a test stored procedure
CREATE PROCEDURE [dbo].[get_phy_list]
@DateFrom datetime,
@DateTo datetime,
@mName varchar(50) =null
AS
SET NOCOUNT ON
SET @moduleName ='Part I'
SELECT DISTINCT LicenseNumber , DoctorName
FROM dbo.[Doctor Module Log]
Where [Access Module Time] >= @DateFrom
and [Access Module Time] < @DateTo )
and [Module Name] = @moduleName /*why like?*/
order by DoctorName
work?
-
I set that as string. Even though I set my criteria directly and it did not return any value. My vb.net code work with two parameters. so I suspect the stored procedure wrong. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply