Not rocket science, but stumped...

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

General SQL Server Forums

New to SQL Server Programming

Not rocket science, but stumped...

Author

Topic

pats2kDynasty
Starting Member

16 Posts

Posted - 2009-07-10 : 12:23:03

All I am trying to do is to send in 3 variables(query criteria ID, a field name, a table name) to a function and then dynamically query and return the result, so...

CREATE FUNCTION dbo.GetFieldValueFromTable(@DID nvarchar(11), @Field nvarchar(50), @Table nvarchar(50))

Problems:
1. I do not know what the return type for the function will be (querying dynamic tables and fields based on user passed in criteria), so I went the route of a table variable @Result with three fields of the data types that I will need.
2. Can not create a temp table in a function
3. Had trouble using a table variable because can not use exec with table variable to find out the table name value that the user passed in.

Possible Solutions (unsuccesfull with all of these attempts):
1. Find a way to dynamically declare a funtion return type
2. Create a temp table in a function
3. Somehow get the value of table name variable @Table
4. Use something othe than a function like a stored proceedure with OUTPUT variables

CODE:
CREATE FUNCTION dbo.GetFieldValueFromTable(@DID nvarchar(11), @Field nvarchar(50), @Table nvarchar(50))
RETURNS @Result TABLE
(
rNVarChar nvarchar(400) null,
rIntField int null,
rNumeric numeric null
)
AS
BEGIN
DECLARE @DataType nvarchar(30)

--This determines the data type so that the correct field can filled in @Result table. The other fields are null by default
SET @DataType = (SELECT data_type FROM information_schema.columns WHERE table_name = @Table AND column_name = @Field)

IF @DataType LIKE 'NVARCHAR'
BEGIN
--The exec line was used in an effort to get the value of @Table, but the could not use exec with @Result
--exec('INSERT INTO @Result (rNVarChar) SELECT [' + @Field + '] FROM [' + @Table + '] WHERE ParentID = ' + '''' + @DID + '''')
INSERT INTO @Result (rNVarChar) SELECT @Field FROM @Table WHERE ParentID = @DID
END
ELSE IF @DataType LIKE 'INT'
BEGIN
--The exec line was used in an effort to get the value of @Table, but the could not use exec with @Result
--exec('INSERT INTO @Result (rIntField) SELECT [' + @Field + '] FROM [' + @Table + '] WHERE ParentID = ' + '''' + @DID + '''')
INSERT INTO @Result (rIntField) SELECT @Field FROM (SELECT table_name FROM information_schema.TABLES WHERE table_name = @Table) tbl WHERE tbl.ParentID = @DID
END
ELSE IF @DataType LIKE 'NUMERIC'
BEGIN
--The exec line was used in an effort to get the value of @Table, but the could not use exec with @Result
--exec('INSERT INTO @Result (rNumeric) SELECT [' + @Field + '] FROM [' + @Table + '] WHERE ParentID = ' + '''' + @DID + '''')
INSERT INTO @Result (rNumeric) SELECT @Field FROM (SELECT table_name FROM information_schema.TABLES WHERE table_name = @Table) tbl WHERE tbl.ParentID = @DID
END

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-10 : 13:10:26

1.what do you mean by dynamic return type? why does return type vary?
2. table variables can be created inside tabled valued udf
perhaps you could explain us why your result type varies.

pats2kDynasty
Starting Member

16 Posts

Posted - 2009-07-10 : 13:34:24

I meant that a function has to have a declared return type: function xyz () as RETURNTYPE

Since I am querying multiple tables and fields with varying data types I could never be sure of what the return type would be.

The above is the reason I went with a table variable.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 14:04:56

So you want a single UDF that will return any value from any column from any table in your database? That is not a a good stategy for a relational database. If you want to do that then you would need to store all your data in a single table with 4 columns (table, column, dataType, value)

Be One with the Optimizer
TG

pats2kDynasty
Starting Member

16 Posts

Posted - 2009-07-10 : 14:39:19

This is a subset of a large data pull from a military DB. If you know anything about gov't DB's they are not relational. They are comprised of hundreds of tables all connected via single ID field. That is what I am working with whether I like it or not.

If I can run a the query SELECT fieldA FROM tableA WHERE ID = @ID. Then I did not think it too impossible to have a function that you could pass in fieldA, tableA, @ID in order to run a query and return one field from one record.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 14:47:27

The nature of compiled code is to have the sql objects (tables, columns) static. The parameters are just for the comparisons to data values. What you're talking about is dynamic sql. As you found out you can't execute dynamic sql from within a UDF.

If you want to exec dynamic code you could make that into a stored procedure rather than a UDF.

Be One with the Optimizer
TG

pats2kDynasty
Starting Member

16 Posts

Posted - 2009-07-10 : 16:24:46

thank you. I am now trying it as an sp. I am using sp_executesql but I am not familiar with it so I know I am doing something wrong with below:

--@p* = parameter coming in from call
DECLARE @pField nvarchar(50)
DECLARE @pTable nvarchar(50)
DECLARE @pDID nvarchar(11)

DECLARE @sql as nvarchar(400)
DECLARE @paramDef as nvarchar(400)

--putting values like call would fill
SET @pField = 'MDS'
SET @pTable = 'A_MDS_DETAIL'
SET @pDID = 'FFT3K04A001'

set @sql = 'SELECT @pField FROM @pTable WHERE ParentID = @pDID'
set @paramDef = '@pField nvarchar(50) = @pField, @pTable nvarchar(50) = @pTable, @pDID varchar(11) = @pDID'

EXEC sp_executesql @sql, @paramDef

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 16:34:47

Same problem with sp_executesql - you can't declare sql objects as part of the parameter list. But you can do this:


DECLARE @pField nvarchar(50)
DECLARE @pTable nvarchar(50)
DECLARE @pDID nvarchar(11)

DECLARE @sql as nvarchar(400)
DECLARE @paramDef as nvarchar(400)

--putting values like call would fill
SET @pField = 'name'
SET @pTable = 'sysusers'
SET @pDID = '0'

set @sql = N'SELECT ' + @pField + ' FROM ' + @pTable + ' WHERE uid = @pDID'
set @paramDef = N'@pDID varchar(11)'

EXEC sp_executesql @sql, @paramDef, @pDID = '0'@pDID

OUTPUT:
name
------------
public

EDIT:
correction above

Be One with the Optimizer
TG

pats2kDynasty
Starting Member

16 Posts

Posted - 2009-07-10 : 17:00:03

Thank you. That did it. I really appreciate your help!

I was really off base and complicating things in my thinking...live-n-learn I guess.

Thanks again!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 17:07:33

You're welcome - good luck

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources