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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply