Retrieving multiple records in a single Sp

Posted by Self-Innovator under Sql Server on 10/30/2012 | Points: 10 | Views : 2152 | Status : [Member] | Replies : 5
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response