How to return the result of an EXEC from a function

Hi,

I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.

This is my function:

ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@.TypeID int,
@.CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1
BEGIN
SET @.RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END

FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND P.CaseID = ' + CAST(@.CaseID as varchar)
END
ELSE
BEGIN
SET @.RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND CaseID = ' + CAST(@.CaseID as varchar)
END

RETURN @.RTN

END

Each time I try " RETURN EXEC(@.RTN) " or something similar I get an error.

I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.

Anyone with any ideas?

You need to give a look to the CREATE FUNCTION article in books online. There are a number of constraints on the DML that can be used inside of a function. In this case you are being stopped by the constraint that does not allow use of the EXEC ( @.anSQLString ) inside of a function. You are also not allowed to execute stored procedures from inside the body of a function. I would suggest that if you want to run an EXEC ( @.someKindOfString ) that you really ought to use a stored procedure instead of a function. Since the problem is dynamic SQL you might want to first see if you can eliminate the dynamic SQL.

Kent

|||

Following on from Kent, in looking at your query do you actually need to build a dynamic string at all?

Code Snippet

CREATE FUNCTION [dbo].[ReturnPickItemValue]

(

-- Add the parameters for the function here

@.TypeID int,

@.CaseID int

)

RETURNS varchar(max)

AS

BEGIN

-- Declare the return variable here

DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1

BEGIN

SET @.Rtn = (SELECT I.PickItem +

CASE D.IsStartDateEstimated

WHEN 0 THEN CAST(StartDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)

END +

CASE D.IsEndDateEstimated

WHEN 0 THEN CAST(EndDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)

END

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID

LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID

WHERE L.PickTypeID = @.TypeID

AND P.CaseID = @.CaseID)

END

ELSE

BEGIN

SET @.Rtn = ( SELECT I.PickItem

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID

WHERE L.PickTypeID = @.TypeID

AND CaseID = @.CaseID)

END

RETURN @.Rtn

END

HTH!

|||cheers richbrownesq

That works a treat!!