January 13, 2012 at 11:32 pm
Hi friends,
as I said my query is working fine for single value but when localityIds are coming in comma separated 2 values e.g. '2,3' the error is 'unable to convert datatype nvarchar to int. getting values from checkbox list is not a problem here. so first i m sending static values from c#...
@bedrooms is nvarchar variable
... experiment is going on...
Thanks & Regards,
Hem Singh
January 14, 2012 at 4:43 am
Ninja's_RGR'us (1/13/2012)
I work on massive ERP reports. Some of my reports have 20 parameters so you need to get inventive to both get the correct results and with fast speed.The way to do this is create a local temp table like this :
CREATE TABLE #Localities(
LocalityId INT PRIMARY KEY CLUSTERED
)
Then split to localities from your concatenated value into that table.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Then use that table for the in in your query. The reason I use the temp table with PK is that it usually gives the best possible estimates for that filtering. Which then gives a steady performance for the SP.
Dynamic sql will give you the same speed & compile time (roughly), but with the added securities & permission issues that come with it.
thanks friends,
it is solved in sql side now. Special thanks to @Brandie Tarvin & @ninja's_RGR'us . I am working in c# to get selected vals of checkboxlist. giving sql code here. may help to some visitor:
ALTER PROCEDURE [dbo].[Property_Search]
@CityId int,
@LocalityId nvarchar(50),
@PriceFrom money,
@PriceTo money,
@bedrooms nvarchar(20)
AS
BEGIN
-- create temp table for localityIds
CREATE TABLE #hpTemp(
LocalityId int Primary Key Clustered
)
-- Insert comma separated LocalityIds in #hpTemp table one by one
DECLARE @nextstring varchar(100), @deli varchar(100), @index int, @stringval varchar(100);
SELECT @deli = ',', @stringval = @LocalityId+@deli, @index = CHARINDEX(@deli,@stringval);
WHILE(@index <> 0)
BEGIN
set @nextstring = SUBSTRING(@stringval,1,@index-1)
if (@nextstring <>'')
begin
INSERT INTO #hpTemp(LocalityId) VALUES(@nextstring);
end
set @stringval = SUBSTRING(@stringval,@index+1,LEN(@stringval))
set @index = CHARINDEX(@deli,@stringval)
END
-- select values as per multi values of one column i.e. Locality
SELECT p.Id
,(SELECT Name FROM Users WHERE UId=p.UId) AS Name
,City
,Locality
FROM Property p
WHERECityId= @CityId and
(TotalPrice>= @PriceFrom and TotalPrice<= @PriceTo) and
Bedrooms= @bedrooms and LocalityId IN (SELECT LocalityId FROM #hpTemp)
ORDER BY DateCreated DESC;
DROP table #hpTemp
END
There is no need of nvarchar(max), it worked simply. Is there any other easy way to insert comma separated values as I did above. If yes plz let me know.
Thanks a lot.
Thanks & Regards,
Hem Singh
January 14, 2012 at 5:25 am
I would use nvarchar(max) in this case for the simple reason that a single region might have a massive list of ids and that the ids will be longer than 1-2-3 characters later down the road. So nvarchar(50) will likely cause you issues.
I posted this link earlier so you could save and use Jeff's fantastic splitter function.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
The article is long but in the end the code will look something like this :
Insert into #tmp (colname) Select Value FROM dbo.Split(@Parameter, ',')
This is 1 case where you can skim the entire article and just use the final code. It's a very good idea to understand all that's going on in there but not necessary for you at the current time. That article has been 5 000 hours in the making, that's why it's so long and deep ;-).
January 14, 2012 at 6:55 am
Ninja's_RGR'us (1/14/2012)
I would use nvarchar(max) in this case for the simple reason that a single region might have a massive list of ids and that the ids will be longer than 1-2-3 characters later down the road. So nvarchar(50) will likely cause you issues.I posted this link earlier so you could save and use Jeff's fantastic splitter function.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
The article is long but in the end the code will look something like this :
Insert into #tmp (colname) Select Value FROM dbo.Split(@Parameter, ',')This is 1 case where you can skim the entire article and just use the final code. It's a very good idea to understand all that's going on in there but not necessary for you at the current time. That article has been 5 000 hours in the making, that's why it's so long and deep ;-).
ok i will use nvarchar(max) for locality Id. BTW I was saying not to use nvarchar(max) to assign the whole query in a @var then execute it. As suggested by @Brandie Tarvin.
I will try to checkout yr link later as it needs some hours study as I think.
Thanks & Regards,
Hem Singh
January 14, 2012 at 7:08 am
Nothing wrong with nvarchar(max). You just have to learn when and where it's appropriate to use. 😉
January 18, 2012 at 12:26 pm
I'm glad we were able to help.
January 19, 2012 at 11:13 pm
nice Brandie. I have checked yr site... interested journey...
Thanks & Regards,
Hem Singh
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply