April 11, 2012 at 11:07 am
hi
i have 1 table
code item1
a pen
a pencil
b item3
b item4
i need to select any item based on code in just 1 column.
now item3 contains pen and pencil.
so i supply @item3 it gives me
a pen
a pencil
,what i need is ,if @sign is there,it should return b item3
how to do that?
April 11, 2012 at 11:17 am
i read your post twice, and I cannot visualize it;
is this your what your data would look like?
CREATE TABLE [dbo].[MYTABLE] (
VARCHAR(10) NULL,
[ITEM1] VARCHAR(30) NULL)
INSERT INTO [dbo].[MYTABLE]
SELECT 'a','pen' UNION ALL
SELECT 'a','pencil' UNION ALL
SELECT 'b','item3' UNION ALL
SELECT 'b','item4'
what do you mean 'item3' contains a pen and a pendcil?
Lowell
April 11, 2012 at 11:25 am
no,
this is select statement in my sp,
i need to supply one parameter @name
this contains 2 item suppose pen and pencil,
and it gives me that 2 item ,
now in table w have values like this
a pen
a pencil
b name
so ,i dont want the items it contains,i want result as (b , name) when i supply @ in sp
April 11, 2012 at 11:42 am
hbtkp (4/11/2012)
no,this is select statement in my sp,
i need to supply one parameter @name
this contains 2 item suppose pen and pencil,
and it gives me that 2 item ,
now in table w have values like this
a pen
a pencil
b name
so ,i dont want the items it contains,i want result as (b , name) when i supply @ in sp
Details.
Remember noone here is looking over your shoulder, seeing what you see;
show us exactly hat you are trying to do, no pseudo code, and we can help better.
so now i'm thinking your question is like this:
EXECUTE sp_mySearch 'pen,pencil,marker'
and you are asking how to turn the comma delimited parameter into a table, so you cna search?
I'f i'm on target, search for the DelimitedSplit8K function.
to get the results, it's very simple:
SELECT *
FROM MyTable
WHERE myColumn IN (SELECT Item
FROM dbo.DelimitedSplit8K(@myParameter,',') myf
)
again, if you need more help, you have got to provide details; i'm just doing a best guess based on experience here.
Lowell
April 11, 2012 at 11:48 am
ok.
le tme give you example.
exec spname
@name = @item3
this is how i need to execute my sp.
now @item3 is a group , it contains pen and pencil
in my table i have values like this
1 pen
1 pencil
2 item3
so , for group it has code 2 for single it has code 1.
when i execute @item3
it gives me whatever it has inside ,like 1 pen
1 pencil
i want third row 2 ,item3 as a my result
i hope you get it
April 11, 2012 at 11:51 am
i didn't even read your reply.
you have got to do your part first.
help us help you!
1. provide a CREATE TABLE example representative of what you are using.
2. provide INSERT INTO that table with some sample data(i did it in an earlier post, it's not hard!)
3. show us the EXACT code you have tried so far.
Lowell
April 11, 2012 at 11:52 am
hbtkp (4/11/2012)
ok.le tme give you example.
exec spname
@name = @item3
this is how i need to execute my sp.
now @item3 is a group , it contains pen and pencil
in my table i have values like this
1 pen
1 pencil
2 item3
so , for group it has code 2 for single it has code 1.
when i execute @item3
it gives me whatever it has inside ,like 1 pen
1 pencil
i want third row 2 ,item3 as a my result
i hope you get it
Actually, no. How do we know what Item3 contains? Nothing you have provided shows us this relationship.
April 11, 2012 at 11:52 am
You say that you are supplying one parameter, but you don't say what value you're supplying to that parameter.
You say that name contains pen and pencil, but the data provided does not support assertion. There is no relation between pen, pencil, and name in the data provided.
You mention that you supply @, but that is an invalid variable/parameter name. If you mean that you are supplying '@', you need to indicate that it is a string by providing the single quotes.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2012 at 11:55 am
@ stands for group
i have group name item3 ,which i am supplying in my sp.
now this group contains pen ,pencil .so in my table i have code for everything
for pen,pencil and item3
i want only item3 code should be getting when i run query
April 11, 2012 at 12:06 pm
hbtkp (4/11/2012)
@ stands for groupi have group name item3 ,which i am supplying in my sp.
now this group contains pen ,pencil .so in my table i have code for everything
for pen,pencil and item3
i want only item3 code should be getting when i run query
But nothing you have shown us tells us that pen and pencil are part of Item3.
April 11, 2012 at 12:09 pm
hbtkp (4/11/2012)
@ stands for group
No, @ stands for the beginning of a variable/parameter definition. '@' may stand for group, but not @.
i have group name item3 ,which i am supplying in my sp.
now this group contains pen ,pencil .so in my table i have code for everything
No, you don't have everything. You are missing the relationship. You table has three completely unrelated items.
The ReadMyMind API is still vaporware, so the database has no way of knowing what's related unless you specifically tell it, and you haven't yet provided that information.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2012 at 12:09 pm
assume that it is part of item 3 ,it is not in database ,it is in application
April 11, 2012 at 12:15 pm
hbtkp (4/11/2012)
assume that it is part of item 3 ,it is not in database ,it is in application
How is the database supposed to know that Item3 is composed of pen and pencil? It isn't clairvoyant, it has to be told.
April 11, 2012 at 12:27 pm
it doesnt matter , how do i get those value in select stm
April 11, 2012 at 12:31 pm
hbtkp (4/11/2012)
it doesnt matter , how do i get those value in select stm
lol . if it doesn't matter, well...whatever.
I'll ask again for some specific code: show us what you've tried, even if it's not working.
Lowell
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply