ALTER procedure [dbo].[countyListings_test] 

@palace         nvarchar(255) = null,
@historicCounty nvarchar(255) = null,
@confidence     nvarchar(255) = null

as 
select @palace as palace
select @historiccounty as historicCounty
select @confidence as confidence
return

exec sp_executesql 
N'exec dbo.countyListings_test @historicCounty,@palace',
N'@historicCounty nvarchar(12),
@palace nvarchar(255)',
@historicCounty=N'Lincolnshire',
@palace=N'Palace'

....
where (palacefield = @palace or @palace is null)
and (historicCountyfield = @historicCounty or @historicCounty is null)
and (confidencefield = @confidence or @confidence is null)
...

quote:

---

Originally posted by stormie

Sorry if I was not clear

My issue is the sp is expecting say 4 parameters, but if I only pass
parameter 2,3,4 because parameter 1 is not needed

parameter 2 is going in the parameter1 slot 3 in the 2 slot and 4 in the three slot.

I dont understand why this is happening

The example if gave was to illustrate how it's going wrong, but you are correct my code does look like
where
a.historicCounty = @historicCounty
and (a.confidence = @certain
or a.confidence = @possible
or a.confidence = @questionable
or a.confidence = @rejected
)


I would be much happier translating Medieval french I think

---

quote:

---

Originally posted by stormie

Thanks for your prompt response

It's getting called from the asp .net web page, but I seem to have the same problem if I run it via sql query analyzer

What I am trying to achieve, is I have checkBox with four items, which can be true or false and I'm using it to filter out the data

In the confidence example, if I only want to see "certain" and "possible" sites, then I just tick those two.

I can work round the issue, by adding in all 4 parameters, but pass a null for the ones that are unchecked.

---

create procedure dbo.myProblem

@param1 varchar(255) =null,
@param2 varchar(255) = null,
@param3 varchar(255) = null

as

select
@param1 as P1,@param2 as P2,@param3 as P3

exec sp_executesql 
N'exec dbo.myProblem @param1 ,@param2, @param3',
N'@param1  nvarchar(255),@param2  nvarchar(255),@param3  nvarchar(255)',
@param1=N'p1',
@param2=N'p2',
@param3=N'p3'

exec sp_executesql 
N'exec dbo.myProblem @param1 , @param3',
N'@param1  nvarchar(255),@param3  nvarchar(255)',
@param1=N'p1',
@param3=N'p3'

exec dbo.myProblem @param1='P1',@param3='P3'

exec sp_executesql 
N'exec dbo.myProblem @param1 ,@param2, @param3',
N'@param1  nvarchar(255),@param2  nvarchar(255),@param3  nvarchar(255)',
@param1=N'p1',
@param2=N'p2',
@param3=N'p3'

like this and see

exec sp_executesql 
N'exec dbo.myProblem @param1 =@param1,@param2 = @param2, @param3 = @param3',
N'@param1  nvarchar(255),@param2  nvarchar(255),@param3  nvarchar(255)',
@param1=N'p1',
@param2=N'p2',
@param3=N'p3'