Retrieving multiple records in a single Sp

Posted by Self-Innovator under Sql Server on 10/30/2012 | Points: 10 | Views : 3241 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
How to get multiple records from a single sp
Ex:

CREATEproc [C244570_marriage].[sp_getpersonaldetails]
@customerid nvarchar(max)
as
begin 
select * from personalinfo where CustomerId=@customerid
end

this retruns only the records whose matching the customer id...I need to get UserNames also
CREATEproc [C244570_marriage].[sp_getpersonaldetailsby uname]
@UserName nvarchar(max)
as
begin
select * from personalinfo where UserName=@UserName
end

instead of creating another sp how can i achieve at both results in a single sp..
Find me soln.

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Vasanthmvp on: 10/30/2012 [Member] Starter | Points: 25

0	Hi, The main use of Stored Procedure itself is to maintain more than one sql query at a time. Here, you can include both queries into a single SP. and use sql data reader in the code behind of aspx page. Reader first returns the first data set. reader.NextResult() returns the next result. To know how to retrieve more than one dataset using sqldatareader. The following article solves your issue: http://www.dotnetfunda.com/articles/article1566-how-to-work-with-multiple-result-sets-using-sqldatareader.aspx Awesome Coding !! :) Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
The main use of Stored Procedure itself is to maintain more than one sql query at a time.
Here, you can include both queries into a single SP. and use sql data reader in the code behind of aspx page.
Reader first returns the first data set. reader.NextResult() returns the next result.
To know how to retrieve more than one dataset using sqldatareader.
The following article solves your issue:
http://www.dotnetfunda.com/articles/article1566-how-to-work-with-multiple-result-sets-using-sqldatareader.aspx

Awesome Coding !! :)

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

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

0	I need sp provide me the Sp which can handle to retrieve different results based on the Parameters which we passed.. I need run the below two queries in my sp where i may pass any one values at a time....CustomerId or UserName select * from personalinfo where CustomerId=@CustomerId select * from personalinfo where UserName=@UserName Join Hands Change lives Thanks & Regards Straight Edge Society Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

I need sp provide me the Sp which can handle to retrieve different results based on the Parameters which we passed..
I need run the below two queries in my sp where i may pass any one values at a time....CustomerId or UserName

select * from personalinfo where CustomerId=@CustomerId

select * from personalinfo where UserName=@UserName

Join Hands Change lives
Thanks & Regards
Straight Edge Society

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

Posted by: Naraayanan on: 10/30/2012 [Member] Starter | Points: 25

0	Hi, Use Or Condition in your SP. Regards, Lakshmi Naraayanan.S http://dotnettechrocks.blogspot.in/ http://abaprocker.blogspot.com/ Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Vasanthmvp on: 10/30/2012 [Member] Starter | Points: 25

0	Hi, Try this way: If you want to pass anyone value to the SP set the other value to some default known value or null value. 1. Pass the two values (CustomerID and UserName) from the code behind. Like: i) Incase where we want first query to be executed. select * from personalinfo where CustomerId=@CustomerId Anyway we will pass the @CustomerID from code behind to execute this. At the same time pass some known value to @UserName like "xyz" (let us assume) or null (not sure we can pass null value to SP) ii) Incase where we want to execute second query select * from personalinfo where UserName=@UserName Anyway we will pass the @UserName from code behind to execute this. At the same time pass some known value to @CustomerID like -1 (let us assume) or null (not sure we can pass null value to SP) 2. Include a If condition in the SP. @CustomerID int, @UserName varchar(50) If(@CustomerID != -1) // or != null begin select * from personalinfo where CustomerId=@CustomerId end Else If(@UserName != "xyz") // or !=null begin select * from personalinfo where UserName=@UserName end The above procedure is much useful if you want to retrieve data from two different tables (2 sql queries under one SP). If it is from the same table just include an OR condition. CustomerId = @CustomerID or UserName = @UserName This might help you. Regards, Awesome Coding !! :) Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,
Try this way:
If you want to pass anyone value to the SP set the other value to some default known value or null value.
1. Pass the two values (CustomerID and UserName) from the code behind.
Like: i) Incase where we want first query to be executed.
select * from personalinfo where CustomerId=@CustomerId
Anyway we will pass the @CustomerID from code behind to execute this. At the same time pass some known value to @UserName like "xyz" (let us assume) or null (not sure we can pass null value to SP)

ii) Incase where we want to execute second query
select * from personalinfo where UserName=@UserName
Anyway we will pass the @UserName from code behind to execute this. At the same time pass some known value to @CustomerID like -1 (let us assume) or null (not sure we can pass null value to SP)

2. Include a If condition in the SP.

@CustomerID int,

@UserName varchar(50) 

If(@CustomerID != -1) // or != null

begin

select * from personalinfo where CustomerId=@CustomerId

end

Else If(@UserName != "xyz") // or !=null

begin

select * from personalinfo where UserName=@UserName

end

The above procedure is much useful if you want to retrieve data from two different tables (2 sql queries under one SP).
If it is from the same table just include an OR condition. CustomerId = @CustomerID or UserName = @UserName

This might help you.
Regards,

Awesome Coding !! :)

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

Posted by: Sourabh07 on: 10/31/2012 [Member] Starter | Points: 25

0	hi... try this sp... CREATEproc [C244570_marriage].[sp_getpersonaldetails] ( @customerid nvarchar(max), @UserName varchar(100) ) as begin select distinct * from personalinfo where CustomerId=@customerid or UserName=@UserName end Sourabh07 Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

hi...

try this sp...

CREATEproc [C244570_marriage].[sp_getpersonaldetails]

(

@customerid nvarchar(max),

@UserName varchar(100)

)

as

begin 



   select distinct * from personalinfo where CustomerId=@customerid or UserName=@UserName 



end

Sourabh07

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

Latest Posts