troubleshooting Question

Add a substring in the SQL where clause

Avatar of Angel02
Angel02 asked on
* T-SQLSQL
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
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?
ASKER CERTIFIED SOLUTION
ste5an
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