Add a substring in the SQL where clause

The where clause in my SQL statement is tricky. I need to call a function to get the where clause. But I need to send a column from the table to the function as a parameter. I am not sure what is the best way to do it.

The SQL statement is executed in a loop (5 times for 5 weeks)

DECLARE @currDate as datetime
DECLARE @currWk as int, @lastWk as int
SET @currDate = cast(Convert(varchar(10),GETDATE(),101) as datetime)
SET @currWk = DATEPART(wk, @currdate)
SET @lastWk = @currWk + 5


While @currWk < @lastWk
BEGIN

INSERT into #tempTable
SELECT [AWBILL], dbo.fn_get_current_ship('01/24/2018', [SHIPDATE]) as [SHIPON],[FARM],[FLOWER],[NAME],[UNITS],[UOM],[BOXES],([COST]) AS [COST],[MYID] FROM #tb_shipping  WHERE dbo.fn_getRealShipQuery('US',(CAST(CONVERT(char(10), dbo.fn_get_current_ship('01/24/2018', [SHIPDATE]), 102) AS datetime))) AND ([COUNTRY]='US')

END

fn_get_current_ship returns a date
fn_getRealShipQuery returns a string
Eg: (SUBSTRING(DATENAME(weekday,[SHIPDATE]), 1, 2) ='Fr') AND ([TYPE]='C')

I need to include this string in the where clause. But I can obtain this string based on the [SHIPDATE] column.

Is there a way I can do this in a single query and avoiding another loop?
Angel02Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
The simple solution is to create a new function, which takes the country,ship and type as parameter, run your test and returns a 1 for a match and a zero for a mismatch.

CREATE FUNCTION getRealShipQuery(@Country AS CHAR(2), @Shipdate DATE, @Type CHAR(1))	  
RETURNS BIT
AS
BEGIN
    DECLARE @Result BIT;

    -- you need to apply your orignal functions code.
    SET @Result = IIF(SUBSTRING(DATENAME(WEEKDAY, @Shipdate), 1, 2) ='Fr') AND (@Type ='C'), 1, 0);
    RETURN @Result;
END;

Open in new window

So that you can change your WHERE to

WHERE dbo.fn_getRealShipQuery('US', [SHIPDATE], [TYPE]) = 1 AND ([COUNTRY]='US') ;

Open in new window

But calling functions per row is pretty expensive, thus your query will run slower than needed. Depending on your existing functions you should consider to use their code directly in your query.
0
 
ste5anSenior DeveloperCommented:
You need to modify your function to accept the shipdate column as parameter and return eiter 1 or 0 and then you can test it for this value.

With a signature like dbo.fn_Test(@country AS CHAR(2), @Shipdate DATE).

Using functions like this has a severe performance drawback. Thus you should conisder expanding the functions code to the actual query.

p.s. prior post was nonsense, cause dynamic SQL cannot refer to your actual rows colmn.
0
 
Angel02Author Commented:
Modify the function (which one?) or add a new function?

Can you elaborate your response please?
0
 
Angel02Author Commented:
I considered " Depending on your existing functions you should consider to use their code directly in your query."
And figured out a way to do this directly in the query. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.