May 2, 2002 at 5:30 pm
Hi:
I have an ASP application where a user identifies records to mark using checkboxes.
I'm trying to use a stored procedure to do the update. There can be multiple selections, but a limited number, so I'd like to use the 'IN' range check.
The datatype of the field being searched is INT
However, I build the value range using a variant datatype via ASP, so the variable ends up looking like
strValueRange = "245, 322, 289"
So, when I defined an input parameter for the stored procedure, I started out giving it a VARCHAR datatype.
When tested, SQL complained:
'Error converting data type varchar to int.'
Which makes some sense. But I doubt that I can build the necessary data range in ASP using anything but a variant/string.
I'm guessing that this is one of those times where stored procedures are not the answer. But I wanted to ask the question in case I've overlooked something, or if there's something I'm unaware of.
Thanks for your time.
JK
May 2, 2002 at 5:53 pm
No, you cant pass it as a string. You could guess at the max number of parameters you want to test and have that may inputs to your proc. Another method would be to pass it as an XML doc and use OpenXML on the server. Building the sql on the client and submitting is not the worst idea in the world, depends on how heavily you're committed to using procs for everything.
Andy
May 2, 2002 at 6:27 pm
Another way would be to use CHARINDEX to parse it into a temp table like so
CREATE TABLE #tblSel (
sels int not null
)
--You will need to test this section, and may need to use a cast.
WHILE CHARINDEX (',',@strValueRange) > 0
BEGIN
INSERT INTO #tblSel (sels) VALUES LEFT(@strValueRange,CHARINDEX (',',@strValueRange) -1)
SET @strValueRange = RIGHT(@strValueRange, LEN(@strValueRange) - CHARINDEX (',',@strValueRange))
END
INSERT INTO #tblSel (sels) VALUES (@strValueRange)
SELECT * FROM tblX WHERE colX IN (SELECT sels FROM #tblSel)
Thus no dynamic sql needed and no dealing with XML although it might be a better solution.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 3, 2002 at 12:07 am
That's a very nice approach. I really should have thought of that myself.
Thanks for the help.
JK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply