January 30, 2009 at 2:22 pm
I am running a stored procedure to retrieve records with a matching name.
However, when there are two records with the same name, only one record is returned.
There is probably an obvious answer, but I would appreciate some input. How do I modify the following stored procedure to get multiple results?
Here is a simplified version of my code:
CREATE TABLE testname (
personID int identity(1,1),
personname varchar(20)
)
INSERT INTO testname
(personname)
SELECT 'x'
UNION
SELECT 'name'
INSERT INTO testname
SELECT 'NAME'
select personid from testname where personname = 'name'
-- 2 records
--Using my stored procedure I only get the one personid returned to me. How do I get multiple records returned to me?
CREATE PROCEDURE prcTestLookup
(@Name varchar(20) = NULL,
@PersonID int OUTPUT
)
AS
SET NOCOUNT ON
BEGIN
declare @sql nvarchar(4000),
@params nvarchar(4000)
SELECT @sql = 'SELECT @PersonID = PersonID FROM testname WHERE 1 = 1 '
IF @Name IS NOT NULL
SELECT @sql = @sql + ' AND PersonName = ''' + @Name + ''''
--print @sql
SELECT @params = ' @Name varchar(20),
@PersonID int OUTPUT'
Exec sp_executesql @sql, @params, @Name, @PersonID OUTPUT
SET NOCOUNT OFF
END
GO
--I only get personid 3 returned to me.
declare @personid int
exec prctestlookup 'name', @personid output
select @personid
January 30, 2009 at 2:39 pm
It is not a sp_executesql problem.
Your procedure is only passing back an output parameter of @PersonID,
you can only put 1 value in a parameter.
you need to restructure this call to return a recordset to the calling app/Stored procedure.
Structuring a query like this will always stuff the value of the Last row returned into the parameter.
Select @param = value
From Mytable
where Something = 'this'
January 30, 2009 at 2:47 pm
Yeah, that's what I was figuring was going on. But I don't know how to retrieve multiples. I didn't want to have to loop it based on a record count. That would get me a lashing from the !rbar crowd for sure.
January 30, 2009 at 3:13 pm
My question is why do you need it in a parameter.
Why not use a recordset?
And if you need it in a parameter there are ways.
January 30, 2009 at 3:19 pm
Will the procedure return a record set? And if so, how can I do subsequent queries off of that returned record set?
I want to keep this as a procedure because I have encrypted/hashed data that I'm selecting against. My procedure "unravels" the security so that I can select against the data. (I didn't bother putting that in my sample for brevity's sake.)
January 30, 2009 at 3:46 pm
Its not the easiest way, to go about it because I don't know the whole problem your trying to solve, but for this specific example you can just return all Id's that match.
and insert them into a temp table in the calling procedure.
and use that temp table to join to your resultset
CREATE PROCEDURE prcTestLookup
( @Name varchar(20) = NULL
)
AS
SET NOCOUNT ON
BEGIN
declare @sql nvarchar(4000),
@params nvarchar(4000)
SELECT @sql = N'SELECT PersonID FROM testname WHERE 1 = 1 '
IF @Name IS NOT NULL
SELECT @sql = @sql + N' AND PersonName = ''' + @Name + ''''
--print @sql
SELECT @params = N' @Name varchar(20)'
Exec sp_executesql @sql, @params, @Name
END
GO
Create table #personID (PersonID INT)
Insert into #PersonID (PersonID)
exec prcTestLookup
January 30, 2009 at 3:51 pm
Aha! This is what I wasn't doing:
Insert into #PersonID (PersonID)
exec prcTestLookup
I'm pretty sure that should work for me.
Thank you!!
March 3, 2009 at 11:27 am
Not sure why you are using dynamic sql here - it is not recommended (you may want to lookup SQL Injection for more info).
Will this not work for you?
CREATE PROCEDURE prcTestLookup
( @Name varchar(20) = NULL
)
AS
SET NOCOUNT ON
BEGIN
SELECT PersonID
FROM Testname
WHERE @Name IS NULL OR PersonName = @Name
END
GO
The "WHERE" clause can be done in a number of different ways. For ex:
WHERE PersonName = ISNULL(@Name, PersonName)
HTH.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy