how to write select statement for this procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

SQL

alter proc getdata
@subcompname varchar(50)
as
begin

declare @liststr varchar(max)
select @liststr = coalesce(@liststr+',','')+subcompname from IRNUMBER
declare @subcomp varchar(max)=@liststr
declare @stringval varchar(100)
declare @output table([stringval] varchar(50))
while LEN(@subcomp)>0
begin
set @stringval = LEFT(@subcomp,ISNULL(nullif(charindex(',',@subcomp)-1,-1),len(@subcomp)))
set @subcomp= SUBSTRING(@subcomp,ISNULL(nullif(charindex(',',@subcomp),0),len(@subcomp))+1,len(@subcomp))
insert into @output([stringval]) values(@stringval)
end
select stringval from @output where(substring(stringval,1,10)=@subcompname)

iam having data like this

sno           subcompname            iRnumber           Drawingnumber
1              c1,c2,c3                 001                    1

2               c200,c444,c555          002                     2

3              c777,c878,c999           003                      3

when user enters 'c777' iam getting third record correctly as "stringval c777".
and i also want iRnumber and Drawingnumber............how to write select statement......
please help me..........

Posted 12-May-15 0:34am

vemsoft

Updated 12-May-15 0:46am

Andy Lanng

v2

Add a Solution

Comments

Andy Lanng 12-May-15 6:50am

Why do you need to turn this into a select statement? Why not execute the proc from c#?

I could understand if you want to do most of the processing in c#, but that's not what you're asking for.

I cannot imagine any select statement that wont use stored procs and function to achieve this, but maybe there is someone better than I who can.

vemsoft 12-May-15 6:56am

at present iam getting only subcompname..using this procedure i want irnumber and drawingnumber in this procedure

Andy Lanng 12-May-15 7:05am

You can just add them to your result. Event a stored procedure can return more than a scalar value. I'll try to fit this into a solution

Andy Lanng 12-May-15 7:15am

OK - I get it now, but I need more info.
There is a table called IRNUMBER. is there a column called iRnumber?
how does iRnumber relate to subcompname?
Where does Drawingnumber come from?

vemsoft 12-May-15 7:24am

there exist IRNUMBER table and irnumber and drawing number columns in that table...........

ZurdoDev 12-May-15 8:35am

1. Reply to comment so that the user is notified.
2. Where are you stuck? Just write a sql SELECT statement to get all that you need.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ConnectingKamlesh · Accepted Answer · 2015-05-12T03:48:00

Solution 1

Replace your last select statement with the following :

SQL

declare @var varchar(100)
select @var = stringval from @output where(substring(stringval,1,10)=@subcompname)

select sno, @var as stringval, iRnumber, Drawingnumber from iRnumber where subcompname like '%' + @var + '%'

Posted 12-May-15 3:48am

ConnectingKamlesh

Comments

vemsoft 13-May-15 3:22am

here iam getting duplicate values.....when user enters 'c1' this query is retrieving c1,c122.c1422,c1000 etc......

i want only c1.

ConnectingKamlesh 13-May-15 3:54am

use this :
select sno, @var as stringval, iRnumber, Drawingnumber from iRnumber where subcompname like '%' + @var + ',%'

note : I have only modified the last part of the query (',%')

vemsoft 13-May-15 5:45am

sno subcompname iRnumber Drawingnumber
1 c1,c2,c123 001 1

2 c200,c444,c555 002 2

3 c777,c878,c999 003 3

if i enter c1 iam getting but when i enter c123 iam not getting

ConnectingKamlesh 13-May-15 7:06am

is it possible for you update you table values with trailing comma values.
For e.g. update values in column subcompname as c1,c2,c123, (see the comma in the end).

vemsoft 13-May-15 7:50am

Thank u.......kamlesh.its working....i added comma in the end

ConnectingKamlesh 13-May-15 7:51am

You are welcome. :)

vemsoft 15-May-15 2:37am

@component varchar(50)
declare @var varchar(100)
select @var = stringval from @output where(substring(stringval,1,10)=@subcompname)

select sno, @var as stringval, iRnumber, Drawingnumber from iRnumber where subcompname like '%' + @var + ',%' and component=@component

hai kamlesh when iam passing component in the above query iam not getting this...
please help me.........

ConnectingKamlesh 18-May-15 3:48am

where have you assigned the value in @component and do you have this column in your table ?

vemsoft 19-May-15 2:52am

yes i have column name component....here yhe user selects component...thats why i passed component=@component in where condition....

ConnectingKamlesh 19-May-15 3:39am

where have you assigned the value in @component