How to pass Parameter in SQL Server Sp

Posted by Self-Innovator under Sql Server on 12/20/2012 | Points: 10 | Views : 947 | Status : [Member] | Replies : 8
Hi,,
I need to pass the splitted string as a parameter in SQL Stored Procedure please find the exact solu....Below i've my Sp please guide me to achieve my solution...
Hi in my table i have an column called complexion where i've the data as 2,5, i am getting two and 5 seperately i need to pass thw value of 2 nad 5 and i should get the two rows in my dataset...but i am getting two rows with both values as 2,5, only not as 2 & 5 seperately
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

SET @StartingPos= 1

SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='68572b93-16bd-4764-b75f-3aad35fef04b')

WHILE @StartingPos<=LEN(@Test)

BEGIN

SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)

SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)

PRINT @RecSet

SELECT CC.CUSTOMERID,CC.COMPLEXION1,CM.ID FROM CUSTOMEREXPECTATION CC
LEFT OUTER JOIN
COMPLEXION CM
ON CC.COMPLEXION1=@RecSet WHERE CUSTOMERID='68572b93-16bd-4764-b75f-3aad35fef04b'

SELECT @StartingPos=@RecPos+1

END


Join Hands Change lives
Thanks & Regards
Straight Edge Society



Responses

Posted by: Pavanandey on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
can you post your table structure and sample data and required out put

Thanks
Pavan Kumar
Mark Answer if this fits the need

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-Innovator on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
HI PAVANNADAY,

I HAVE TWO TABLES CUSTOMEREXPECTATION AND COMPLEXION I AM STORING THE COMPLEXION ID IN CUSTOMEREXPECTATION TABLE AS (2,3,) IF USER HAS SELECTED 2ND AND 3RD COMPLEXIONS IN THE LIST WHERE I AM STORING 2,3, IN MY CUSTOMER EXPECTATION TABLE NOW I NEED TO GET THE COMPLEXION OF THESE 2 AND 3 VALUES FROM COMPLEXION TALBLE BY JOINNG AS 2-FAIR AND 3-BLACK I NEED TWO GET TWO ROWS AS A DATA SET...I HAVE ATTACHE THE FILE OF MY TABLE STRUCTURE PLS FIND ME SOLN...

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
post your table structure and sample data and required out put

so that narrates much better

Thanks
Pavan Kumar
Mark Answer if this fits the need

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-Innovator on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
TABLE STRUCTURE
select * from complexion

select * from customerexpectation

OUTPUT
ID COMPLEXION
2 Black

ID CUSTID ABC AGE X Y Z COMPLEXION1
127 5e2a9455-4bd0-4ec6-b6fb-0f82004db375 NULL 18 23 3.3 5.2 2,
128 68572b93-16bd-4764-b75f-3aad35fef04b NULL 19 20 6.2 5.6 2,5,


Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Self-Innovator on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
TABLE STRUCTURE
select * from complexion

select * from customerexpectation

OUTPUT
ID    COMPLEXION

2 BlacK


ID     CUSTID                                                COMPLEXION1

127 5e2a9455-4bd0-4ec6-b6fb-0f82004db375 2,3,
128 68572b93-16bd-4764-b75f-3aad35fef04b 2,5,



Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/20/2012 [Member] Bronze | Points: 25

Up
0
Down
implement the solution in the following way

suppose you have COMPLEXION1 2,5,6

now create a temp table in your stored proc

now create a loop in such a way it runs 3 times for the above COMPLEXION1 2,5,6
it picks up 2, 3 and 6 in each loop.

now in each loop insert in the feched records into the temp table.


Thanks
Pavan Kumar
Mark Answer if this fits the need

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandeepmhatre on: 12/21/2012 [Member] Starter | Points: 25

Up
0
Down
http://sandeepmhatre.blogspot.com/2012/12/comma-seperated-values-to-stored.html

Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shanky11 on: 12/22/2012 [Member] Starter | Points: 25

Up
0
Down
table details
create procedure prcexample
(
@name varchar(20),
@age int ,
@address varchar(30)
)
as
begin
insert into details (@name,@age,@address) values('shankr',12,.address u want to enter')
end

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response