September 23, 2011 at 2:32 am
Hi,
I am working on a multiple parameters report out of which there are 4 free search text parameters. The problem is when I include these 4 parameters in the report, it doesnt shows any data even if the free text parameter has a NULL value.
This is the sql code I have created to accomodate one of the free text parameter:
where Commodity like '%' + @Commoditylike + '%' or @Commoditylike Is NULL
Could someone pls tell what is it that I am doing wrong.
Thanks,
Paul
September 23, 2011 at 5:17 am
Fire up profiler and see the REAL statement sent to the server. There are a few gotchas in there ;-).
September 23, 2011 at 5:27 am
Sorry Ninja, I did not get you...do you mean set up a trace ?
September 23, 2011 at 5:31 am
yes.
Then copy the statemenet sent to the server in ssms and debug that. You'll figure out what to do next ;-).
September 23, 2011 at 7:25 am
try this..
where (Commodity like '%' + @Commoditylike + '%') or (Commodity Is NULL)
September 23, 2011 at 7:29 am
Hmm... can you post the full statement? I think I know what you're having problems with.
Reading this is not a bad idea (just to know it exists)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
September 23, 2011 at 7:35 am
Ninja's_RGR'us (9/23/2011)
Hmm... can you post the full statement? I think I know what you're having problems with.Reading this is not a bad idea (just to know it exists)
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries
I have in fact gone through it before but it I am not able to understand it and hence unable to apply it....
Pls find the stored procedure below:
-----------------------------------
ALTER PROCEDURE [dbo].[usp_QM_Details_Sailing]
@From_date [nvarchar](4000),
@To_date [varchar](4000) ,
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000),
@Commoditylike [nvarchar](4000)
WITH RECOMPILE
AS
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
BEGIN
SELECT BLNO, SAILING_DT, ON_BOARD_SAILING_DT, DIRECTION, [F/E], SCNO, SHIPPER,FORWARDER,
CONSIGNEE, NOTIFY, [CONTROL PARTY], [NAYOSE NAME], COMMODITY, ORIGIN, POR, POL, POD, DEST, PDL, TRADE_CD, SAISAN_SERVICE, VESSEL, VOYAGE, LEG, TEU,
(VESSEL + VOYAGE) AS VSLVOY
FROM tbl_TMIS_DATASET
WHERE
SAILING_DT >= Convert(datetime, Convert(varchar, @From_date)) and
SAILING_DT <= Convert(datetime, Convert(varchar, @To_date)) and
DIRECTION IN (select Item From dbo.Split(@direction,',')) AND
[F/E] IN (select Item From dbo.Split(@fe,','))
and ORIGIN IN (select Item From dbo.Split(@Countryofreceipt,','))
and POR IN (select Item From dbo.Split(@POR,',')) and
POL IN (select Item From dbo.Split(@POL,',')) AND
POD IN (select Item From dbo.Split(@POD,',')) AND
DEST IN (select Item From dbo.Split(@Countryofdelivery,',')) and
PDL IN (select Item From dbo.Split(@PDL,',')) and
TRADE_CD IN (select Item From dbo.Split(@Trade,',')) and
SAISAN_SERVICE IN (select Item From dbo.Split(@Service,',')) and
(VESSEL + VOYAGE) IN (select Item From dbo.Split(@VesselVoyage,','))
and (Commodity like '%' + @Commoditylike + '%') or (Commodity Is NULL)
------------------------------------------------------------------------------------
September 23, 2011 at 7:37 am
thundersplash845 (9/23/2011)
try this..where (Commodity like '%' + @Commoditylike + '%') or (Commodity Is NULL)
I have tried this but it still does' nt work. If I remove this parameter from the report, then the report shows data.
September 23, 2011 at 7:41 am
and (Commodity like '%' + @Commoditylike + '%') or (@Commoditylike Is NULL)
September 23, 2011 at 7:50 am
Ninja's_RGR'us (9/23/2011)
and (Commodity like '%' + @Commoditylike + '%') or (@Commoditylike Is NULL)
I tried this one before including some other permutations and combinations but this hard shell doesnt crack !!!!!!!!!!!
September 23, 2011 at 7:54 am
pwalter83 (9/23/2011)
Ninja's_RGR'us (9/23/2011)
and (Commodity like '%' + @Commoditylike + '%') or (@Commoditylike Is NULL)I tried this one before including some other permutations and combinations but this hard shell doesnt crack !!!!!!!!!!!
Please do a print or select to see what's in that value. Maybe it's just empty.
Have you checked the data to make sure you have data in that column, and also for the time period you're working on?
September 23, 2011 at 7:58 am
Ninja's_RGR'us (9/23/2011)
pwalter83 (9/23/2011)
Ninja's_RGR'us (9/23/2011)
and (Commodity like '%' + @Commoditylike + '%') or (@Commoditylike Is NULL)I tried this one before including some other permutations and combinations but this hard shell doesnt crack !!!!!!!!!!!
Please do a print or select to see what's in that value. Maybe it's just empty.
Have you checked the data to make sure you have data in that column, and also for the time period you're working on?
Commodity column is in fact empty but it should still display data for the other columns as I have handled this condition specifically in the sql code:
or @Commoditylike Is NULL
September 23, 2011 at 8:03 am
What's the value you have in that variable?
@Commoditylike
September 26, 2011 at 2:08 am
Ninja's_RGR'us (9/23/2011)
What's the value you have in that variable?@Commoditylike
The value comes from a column called Commodity.
September 26, 2011 at 5:17 am
pwalter83 (9/26/2011)
Ninja's_RGR'us (9/23/2011)
What's the value you have in that variable?@Commoditylike
The value comes from a column called Commodity.
I meant when you run the query and it fails to return anything.
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply