CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012
VSO
VSO

Reputation: 12636

SQL select result of stored procedure into a comment

I am return a result set from a stored procedure (multiple columns). I am able to populate values for columns from functions (getContactDisplayName), e.g.:

SELECT
    (SELECT [dbo].getContactDisplayName([Form_Response_ID])) AS Submitted_By
FROM 
    foo_Table;

I would like to do the exact same thing with a stored procedure (sp_GetFormAnswer), e.g.:

SELECT 
    (SELECT EXEC sp_GetFormAnswer @FieldName = 'Submission_Data') AS Answer
FROM 
    foo_Table;

Is this possible? If so, why not? What are my alternatives? I do have to use a stored procedure, as that stored procedure calls dynamic SQL (which uses exec, which functions cannot do).

Note - the code is tested, populating columns from functions works fine, as does the stored procedure, on its own. Just not inside the select.

EDIT: My actual, full, working, stored procedure (sorry, it's not on the test db):

ALTER PROCEDURE dbo.crds_sp_GetFormAnswer 
    @FieldName varchar(max), 
    @TableName varchar(max), 
    @PrimaryKeyColumnName varchar(max), 
    @DataRecordId int
AS
    DECLARE @sql nvarchar(max)

    SELECT @sql = 'SELECT ' + @FieldName +
              ' FROM ' + @TableName + 
              ' WHERE ' + @PrimaryKeyColumnName + '  =  ' + STR(@DataRecordId) + ';';

    EXEC sp_executesql @sql, 
            N'@FieldName varchar(max), @TableName varchar(max), 
              @PrimaryKeyColumnName varchar(max), @DataRecordId int', 
            @FieldName = @FieldName, @TableName = @TableName, 
            @PrimaryKeyColumnName = @PrimaryKeyColumnName, @DataRecordId = @DataRecordId;
GO

--test
EXEC crds_sp_GetFormAnswer @FieldName = 'Submission_Data', 
                           @TableName = 'cr_Submissions', 
                           @PrimaryKeyColumnName = 'Submission_ID', 
                           @DataRecordId = 15;

In short, my end goal is to use dynamic SQL to populate a field. I have to use dynamic SQL because I am selecting from any number of tables. Business req was that use creates report to write to any table in the db if they have permissions (which is a separate topic).

enter image description here

NOTE: I want to pass different data from each row into the stored procedure and have a different output for each row.

Upvotes: 0

Views: 1235

Answers (2)

Bernard Walters
Bernard Walters

Reputation: 391

Instead of creating a temp table(As some of the comments say), change the returned result set.

EXEC usp_Get_TwoColumnsData
WITH RESULT SETS
(
    (
       NewReturnedColumnName1 VARCHAR(1337) NOT NULL,
       NewReturnedColumnName2 VARCHAR(1337) NOT NULL
    )
)

Output

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

I will just assume that your dynamic sql posted is valid (see my comment on the OP). If you are on a version earlier than 2012 you can do this using a table variable or temp table. Here is how you can do this using a table variable. 

declare @Result table
(
    Result varchar(max) --or whatever datatype makes sense here
)

insert @Result
EXEC crds_sp_GetFormAnswer @FieldName = 'Submission_Data', @TableName = 'cr_Submissions', 
                           @PrimaryKeyColumnName = 'Submission_ID', @DataRecordId = 15;

Upvotes: 1

Related Questions