Passing Column names as Parameters

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

Transact-SQL (2005)

Passing Column names as Parameters

Author

Topic

javaguy5
Starting Member

9 Posts

Posted - 2008-07-12 : 15:12:47

I have been working with SQL for about 4 months in a school environment. Now I am in the real world and am having some real problems.
Here is my code so far, this is the simpliest of them but if answered I will be able to complete about 5 other functions and procedures:

CREATE FUNCTION getMin
(
@column varchar(max) = 'leftHorizRaw',
@table varchar(max) = 'tempRawDataForTest'
)
RETURNS decimal(6,4)
AS
BEGIN

DECLARE @min decimal(6,4)

set @min =('Select min(' + @column + ') from ' + @table)

RETURN @min

END
GO

Obviously what I am trying to do is have the user from a future VB program enter column and tables name, select from a drop down and listbox of course, into the function and the function will return the min value from the column and table selected.
I keep getting a "Arithmetic overflow error converting varchar to data type numeric" (or decimal). I know having an execute command prior to the "('Select min(' + @column + ') from ' + @table)" would work it this were just a stored procedure and not passing it back to a variable in a function but this is a last resort. I know that the @column and @table is just putting a string into the decimal(6,4)column that I am trying to reference. How can I get it so that it executes the command by using the parameters as the column and table names not as there inputs?Once I am able to accomplish this then I should be able to solve this also:
“declare goThroughRows CURSOR READ_ONLY for SELECT @columnX, @columnY from tempRawDataforTest” – I want this to be able to read @columnX and @columnY columns (which are input parameters of another function) – If anyone can solve that issue as well that would be great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 15:28:18

If you want to use table and column as parameters in query you should use dynamic sql. You can use sp_executesql to declare the variable @min as output type and return value through. Have a look at sp_executesql in books online.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 15:29:11

This is a very bad idea. You should not be designing a system like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

javaguy5
Starting Member

9 Posts

Posted - 2008-07-12 : 16:22:53

I was wondering why this is a bad idea. I need to be able to have a system where the user can define complex algorithms using some functions that I have coded into SQL along with some if statements and arrays. They will need to get various minimums, slopes, percentiles. and ranges from tables and columns of their choice after I am gone. They want to able to define these algorithms without having to learn how to code the database. These algorithms will also be stored as a string so that others will be able to see what algorithms are avaliable and their logic. All parameter inputs will be checked before going to the server by a program that I will build to ensure valid algorithms and parameter inputs. No one using this system will have a technical background, so this was the only way I though this could be possible, with my current knowledge (Bachelor of Science in MIS). If you have some advice or recommend some reading material I am open minded.

quote:
Originally posted by tkizer

This is a very bad idea. You should not be designing a system like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 16:49:32

It is bad for performance and security reasons. You can search on dynamic sql to find out more information about it being a bad practice usually as it has been discussed at length.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

javaguy5
Starting Member

9 Posts

Posted - 2008-07-12 : 17:16:38

Therefore would you recommend building all the functions and stored procedures that could possible be used and then have the application decide which to execute based on what the user defines?

quote:
Originally posted by tkizer

It is bad for performance and security reasons. You can search on dynamic sql to find out more information about it being a bad practice usually as it has been discussed at length.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 22:35:54

Yes, but if that's too cumbersome, you might as well use inline sql rather than stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-14 : 03:24:12

Also make sure you read this article
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

javaguy5
Starting Member

9 Posts

Posted - 2008-07-15 : 16:37:37

Thanks very much for the article it was truly helpful.

quote:
Originally posted by madhivanan

Also make sure you read this article
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

deepthi immadi
Starting Member

3 Posts

Posted - 2011-12-13 : 21:03:17

Hi ,i have the same problem .can please send me the solution .how u resolve that one.

Thanks
Dk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:50:40

quote:
Originally posted by deepthi immadi

Hi ,i have the same problem .can please send me the solution .how u resolve that one.

Thanks
Dk

can you specify what exactly is the issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepthi immadi
Starting Member

3 Posts

Posted - 2011-12-14 : 00:47:11

Hi ,
i am passing column name dynamically to select query i am unable execute
that select query inside a function or cursor also .please find below.

LTER FUNCTION [dbo].[getdisp](@userid int,@endpointtype nvarchar(100))
RETURNS nvarchar(max)
AS
Begin
Declare @re varchar(100)
Declare @TgEnd nvarchar(100)
Declare @Dicolumn nvarchar(100)
Declare @Tgcolumn nvarchar(100)
Set @re='Deepthi'

IF @endpointtype='ActiveDirectory'
BEGIN
select top 1 @Dicolumn= DiColumn,@Tgcolumn=Tgcolumn from DI_ADS_AttributeMapping
--select @re= @Dicolumn from DI_M_User where userid=@userid
select @TgEnd=(select @Tgcolumn from DI_ADS_userinfo where userid=@userid )
Set @re = 'Attribute Mapping ' +(@TgEnd)+ ' = '+('"'+ @Tgcolumn +'"') +' Not mapped with '+ (@Dicolumn) +' in Trusted Source'
END
ELSE
BEGIN
select top 1 @Dicolumn= DiColumn,@Tgcolumn=Tgcolumn from DI_ADS_AttributeMapping

select @TgEnd=(select @Tgcolumn from DI_ADS_userinfo where userid=@userid )
Set @re = 'Attribute Mapping ' +(@TgEnd)+ ' = '+('"'+ @Tgcolumn +'"') +' Not mapped with '+ (@Dicolumn) +' in Trusted Source'

END
RETURN(@re)
END

unable to execute select command inside a function.

Thnaks
Dk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 02:09:42

hmm...dynamic sql inside function? whats the purpose of doing this? Can you explain what you're trying to do here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

deepthi immadi
Starting Member

3 Posts

Posted - 2011-12-14 : 02:29:28

Hi,
Actually column names we are getting from some other tables and it all ways change the column name based on the i need construct one string and storing into database(string is one column value i am executing this function in stored procedure.Suggest something.

Thanks
Dk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 03:35:56

hmm...what does string contain? will it be always returned as a single value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources