troubleshooting Question

Add a substring in the SQL where clause

Avatar of Angel02
Angel02 asked on
4 Comments1 Solution99 ViewsLast Modified:
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

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


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?
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros