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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.