asked on

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?

ste5an

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.

Angel02

ASKER

Modify the function (which one?) or add a new function?

Can you elaborate your response please?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Angel02

ASKER

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!