How do i get around this "parameter has already been defined" error?

Question

How do i get around this "parameter has already been defined" error?

SSCrazy

Points: 2257

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Are you sure it's a SQL Server's message? Can you post exact text and message ID?

--Vadim R.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Thom A SSC Guru Points: 99126 More actions · Answer 3

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