April 25, 2019 at 7:19 pm
My parameter can be a combination of formats and it can be in two different columns . . dashed/no dashes; or in PD_ID or in Like PD ID
if object_id('TEMPDB..#temp1') is not null
drop table #temp1;
select * into #temp1 from
(select distinct
[PD_ID]
, [LIKE_PD_ID]
, [FLAT_LIKE_PD_ID]
, [FLAT_PD_ID]
from
[ADHOC].[TEST_PDLINK] P
where
P.PD_ID in (@Part)
or p.like_PD in (@part)
or P.Flat_PD-ID in (@part)
or p.Flat_LIKE_PD_DI in (@Part)
) as test
select * from #temp1
April 25, 2019 at 8:55 pm
Are you sure it's a SQL Server's message? Can you post exact text and message ID?
--Vadim R.
April 26, 2019 at 1:27 pm
Can you post the rest of the code? If it says parameter has been defined, that means you're defining a parameter, but we can't see that in what you posted.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 26, 2019 at 2:04 pm
Judging by your other question I suspect this is SQL in an SSRS dataset? SSRS, when using the syntax {Expression} IN (@Parameter) with a multi-value parameter injects the values instead, replaceing the value of @Parameter with the list; so the query isn't actually parametrised. This is in contrast with syntax like {Expression} = @Parameter, where SSRS does use a parametrised query.
Due to this "feature", and the fact that you are referencing @part, multiple times in the WHERE, SSRS is basically falling over itself. I would suggest instead using an SP, and splitting the value using something like delimitedsplit8k_lead (I assume you are on 2014, and not 2016+). So, your SP would look something like this:
CREATE PROC ADHOC.PartSearch @part varchar(8000)
AS
BEGIN
--Not sure why you were using a Temporary table, this is unneeded overhead
SELECT DISTINCT
[PD_ID],
[LIKE_PD_ID],
[FLAT_LIKE_PD_ID],
[FLAT_PD_ID]
FROM [ADHOC].[TEST_PDLINK] AS P
JOIN dbo.DelimitedSplit8K_lead(@Part,',') DS ON P.PD_ID = DS.Item
OR P.like_PD = DS.Item
OR P.Flat_PD - PID = DS.Item --Seems odd to take an ID away from something
OR P.Flat_LIKE_PD_DI = DS.Item;
END;
Then, in SSRS, change the dataset type to a Stored Procedure and select ADHOC.PartSearch (or whatever you ended up calling it).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply