sp_executesql and selecting multiple records

  • 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

  • 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'

  • 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.

  • 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.

  • 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.)

  • 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

  • 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!!

  • 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