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 2005 Forums
 Transact-SQL (2005)
 Passing Column names as Parameters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

javaguy5
Starting Member

USA
9 Posts

Posted - 07/12/2008 :  15:12:47  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 07/12/2008 :  15:28:18  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 07/12/2008 :  15:29:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

javaguy5
Starting Member

USA
9 Posts

Posted - 07/12/2008 :  16:22:53  Show Profile  Reply with Quote
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


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 07/12/2008 :  16:49:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

javaguy5
Starting Member

USA
9 Posts

Posted - 07/12/2008 :  17:16:38  Show Profile  Reply with Quote
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


Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 07/12/2008 :  22:35:54  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 07/14/2008 :  03:24:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also make sure you read this article
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

javaguy5
Starting Member

USA
9 Posts

Posted - 07/15/2008 :  16:37:37  Show Profile  Reply with Quote
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

Go to Top of Page

deepthi immadi
Starting Member

3 Posts

Posted - 12/13/2011 :  21:03:17  Show Profile  Reply with Quote
Hi ,i have the same problem .can please send me the solution .how u resolve that one.

Thanks
Dk
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/13/2011 :  23:50:40  Show Profile  Reply with Quote
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/

Go to Top of Page

deepthi immadi
Starting Member

3 Posts

Posted - 12/14/2011 :  00:47:11  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/14/2011 :  02:09:42  Show Profile  Reply with Quote
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/

Go to Top of Page

deepthi immadi
Starting Member

3 Posts

Posted - 12/14/2011 :  02:29:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/14/2011 :  03:35:56  Show Profile  Reply with Quote
hmm...what does string contain? will it be always returned as a single value?

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

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.14 seconds. Powered By: Snitz Forums 2000