How To Return Results from EXEC of Dynamic SQL
-
I'm writing a UDF that reads a varchar(255) column (Display_Value), and its data type (Item_Type) from a table. The function needs to convert the Display_Value to the data type in the Item_Type column, and return that value.
So, if Display_Value = '123.12', and Item_Type = 'decimal(10,2)'
then my UDF would convert '123.12' to 123.12 and return it.
SELECT @DisplayValue = Display_Value, @ItemType = Item_Type
from MyTable ...
Display_Value and Item_Type are both varchar(255)
The value of Item_Type can be the string any valid SQL data type:
'varchar(50)'
'int'
'bigint'
'real'
'bit'
'decimal(10,2)'
etc.
If I knew the data type explicitly, I could write:
SET @ReturnValue = CAST (@DisplayValue AS int)
But I don't.
EXEC 'SET @ReturnValue = CAST (' + @DisplayValue + ' AS ' + @ItemType + ')'
will execute, but @ReturnValue is not available outside if the EXEC string.
So, how can I get the results of the EXEC?
-
hey, try this to see if that is what you are looking for:
CREATE FUNCTION dbo.fn_dyna_cast() RETURNS sql_variant
AS
BEGIN
SET NOCOUNT ON
DECLARE @DisplayValue AS VARCHAR(255), @ItemType AS VARCHAR(255)
SELECT @DisplayValue = Display_Value, @ItemType = Item_Type FROM MyTable ...
DECLARE @ReturnValue AS sql_variant
SELECT @ReturnValue = N'CAST(' + N'''' + @DisplayValue + N'''' + N' AS ' + CAST(@ItemType AS NVARCHAR(255)) + N')'
RETURN @ReturnValue
END
JP
-
Journeyman,
Thanks for the suggestion, but it doesn't seem to return what I need. For example:
DECLARE @DisplayValue AS VARCHAR(255),
@ItemType AS VARCHAR(255)
DECLARE @ReturnValue AS sql_variant
SET @DisplayValue = '123.12'
SET @ItemType = 'decimal(6,2)'
SELECT @ReturnValue = N'CAST(' + N'''' + @DisplayValue
+ N'''' + N' AS ' + CAST(@ItemType AS NVARCHAR(255)) + N')'
PRINT Cast(@ReturnValue AS varchar(255))
The result is:
CAST('123.12' AS decimal(6,2))
I need @ReturnValue to be the decimal value:
123.12
Thanks.
Jim
-
hey jim,
i think this should do the trick:
CREATE PROCEDURE dbo.usp_dyna_cast(@ReturnValue sql_variant OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DisplayValue AS VARCHAR(255), @ItemType AS VARCHAR(255)
SELECT @DisplayValue = Display_Value, @ItemType = Item_Type FROM MyTable ...
DECLARE @stmt AS NVARCHAR(750)
SET @stmt = N'SELECT @ReturnValue = CAST(' + N'''' + CAST(@DisplayValue AS NVARCHAR(255)) + N'''' + N' AS ' + CAST(@ItemType AS NVARCHAR(255)) + N')'
EXEC sp_executesql @stmt, N'@ReturnValue sql_variant OUTPUT', @ReturnValue OUTPUT
END
JP
-
JP,
Thanks.
I get the following error:
EXECUTE cannot be used as a source when inserting into a table variable.
Jim
-
hi jim,
i updated the code in my previous post to do the CAST operation using sp_executesql. Refer to the post that has the CREATE PROCEDURE statement ...
JP
-
Read this. I don't think you can use dynamic sql inside a UDF.
Furthermore I think your whole approach isn't optimal. You should use the appropriate datatypes to store the data and avoid this unnecessary casting and converting.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
You're entitled to your opinion, but you might want to gather the necessary information before you make such a profound judgement.
You have no idea of what I'm trying to accomplish, yet you condem it.
Actually my design is optimal, and has been implemented in many other DBMS. The problem is the limitations of T-SQL, a rather primitive language by comparison.
Regards,
Jim
-
Oops Jim, no need to be that harsh!
You seem to be an application programmer. Your apparent non-understanding of T-SQL and/or RDBMS can be cured if you are willing to.
Good luck with your perfect design
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh, I forgot...
EOD!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I believe you are the one who is being harsh -- continuning to offer comments about things of which you have very little information.
FYI, I am a solution provider. Neither the DBMS nor the app alone can provide the client with what he needs. My comment about T-SQL was not intended to be negative, but simply to reconginze it's limitations. The a key decision every solution provider must make is in selecting the best tool for each job. Sometimes the processing is best done in a SP, other times in the app language, which is almost always a richer, more capable language.
There's no need to get nasty here -- why don't we keep this on a professional level.
Regards,
Jim
-
Jim,
when you write I don't have all information I need, why don't you provide them to increase the chance that someone here on the fora can provide a solution
Strictly professionally spoken, you have no chance achieving your goal with a UDF, since within a UDF you can't use dynamic sql. If you search the web you'll find numerous sources stating that.
You write the app can't provide what the client needs. Since the client is king and his wish is your command, I guess there is no chance to convince him to change his requirements?
Why does this need to be done at the server? Would it be an alternative to use a middle layer and do the transition there?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
>>Would it be an alternative to use a middle layer and do the transition there?<<
Exactly. There are some operations I would have preferred to do on SQL Server, but can also be done in the app. As you say, given the limitations of T-SQL I don't have a choice in some cases.
Regards,
Jim
-
what are u trying to achieve and whats the purpose behind all this
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply