dynamic subquery within a sproc select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

dynamic subquery within a sproc select

Author

Topic

CarlinAnderson
Starting Member

2 Posts

Posted - 2011-12-02 : 18:31:56

I need to be able to build a dynamic function/sproc and then have it callable by another sproc to return the value as part of the main return set

with the lowest code block below, I have created what would be a UDF if it were possible and how it would be embedded. I have also tried having the function just return the dynamic SQL execution string, but I am not sure how I would get the main query to actually execute it to return a single string value.

I suppose I could load the results to a temp table, cursor through the table and call the dynamic sproc for each row, update the temp table, then select to return it, but that seems like it would be a huge performance hit. Is there a better way where the SQL engine can do it?

Create FUNCTION [dbo].[getmessagelistcustomfieldstring](@messageid [int], @categoryid [int], @userlanguageid [int])
RETURNS [nvarchar](255) WITH EXECUTE AS CALLER
AS 
begin	
DECLARE @tablename NVARCHAR(255)
DECLARE @fieldname NVARCHAR(255)
DECLARE @abbrev NVARCHAR(255)
DECLARE @fieldvalue INT
DECLARE @rtvstring NVARCHAR(MAX)
DECLARE @outstring NVARCHAR(255)
DECLARE @ParmDefinition nvarchar(255);
SET @rtvstring = ''
	
DECLARE CR CURSOR
FOR
    SELECT  BMF.MessageTableName ,
            bmf.messagecolumnname ,
            cfl.MessageListAbbreviation
    FROM    CategoryFields CF
            JOIN dbo.CategoryFieldLanguages cfl ON cfl.CategoryFieldID = cf.CategoryFieldID
            JOIN BaseMessageFields BMF ON BMF.BaseMessageFieldID = CF.BaseMessageFieldID
    WHERE   cf.CategoryID = @categoryid
            AND ISNULL(cf.MessageListSequence, 0) > 0
            AND cf.IsActive = 1
            AND cfl.LanguageID = CASE ( SELECT  COUNT(*)
                                        FROM    CategoryFieldLanguages AS ATL2
                                        WHERE   ATL2.CategoryFieldID = cf.CategoryFieldID
                                                AND ATL2.LanguageID = @UserLanguageID
                                      )
                                   WHEN 1 THEN @UserLanguageID
                                   WHEN 0 THEN 3
                                 END
    ORDER BY cf.MessageListSequence
SET @rtvstring = 'set @outstring = (select '
	    	
--   process for any joins required due to sort exceptions.
OPEN cr
FETCH NEXT FROM CR INTO @tablename, @fieldname, @abbrev
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SET @rtvstring = @rtvstring + '''<B>' + CAST(@abbrev AS NVARCHAR)
            + '<B> '' + cast(' + @tablename + '.' + @fieldname
            + ' as nvarchar)'
        FETCH NEXT FROM CR INTO @tablename, @fieldname, @abbrev
        IF @@FETCH_STATUS = 0 
            SET @rtvstring = @rtvstring + ' + '', '' + '
    END
DEALLOCATE CR

SET @rtvstring = @rtvstring
    + 'from Messages 
		join MessageOrganizationBillingCodes  on MessageOrganizationBillingCodes.messageid = messages.messageid
		left outer join MessageOptionFields  on MessageOptionFields.MessageID = messages.MessageID
		left outer join MessageTemplateContents on MessageTemplateContents.MessageID = messages.MessageID where messages.messageid = '
    + CAST(@messageid AS NVARCHAR) + ')'
				
SET @ParmDefinition = N'@outstring varchar(255) OUTPUT';
	
EXEC sp_executesql @rtvstring, @ParmDefinition, @outstring=@outstring OUTPUT;

RETURN ISNULL(@outstring,'')
END


GO 

CREATE PROCEDURE SelectMessageDisplayList
( @userlanguageid int)
as
SELECT dbo.[getmessagelistcustomfieldstring](m.messageid,m.categoryid,@userlanguageid)  AS customstring,* FROM messages m

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources