February 26, 2011 at 12:44 am
Hi
i am trying to retrieve output data last name using the stored proc
CREATE PROCEDURE dataretrivel
@firstname varchar(100),
@lastname varchar(100) output
as
select @lastname=lastname
from Person.Contact
where FirstName=@firstname
i want to get all the last names based on the first name 'james'. but when i execute below query it returns only one data or row
Can any body let me know how to go about .
First Name 'James' is having around 25 rows of data
DECLARE @Last varchar(100)
EXEC dataretrivel
@FirstName='james',
@lastname=@last output
select last=@last
i want output data like below
last
------
walter
ponting
Thanks,
Praveen
February 26, 2011 at 1:06 am
Do you want to return all last names in a single return field or do you want to return a set of all last names ( row based ) ??
If you would use your example (corrected)
select @Last=last
You would only get a single value returned for your output parameter.
select last from t where ..
would return the set.
You could use something like this to return all names in a single output value
SELECT @Last= (selectLast + '|'
FROM T
WHERE first = @first
FOR XML PATH('')
)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 27, 2011 at 2:20 am
praveensc2003 (2/26/2011)
i am trying to retrieve output data last name using the stored proc...
CREATE PROCEDURE
dbo.DataRetrieval
(
@FirstName VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
;
SELECT LastName
FROM AdventureWorks.Person.Contact
WHERE FirstName = @FirstName
;
END
GO
EXECUTE dbo.DataRetrieval
@FirstName = 'James'
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply