How To Return Results from EXEC of Dynamic SQL

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 2000 Forums

Transact-SQL (2000)

How To Return Results from EXEC of Dynamic SQL

Author

Topic

Apollois
Starting Member

49 Posts

Posted - 2004-07-18 : 00:58:37

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?

TIA

Best Regards,
Jim

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 02:14:47

You are planning that @ReturnValue is going to be a sql_variant? Otherwise SQL will do its nut!

The normal way to handle this sort of communication with dynamic SQL is to use a table:


CREATE TABLE dbo.MyConversionTable
(
	MyGUID uniqueidentifier NOT NULL PRIMARY KEY,
	MyResult sql_variant NULL
)

DECLARE	@Display_Value	varchar(255),
	@Item_Type	varchar(255),
	@MyGUID		uniqueidentifier,
	@SQLCommand	varchar(8000),
	@ReturnValue	sql_variant

SELECT	@MyGUID = NewID()	-- "Batch Number" for this task

SELECT	@Display_Value = '123.12', @Item_Type = 'decimal(10,2)'

SELECT	@SQLCommand = 
		'INSERT MyConversionTable '
		+ 'SELECT	''' + CONVERT(varchar(40), @MyGUID) + ''', '
		+ 'CAST(''' + COALESCE(@Display_Value, 'NULL') + ''''
		+ ' AS ' + @Item_Type + ')'

SELECT	@SQLCommand as [@SQLCommand]	-- Debugging use only

EXEC	(@SQLCommand)

SELECT	@ReturnValue = MyResult
FROM	dbo.MyConversionTable
WHERE	MyGUID = @MyGUID

SELECT	SQL_VARIANT_PROPERTY(@ReturnValue, 'BaseType') AS [BaseType], 
	SQL_VARIANT_PROPERTY(@ReturnValue, 'Precision') AS [Precision], 
	SQL_VARIANT_PROPERTY(@ReturnValue, 'Scale') AS [Scale], 
	SQL_VARIANT_PROPERTY(@ReturnValue, 'MaxLength') AS [MaxLength], 
	@ReturnValue AS [@ReturnValue]

-- Tidy up current batch
DELETE	dbo.MyConversionTable
WHERE	MyGUID = @MyGUID

But I'm curious, why would you want to do this?

Kristen

Apollois
Starting Member

49 Posts

Posted - 2004-07-18 : 10:39:52

Kristen,

Thanks for your very detailed answer. I really appreciate your effort.

It's beginning to look like I can't do what I want to do in T-SQL.

Here's why I want to do this.

I have for many years (in other DBMS) used an approach to manage data used for a DB application's constants, application-wide settings, and user settings that makes use of two tables:

Params - maintains constants and single-value appliation settings
Lists - maintains lists of items

Let me give you an example for Params:
1. App_Title varchar(80) -- Title of Application
2. App_Ver decimal(6,2) -- Application version number

The Params table looks something like this:

CREATE TABLE Params (
Param_PK int IDENTITY (1, 1) NOT NULL ,
Modules varchar (254) NOT NULL ,
Param_Name varchar (50) NOT NULL ,
Item_Order numeric(5, 1) NOT NULL ,
Item_Type varchar (16) NOT NULL ,
Param_Value varchar (128) NOT NULL ,
Definition varchar (254) NOT NULL ,
Last_Update smalldatetime NOT NULL ,
)

Then I have an application function that gets the param from the DB and converts the value to the appropriate data type:

GetParam(psParamName)

A VBScript implementation of GetParam would look something like:

==============================================================


Function GetParam(psParamName)
	...
	lsSQL = "SELECT Param_Value, Item_Type FROM Params " _
				& "WHERE Param_Name = "" & psParamName & ""
	SET rstParam = oConnection.Execute(lsSQL)
	lxParamValue = rstParam("Param_Value")
	lsItemType = UCase(Left(rstParam("Item_Type"),1))

	SELECT CASE lsItemType
		CASE "I"
			lxReturnValue = CInt(lxParamValue)

		CASE "D"
			lxReturnValue = CDbl(lxParamValue)

		CASE "V", "C"
			lxReturnValue = lxParamValue

		CASE "B"
			lxReturnValue = CBool(lxParamValue)

		etc.
	END SELECT

	GetParam = lxReturnValue
End Function

==============================================================

I was hoping to build a similar function in T-SQL, but I was going to make use of the CAST funtion to dynamically convert to the proper data type. But I forgot that the sql_variant data type works differently than VB and other languages. Even if I can dynamically convert the param to the desired data type, and return as a sql_variant, I still need to convert the returned value in the calling procedure.

So I might as well forget about dynamic conversion in the function GetParam, and just always return it as varchar.

For example:

===============================
PROCEDURE XYZ

...
SET @AppVer = CAST(dbo.fnGetParam('App_Ver') AS Decimal(6,1))

IF (@AppVer > 2.0)
BEGIN
...
END
====================================

In any case, I'm still wondering if/how to return a result from an EXEC of a dynamically built string. Can this be done?

Best Regards,
Jim

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 11:32:45

Couple of thoughts ...

SQL will generally make an impicit data conversion, so its not too fussy if you give it varchar(nn) for Decimal / Date / etc. (assuming the actual value is properly formed for the type, albeit in text). The only real exception is MONEY where SQL really doesn't like to make implicit conversions - I dunno why that should be.

So you could just "go with" varchar as your Parameter value.

Alternatively you could go with multiple columns. This would not help differentiate between, say, DECIMAL(5,2) and DECIMAL(7,3), but could be used to enforce good validation and data typing of DATETIME and so on.

In this way you would have columns for, say, varchar, datetime, decimal(99,9) <Hehehe> and return all three to the application, together with your "lsItemType" and then let the application ignore the returned columns that are of inappropriate type.

If I haven't been clear enough say so and I'll knock up a short example.

Kristen

Apollois
Starting Member

49 Posts

Posted - 2004-07-18 : 15:36:39

Thanks Kirsten,

I think I've decided to just go with returning varchar. Since the calling procedure should know what datatype it needs, then let the calling proc make any necessary conversions.

But I'm still stuck on return data from an EXEC of a string -- not for this app but for other needs. For example:

============================
DECLARE @sReturnVar varchar(30)
SET @sReturnVar = 'Before Exec'

EXEC ('DECLARE @sRV varchar(30) SET @sRV = ''After Exec''')

Print @sReturnVar
=================================

How can I access the value of @sRV after the EXEC runs?

Thanks.

Best Regards,
Jim

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 17:10:48

You can't get much back from dynamic SQL executed within a Stored Procedure - do you need dynamic SQL?

Couple of choices I can think of:


CREATE PROCEDURE MySProc
   @SomeParameter varchar(10),
   @SomeOtherParam int,
   @ReturnVar varchar(30) OUTPUT
AS
   ... stuff ...
  SELECT @ReturnVar = 'FOO'
   ... stuff ...
GO

DECLARE @MyReturnVar varchar(30)
EXEC MySProc 
   @SomeParameter='AAA',
   @SomeOtherParam=123,
   @ReturnVar=@MyReturnVar OUTPUT
SELECT @MyReturnVar AS TheOutputResult

Secondly, if you've got to do it in dynamic SQL, within an SProc, then you need to use sp_executeSQL with a PARAMETERISED query - basically its the same as EXEC(@MySQLString) but allows for parameters to be passed in, and out, of the SQL string expression.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources