Allan Allan - 4 months ago 24
SQL Question

How to replace empty string parameter to Null in stored procedure and VB.net

I have stored procedure that allow null parameter and works fine in SSMS. . Here is the SP

ALTER PROCEDURE [dbo].[sp_RepInventorySales]
@SalesOrderNo varchar(9) = null
AS
BEGIN
SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
FROM SalesOrderDetail
where SalesOrderNo = isnull(@SalesOrderNo, SalesOrderNo )


Actually the SP is the source of my crystal report. When Me.txtSalesOrderNo.Text is NOT empty I get the correct output.However, if textbox is empty, the stored procedure treats the parameter as empty string '' . How can i replace empty string to NULL? . Here is the code VB code

Private Dset As DataSet
Private Da As SqlDataAdapter

Dim objRpt As New RepInventorySales /*used for crystal report*/
Dset = New DataSet

Da = New SqlDataAdapter("sp_RepInventorySales '" & trim(Me.txtSalesOrderNo.Text) & "' ", Conn_DB)
Da.Fill(Dset)
If Dset.Tables(0).Rows.Count <> 0 Then
objRpt.SetDataSource(Dset.Tables(0))


I tried to use IIF in query string in SqlDataAdapter to replace empty string to null but gives me error

Da = New SqlDataAdapter("sp_RepInventorySales '" & Trim(Me.IIf(txtTextBox.Text Is Nothing, Null, txtTextBox.Text).Text) & "' ", Conn_DB)


This is the final solution. thank you for your help!

IF @SalesOrderNo = ''
SET @SalesOrderNo = NULL
IF @ContainerType = ''
SET @ContainerType = NULL

SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
FROM SalesOrderDetail
WHERE ( SalesOrderNo = @SalesOrderNo OR @SalesOrderNo IS NULL )
AND
( ContainerType = @ContainerType OR @ContainerType IS NULL )

Answer

just put the condition on your SP

   IF @SalesOrderNo = '' SET @SalesOrderNo = null 
   IF @ContainerType= '' SET @ContainerType= null 
   SELECT SalesOrderNo, ItemCode, ContainerNo, SealNo, ContainerType, OrderQty
   FROM  SalesOrderDetail 
   where SalesOrderNo = @SalesOrderNo AND ContainerType = @ContainerType