SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 How To Return Results from EXEC of Dynamic SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Apollois
Starting Member

USA
49 Posts

Posted - 07/18/2004 :  00:58:37  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 07/18/2004 :  02:14:47  Show Profile  Reply with Quote
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
Go to Top of Page

Apollois
Starting Member

USA
49 Posts

Posted - 07/18/2004 :  10:39:52  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/18/2004 :  11:32:45  Show Profile  Reply with Quote
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
Go to Top of Page

Apollois
Starting Member

USA
49 Posts

Posted - 07/18/2004 :  15:36:39  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/18/2004 :  17:10:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000