How to return varchar(MAX) from a CLR function?

Hi,

I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters.

How can I solve this problem and return varchar(MAX)?

Thanks

? The Visual Studio deployment tool maps SqlString to VARCHAR(8000). Use SqlChars instead -- it gets mapped to VARCHAR(MAX). -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bill YU@.discussions.microsoft.com> wrote in message news:2a21db0a-f54b-42e2-88b9-fb4cab26d356@.discussions.microsoft.com... Hi, I am trying to return [string/SqlString] from a CLR function, but it was truncated at 8000 characters. How can I solve this problem and return varchar(MAX)? Thanks|||Thanks Adam, it works.|||Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!
|||? Visual Studio deployment unfortunately has a lot of quirks. In case you're interested, I discuss another one in the following blog post: http://www.amazon.com/gp/plog/post.html/ref=cm_blog_pl/104-6385614-0075127?%5Fencoding=UTF8&pt=personalBlog&aid=PlogMyCustomersAgent&ot=customer&pd=1147803274.225&pid=PMCAALIINL2LPJELat1147801908&iid=AALIINL2LPJEL -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Bo416@.discussions.microsoft.com> wrote in message news:2343e9db-ce1c-4f2a-9b47-2d471a0ddb44@.discussions.microsoft.com...Came here to ask a similar question and saw your response to this question and it fixed my problem also. When deploying my CLR SP, studio was mapping the SP'S string parameters to varchar(4000) and my xml data was being truncated. Changed the data type of the sp parameter to SqlChars and everything worked fine after that, thanks!

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!!