CODEWITHSUNDEEP
sql-servert-sql
Sam Bunting
Sam Bunting

Reputation: 965

Use SELECT output variables in stored procedure

I'm fairly new to SQL and have come across an issue where I can't retrieve additional data from a second stored procedure.

I am trying to retrieve data from two tables at once. The person table has a guid which references to the row in the personname table. I need all of the information from both tables.

CREATE PROCEDURE [database].[rsp_person_getitembyid]
    @Id UNIQUEIDENTIFIER
AS
    SELECT 'Person' AS TableName, * 
    FROM person 
    WHERE Id = @Id;

    EXEC [database].rsp_personname NameId;

The inside of rsp_personname is:

CREATE PROCEDURE [database].[rsp_personname]
    @Id UNIQUEIDENTIFIER
AS

    SELECT 'PersonName' AS TableName, * 
    FROM crm_personname WHERE Id = @Id

The error I'm getting is

Error converting data type nvarchar to uniqueidentifier.

I think it is coming from NameId on the final line. The NameId and Id is stored as Guid in both tables.

Any help would be appreciated.

Thanks

Upvotes: 0

Views: 75

Answers (1)

Lamak
Lamak

Reputation: 70638

You should be storing the NameId on a variable, and then using it to call the next sp:

CREATE PROCEDURE [database].[rsp_person_getitembyid]
@Id UNIQUEIDENTIFIER
AS

DECLARE @NameId nvarchar(100);

SELECT @NameId = NameId
FROM person 
WHERE Id = @Id;

EXEC [database].rsp_personname @NameId;

UPDATE

I don't really understand what you want. Seems like a simple JOIN would be enough:

SELECT *
FROM person p
INNER JOIN crm_personname pn
    ON p.Id = pn.Id
WHERE p.Id = @Id;

Upvotes: 3

Related Questions