CODEWITHSUNDEEP
t-sqlexecprocedure
Abyssul
Abyssul

Reputation: 65

Calling Stored Procedure on Column TSQL

Here is my situation. I know there must be a simple answer, but I am just not well versed in TSQL to know how. Below I have the main query of a stored procedure that selects the data I need. I have it working so far except that I need to call a seperate stored procedure called GetRecordMediaById where I feed it the Id from the PhotoId column, and it selects the BLOB data from the appropriate database which then needs to be its own column in the final query or replace the original PhotoId column.

I have no clue how to go about this. I've tried implementing temp tables, but I could never even get it to execute.

Here is my code:

ALTER PROCEDURE [dbo].[GetRollCallData] 
@Ids        VARCHAR(255),
@LexiconId  INT,
@UUID       UNIQUEIDENTIFIER,
@ReadOnly   INT
AS


DECLARE @TableCode INT
SET @TableCode = 58
EXEC InsertInSelectionCache @Ids, @UUID, @TableCode, 0
WITH DOACTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY [File].Id ORDER BY CustomRecordsetId DESC) AS RowNumber, [File].*, FileType2Lexicon.Label as FileTypeLabel, [People].DefaultPhone, [People].InvertedName, CustomFieldValue.Value as DateofArrest
    FROM FileType2Lexicon, SelectionCache, [People], [File]
    INNER JOIN [CustomRecordSet]
    ON [CustomRecordset].RecordId = [File].Id
    INNER JOIN CustomFieldValue
    ON  [CustomRecordset].Id = CustomFieldValue.CustomRecordsetId
    INNER JOIN [CustomField2Lexicon]
    ON CustomField2Lexicon.CustomFieldId = CustomFieldValue.CustomFieldId
    WHERE   [File].Id = SelectionCache.RecordId
    AND SelectionCache.UUID = @UUID
    AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
    AND     [File].Id <> 0 
    AND     [File].FileTypeId = FileType2Lexicon.FileTypeId 
    AND     FileType2Lexicon.LexiconId = @LexiconId
    AND     [File].ClientIdString = [People].ClientIdString
    AND     CustomFieldValue.Value <> ''
    AND     CustomField2Lexicon.Label = 'Date of Arrest'),


PHOTOCTE AS(
SELECT  [File].Id, CustomFieldValue.Value as PhotoId
    FROM FileType2Lexicon, SelectionCache, [People], [File]
    INNER JOIN [CustomRecordSet]
    ON [CustomRecordset].RecordId = [File].Id
    INNER JOIN CustomFieldValue
    ON  [CustomRecordset].Id = CustomFieldValue.CustomRecordsetId
    INNER JOIN [CustomField2Lexicon]
    ON CustomField2Lexicon.CustomFieldId = CustomFieldValue.CustomFieldId
    WHERE   [File].Id = SelectionCache.RecordId
    AND SelectionCache.UUID = @UUID
    AND SelectionCache.TableCode = @TableCode -- this is the code for File table  
    AND     [File].Id <> 0 
    AND     [File].FileTypeId = FileType2Lexicon.FileTypeId 
    AND     FileType2Lexicon.LexiconId = @LexiconId
    AND     [File].ClientIdString = [People].ClientIdString
    AND     CustomFieldValue.Value <> ''
    AND     CustomField2Lexicon.Label = 'Booking Photo')

SELECT DOACTE.*, PHOTOCTE.PhotoId
FROM DOACTE
INNER JOIN 
PHOTOCTE
ON DOACTE.Id = PHOTOCTE.Id
WHERE DOACTE.RowNumber = 1

EDIT:

Solution for me was to create a scalar function that resolves the Id in the BLOB database and returns the BLOB data.

SELECT DOACTE.*, dbo.GetImagebyId(PHOTOCTE.PhotoId) as Photo,
            FROM DOACTE
            INNER JOIN 
            PHOTOCTE
            ON DOACTE.Id = PhotoCTE.Id
            WHERE DOACTE.RowNumber = 1

Upvotes: 1

Views: 565

Answers (1)

shakedown7
shakedown7

Reputation: 115

You can declare a @table_variable and insert the results from "EXEC InsertInSelectionCache @Ids, @UUID, @TableCode, 0" into the table variable.

Then you can join to the @table_variable in the final query.

See here for examples: How to return temporary table from stored procedure

Upvotes: 1

Related Questions