Run Stored Procedure

Question

i have this stored procedure from my friend and i need to execute it to retrive all the data " i don't want to send any data to filter it :

USE [Stock]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ES_SearchStockIn]

@StockInNumber nvarchar(13),
@StockInType tinyint,
@SupplierName nvarchar(50),
@Status tinyint,
@Reference nvarchar(50),
@Identifier nvarchar(15),
@DateFrom datetime,
@DateTo datetime,
@ReferenceSupplier Nvarchar(50)
AS
BEGIN
SET @DateFrom = dbo.GetDateOnly (@DateFrom)
SET @DateTo = dbo.GetDateOnly (@DateTo)
declare @var datetime
if @DateFrom > @DateTo
Begin
set @var=@DateFrom
set @DateFrom=@DateTo
set @DateTo=@var
End

SET NOCOUNT ON;

SELECT StockInHeader.StockInNaumber,
StockInHeader.StockInDate,
StockInHeader.StockInID,
StockInHeader.Status,
ISNULL(Suppliers.SupplierName,'--') as SupplierName,
StockInHeader.StockInType,
' 'as TypeText,
' ' AS statusText,
CreatedByUser.FullName AS CreatedByUserName,
StockInHeader.CreationDate,
ModifiedByUser.FullName AS ModifiedByUserName,
StockInHeader.ModificationDate

FROM StockInHeader left outer JOIN
Suppliers ON StockInHeader.SupplierID = Suppliers.SupplierID INNER JOIN
Users AS CreatedByUser ON StockInHeader.CretaedByUserID = CreatedByUser.UserID LEFT JOIN
Users AS ModifiedByUser ON StockInHeader.LastModifiedByUserID = ModifiedByUser.UserID

where (@StockInNumber is null or StockInHeader.StockInNaumber like '%'+@StockInNumber+'%')and
(@ReferenceSupplier is null or StockInHeader.SupplierReference like '%'+@ReferenceSupplier+'%') and
(@StockInType is null or StockInHeader.StockInType = @StockInType)and
(@SupplierName is null or Suppliers.SupplierName like '%'+@SupplierName+'%')and
(@Status is null or StockInHeader.Status = @Status)and
((@DateFrom is null and @DateTo is null) or (dbo.GetDateOnly(StockInHeader.StockInDate) between @DateFrom and @DateTo)) and
(@Identifier is null or exists(SELECT 1
FROM StockInDetails INNER JOIN
ProductIdentifier ON StockInDetails.ProductId = ProductIdentifier.ProductID
WHERE (StockInDetails.StockInID = StockInHeader.StockInID) and
(ProductIdentifier.ProductIdentifierValue =@Identifier)))AND
(@Reference is null or StockInHeader.Reference like '%'+@Reference+'%')
order by StockInHeader.StockInID desc
END

can some one help me to do this

Pankaj Kumar Choudhary · Answer

Hello! Abed as i suggested use the NULL default values for parameters. if you pass Null values for parameter then you will get all data. DECLARE @return_value int EXEC @return_value = [dbo].[ES_SearchStockIn] @StockInNumber = NULL @StockInType = NULL @SupplierName = NULL @Status = NULL @Reference = NULL @Identifier = NULL @DateFrom = NULL @DateTo = NULL @ReferenceSupplier = NULL SELECT 'Return Value' = @return_value If you want to use NULL value for any parameter then at Asp.net page doesn't pass any value for that parameter. In your case set Null values as default values for parameter in stored procedure and doesn't send any value for parameters from asp.net page use below code Dim strCon1 = Data Source=MyPC;Initial Catalog=DBNAme;user id=sa;password=123 Dim cmdText = ES_SearchStockIn Using con = New SqlConnection(strCon1) Using cmd = New SqlCommand(cmdText con) con.Open() cmd.ExecuteNonQuery() dgvResult.DataBind() End Using End Using

AbedElHamid AlWahidy · Answer

But in this case it will return the row from date 01/01/2015 to 01/01/2015  i need to return all row in table if i execute the stored procedure and check pass null it seem like : USE [DBName] GO DECLARE @return_value int EXEC @return_value = [dbo].[ES_SearchStockIn] @StockInNumber = NULL  @StockInType = NULL  @SupplierName = NULL  @Status = NULL  @Reference = NULL  @Identifier = NULL  @DateFrom = NULL  @DateTo = NULL  @ReferenceSupplier = NULL SELECT 'Return Value' = @return_value GO it return all data

Manas Mohapatra · Answer

If you don't want to pass the value then add default value to StoredProcedure params like below: ALTER PROCEDURE [dbo].[ES_SearchStockIn] @StockInNumber nvarchar(13) = ''  @StockInType tinyint = 0  @SupplierName nvarchar(50) = ''  @Status tinyint tinyint = 0  @Reference nvarchar(50) = ''  @Identifier nvarchar(15) = ''  @DateFrom datetime = '01/01/2015'  @DateTo datetime = '01/01/2015'  @ReferenceSupplier Nvarchar(50) = '' AS BEGIN -- Implement your logic END Note: Still if you are getting any issue please error details.

AbedElHamid AlWahidy · Answer

this the code that i use to call the stored procedure : Dim strCon1 = Data Source=MyPC;Initial Catalog=DBNAme;user id=sa;password=123 Dim cmdText = ES_SearchStockIn Using con = New SqlConnection(strCon1) Using cmd = New SqlCommand(cmdText con) With cmd .CommandType = CommandType.StoredProcedure .Parameters.Add( @StockInNumber  SqlDbType.NVarChar).Value = txtSInNo.Text .Parameters.Add( @StockInType  SqlDbType.TinyInt).Value = ddlSInType.SelectedValue .Parameters.Add( @SupplierName  SqlDbType.NVarChar).Value = txtSupplier.Text .Parameters.Add( @Status  SqlDbType.TinyInt).Value = cbStatus.SelectedValue .Parameters.Add( @Reference  SqlDbType.NVarChar).Value = txtReference.Text .Parameters.Add( @Identifier  SqlDbType.NVarChar).Value = txtBarcode.Text .Parameters.Add( @ReferenceSupplier  SqlDbType.NVarChar).Value = txtReference.Text .Parameters.Add( @DateFrom  SqlDbType.DateTime).Value = CDate (TxtFromDate.Text) .Parameters.Add( @DateTo  SqlDbType.DateTime).Value = CDate (TxtToDate.Text) End With con.Open() cmd.ExecuteNonQuery() dgvResult.DataBind() End Using End Using

Manas Mohapatra · Answer

Can you share complete C# code how you are calling stored procedure.

Pankaj Kumar Choudhary · Answer

Hello! Adeb if you don't want to send any data then you can use Null values for Stored Procedure Parameters. So assign a default Null values for parameters @StockInNumber nvarchar(13)=Null @StockInType tinyint=Null @SupplierName nvarchar(50)=Null @Status tinyint=Null @Reference nvarchar(50)=Null @Identifier nvarchar(15)=Null @DateFrom datetime=Null @DateTo datetime=Null @ReferenceSupplier Nvarchar(50)=Null

AbedElHamid AlWahidy · Answer

i want run it in null value i have this code cmd.Parameters.Add( New SqlParameter( @StockInNumber  SqlDbType.NVarChar 13 ParameterDirection.Input True  0 0   DataRowVersion. Default  ValueToNull(StockInNumber))) but there are error in ValueToNull

Ankur Mistry · Answer

If you comment where condition and run 'exec sp_name'.