Here is my stored procedure:
CREATE PROC [SPmainReport]
@startDate date = null,
@endDate date = null,
@customerName varchar = null,
AS
SELECT Distinct
VI.Code as CustomerCode, VI.Name as CustomerName, VI.Area as CustomerArea, VI.[Address] as [address], CP.ProductName as ProductName, CP.ProductQuantity as Quantity
from
VendorTrading VT inner join CustomerProducts CP on VT.Id = CP.VendorTradingId inner join VendorInfo VI on VT.VendorId = VI.Id
where
(VT.Tradedate between isnull(@startDate,VT.Tradedate) and isnull(@endDate,VT.Tradedate))
and VI.Name = ISNULL(@customerName, VI.Name)
On execution it doesn't return any value but if I execute this query:
SELECT Distinct
VI.Code as CustomerCode, VI.Name as CustomerName, VI.Area as CustomerArea, VI.[Address] as [address], CP.ProductName as ProductName, CP.ProductQuantity as Quantity
from
VendorTrading VT inner join CustomerProducts CP on VT.Id = CP.VendorTradingId inner join VendorInfo VI on VT.VendorId = VI.Id
where
(VT.Tradedate between isnull(@startDate,VT.Tradedate) and isnull(@endDate,VT.Tradedate))
and VI.Name = ISNULL('John', VI.Name)
It return exactly required data. I am totally confused why it is happening. There is no difference at all. I am making sure scripts are running on same database and it also contains perfect data. here is SP execution script:
USE [E:\SANDWICH3\ABC\BIN\DEBUG\DATABASE\ABC.MDF]
GO
DECLARE @return_value Int
EXEC @return_value = [dbo].[SPmainReport]
@startDate = '2015-12-25',
@endDate = '2015-12-25',
@customerName = N'John'
SELECT @return_value as 'Return Value'
GO
One more strange I have noticed is that If I modify this SP and limit conditions with just DATES, not name. It works fine then. I am working on Visual Studio 2013 Interface of SQL server. Not management studio (In case it does matter)
You can use the FETCH FIRST 1 ROW ONLY clause in a SELECT statement to ensure that only one row is returned. This action prevents undefined and unpredictable data from being returned when you specify the INTO clause of the SELECT statement.
No, an empty return statement is optional. The only time it would be required is if you needed to return a value from the procedure, then you would need to either create a result set with a select statement or return a value. In cases like yours the return statement is purely optional.
Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch. Stored procedures offer no performance advantage whatsoever.
In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.
In SQL Server, always use a length parameter when using varchar()
and char()
and related types:
CREATE PROCEDURE SPmainReport (
@startDate date = null,
@endDate date = null,
@customerName varchar(255) = null
)
BEGIN
. . .
END;
Most customer names probably have more than one character.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With