SQL function using dynamic sql.

 Hi all

 I am trying to pass a column name as a variable in a function but it will not let me.  I am sure it is something stupid. I know that you cannot pass a column in a normal select statement as a variable. You have to execute it dynamically, however you cannot use the exec in a function. Please see code below


ALTER FUNCTION dbo.GetData(@ColumnName varchar(50))

RETURNS Decimal(18,4)

AS

BEGIN

DECLARE @Value Decimal(18,4)

SET @Value = (SELECT + @ColumnName + FROM Policy   WHERE Grade = 'Revised') -- Tried this way, does not work need to use Exec

SET @Value = Exec('SELECT  '+ @ColumnName + ' FROM Policy  WHERE Grade = 'Revised') -- Tried this, cannot use exec in a function


RETURN @Value

END

 

0
wstevens
8/21/2008 9:30:07 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
852 Views

Similar Articles

[PageSpeed] 17

This is the function you have to use.

create FUNCTION dbo.GetData(@ColumnName varchar(50))
RETURNS Decimal(18,4)
AS
BEGIN

  DECLARE @Value Decimal(18,4)
  declare @oi nvarchar(300)  
  declare @oParam nvarchar(300)  
  declare @oStText nvarchar(3000)    
  set @oStText = N'select @omOutput = (select  ' + @ColumnName + ' from Policy where Grade = ''Revised''  )';  
  set @oParam = N' @omOutput varchar(30) OUTPUT';     
  execute sp_executesql @oStText,@oParam,@omOutput = @oi OUTPUT;  
  return @oi  
  
END


 you have to use
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
8/21/2008 11:39:25 AM

 Hi I get the following error

 Only functions and extended stored procedures can be executed from within a function.
Invalid attempt to read when no data is present.

This is the actual code the other was pseudo code

ALTER FUNCTION dbo.GetBasicEscTestNew(@Band varchar(10))

RETURNS Decimal(18,4)
AS
BEGIN

DECLARE @Value Decimal(18,4)
DECLARE @oi nvarchar(300) 
DECLARE @oParam nvarchar(300) 
DECLARE @oStText nvarchar(3000) 
 
SET @oStText = N'select @omOutput = (select ' + @Band + ' from BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6))';
SET @oParam = N' @omOutput varchar(30) OUTPUT'; 

EXECUTE sp_executesql @oStText,@oParam,@omOutput = @oi OUTPUT; 

  RETURN @oParam 

END
 

0
wstevens
8/21/2008 11:51:17 AM

there is no msitake in function syntax.

it is creating successfully.

when calling that function, i think you ar edoing mistake

we have to call like

select * from dbo.GetBasicEscTestNew('columnname')


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
8/21/2008 1:02:09 PM

Hi 

Called it from a stored procedure

ALTER PROCEDURE dbo.StoredProcedure9
AS
select * from dbo.TESTGetBasicEsc('A')
RETURN   

and got the following error;

Running [dbo].[StoredProcedure9].
Invalid object name 'dbo.TESTGetBasicEsc'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[StoredProcedure9].

 This is the function code please check if it is correct and that I have not left anything out.

ALTER FUNCTION dbo.TESTGetBasicEsc(@Column varchar(50))
RETURNS varchar(50)
AS
BEGIN
  
   DECLARE @Value Decimal(18,4)
   DECLARE @oi nvarchar(300) 
   DECLARE @oParam nvarchar(300) 
   DECLARE @oStText nvarchar(3000)  
  
  SET @oStText = N'select @omOutput = (select  ' + @Column + ' FROM BasicEsc_tbl WHERE RIGHT(CONVERT(varchar(50),[Date],6),6) = RIGHT(CONVERT(varchar(50),GETDATE(),6),6)'; 
  SET @oParam = N' @omOutput varchar(30) OUTPUT';    
  EXECUTE sp_executesql @oStText,@oParam,@omOutput = @oi OUTPUT; 
  return @oi 
 
 END

Every website I hace gone to in connection with this issue says you cannot use an execute command in a Function
   


 

0
wstevens
8/21/2008 1:31:57 PM

Oh, Sorry, i forgot the principal of "dynamic sql will not work in User defined function"

I think at most you can write this like

ALTER FUNCTION dbo.TESTGetBasicEsc(@Column varchar(50))
RETURNS varchar(50)
AS
BEGIN                        

select

              case 

                    when @Column = 'CategoryCode' then CategoryCode

                    when @Column = 'CategoryName' then CategoryName

                    else Description

               end

from CategoryMaster  


 END

 

You have to write case statement for each column in the table.


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
8/21/2008 1:53:47 PM

Hi ,

     Your UDF (User Defined Function) may compile properly, but you cannot execute a procedure from an UDF.

You wil be getting the following if you follow your approach:

 Msg 557, Level 16, State 2, Line 1 Only functions and extended stored procedures can be executed from within a function.

I will suggest to create a function and use a select statement with a case checking for the column name. Following is the sample query:

CREATE FUNCTION dbo.GetData(@ColumnName varchar(50))

RETURNS int

AS

BEGIN

declare @t int

select @t =

CASE

WHEN @ColumnName = '<col1>' THEN <col1>

WHEN @ColumnName = '<col2>' THEN <col2>

ELSE NULL

END

FROM <table name>

return @t

END

Now you can call the function as

select dbo.GetData('<col name>')

If you want to use in the from clause of the select statement you can return a table from UDF.

Hope this solution gives you some ideas.


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
8/21/2008 1:56:37 PM

Hi ksridharbabuus

Unfortunatly I cannot do it this way as the columns in the table change dynamically according to what my client enters.
 

 

0
wstevens
8/21/2008 2:05:22 PM

instead of using a function, you can use a stored procedure. in that procedure, you can exeute dynamic sql and call that result from another stored procedure.

Example :

-- First Stored procedure
create proc sc_giveTableColumnDate
(
	@ColumnName varchar(100),
	@Id varchar(100) output
)
as
begin
	DECLARE @Value varchar(100)
	DECLARE @oi nvarchar(300)  
	DECLARE @oParam nvarchar(300)  
	DECLARE @oStText nvarchar(3000)    
	SET @oStText = N'select @omOutput = (select top 1 ' + @ColumnName + ' from CategoryMaster )'; 
	SET @oParam = N' @omOutput varchar(30) OUTPUT';  
	EXECUTE sp_executesql @oStText,@oParam,@omOutput = @oi OUTPUT;  
 set @Id = @oi
end

-- second stored procedure

alter PROCEDURE sp_ParentStoredProc
(

@ColumnName varchar(50)
)
AS

declare @Id varchar(100)
EXEC sc_giveTableColumnDate @ColumnName, @Id OUTPUT
select @Id
RETURN
GO 

-- Execution:
sp_ParentStoredProc 'CategoryCode'
 
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
8/21/2008 2:20:16 PM

Hi,

     In general I will not recommend Dynamic queries until unless it is very much required.

Because there is lots of chance to inject SQL injections.

For example in the above we can inject SQL injection as:

set @ColumnName = ' count(*) from sysobjects) --UrColumnName'

If I execute the procedure with the above statement I can identify how many entires are available in the database. with few set of queries we can get lots of info.

 


-Sri
-------------------------------------------------
If this post was useful to you, please mark it as answer. Thank you!
0
ksridharbabuus
8/21/2008 2:39:58 PM

 Hi

I am not worried about  SQL injections as this is not a website but a stand alone vb.net application which does not connect to the ne in any way

as to the example putting it into a stored procedure I am currently trying it.


 

0
wstevens
8/21/2008 2:46:49 PM
Reply:

Similar Artilces:

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

SQL syntax error using dynamic SQL
I have below code:    set @SQL='(select top 1 @idOUT = id from prospects where result=0' + @SQL_excludeprospects + ' order by id ASC)'    print @SQL    SET @ParmDefinition = N'@idOUT int OUTPUT';    EXECUTE sp_executesql @SQL, @ParmDefinition, @idOUT=@id OUTPUT;    SELECT @id; I get this output:(select top 1 @idOUT = id from prospects where result=0 AND id<>2 AND id<>6 AND id<>9 order by id ASC)Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'order'.I do...

Dynamic SQL in Transact SQL
Does Dynamic SQL can be done in transact sql format in stored procedure? If yes, any reference can be read? Thx..... ...

Dynamic SQL in Sybase SQL
Im trying to create a bit of Dynamic SQL in a Sybase stored procedure, im used to MS SQL Server so im not sure what im doing wrong in Sybase. Heres my bit of code declare @sql varchar(100) declare @sql2 varchar(100) Select @sql = '''0001/100212'',''0001/100218'',''0001/100240'','0001/100243''' select @sql2 = 'select * from table where table_tra_code in (' + @sql + ')' Exec @sql2 now i would have thought this would have executed @sql2 and returned a result set, but no :(. Any ideas?, id...

SQL Injection and Dynamic SQL
The consensus on avoiding SQL injection attacks is to pass all your collected data to a stored procedure. If you have to return a result set from a complex form, the answer seems to be to use dynamic SQL. On further study, I discovered that dynamic SQL has a lot of potential pitfalls and low and behold can still be vulnerable to injection attacks. I know that I am not the first person to ever try and collect data from a web form and return a result set, so what are the best practices? Say I have a complex form with about 25 search criteria (most of which are optional), I go through all t...

how do i insert data into sql server using data from textboxes and sql connection and sql command
anyone can give me a simple explaination and code.. Thanx in advance Take a look @ Sample Code: How to add...Sushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb There are a lot of tutorials out there, but I know they can be a bit confusing as they all approach things slightly differently. Basically a nice simple version would go like this:' *** First set up your connectionDim conConnection As New SqlConnection("server=servername;database=yourdatabase;uid=yourname;pwd=yourpassword;")' *** This line takes care of your sommand text and command connection in one ...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

Why these two SQL statement return differently? using ROUND() function in Transact-SQL
Hello, DECLARE @x DECIMAL SET @x = 65.554 SELECT ROUND(@x, 1) --this returns 66 SELECT ROUND(65.554, 1) --this returns 65.600 can someone explain to me why is like that?  Thank you   Always specify the size for the parameters. For example: Decimal(10,3). ***********************Dinakar NethiLife is short. Enjoy it.*********************** DECLARE @x DECIMAL(10,3) SET @x = 65.554 SELECT ROUND(@x,1) SELECT ROUND(65.554,1) both return the same answer. ndinakar & Motley Thank you two very much.  I got it.  :)...

creating ssis packagte for sql server to sql serer using C#.net
Hi friendsdoes any one has code or urls which can help me to create the ssis pacakage which will transfer data from sql server to sql server databaseswith some manipulationsthanks so much Lad LaxmikantPune You are probably in the wrong forum.  This forum is for questions relating to ASP.NET Dynamic Data. thanks,David...

SQL A and SQL Server
Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL Anywhere, therefore, similar to SQL Server? -- Regards, John Not quite true. SQL Anywhere was created by Watcom. SQLServer was created by Sybase, and Microsoft licensed the technology. So MSSQL and ASE have common roots, not SQLAnywhere. -- Paul Horan[TeamSybase] "John Kingan" <john.kingan@abibuildingdata.com> wrote in message news:40d82aa1$1@forums-1-dub... > Hi I heard SQL Server used to be owned by Watcom is this true? Is SQL > Anywhere, therefore, similar to SQL Ser...

SQL > My SQL
I have a problem, my asp.net page wants to connect to the sql server, but it has to be the mysql. How can I solve this problem? This is my code: <%@ page explicit="true" language="VB" debug="true" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.Odbc" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <script runat="server"> sub page_load(sender as object, e as eventargs) If Not IsPostBack Then dim connect as sqlconnectio...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

SQL Express to SQL ?
I am developing a site using the express edition of all tools (VWD and SQL Express).  I want to use GoDaddy hosting and was wondering if I will be able to effectively put my SQL database and website up on their hosting even though it was developed via Express edition of Microsoft programs?...

SQL Function for MS-SQL compatibilty
Hi, There is any chance that Sybase implement customize SQL function in ASE ? We would make softwares who be compatible with both database ASE 12... and MS SQL 2000. At this time, we do it except for SQL Function. Nobody have some information on it ? thx This was an ISUG enhancement request (9310_18) that "went nowhere". Then there is request e01_274 which is basically the same. Sybase has indicated to ISUG that it is planned. Which future release is the interesting question. "Raynald M." <no_spam_bilou120000@hotmail.com> wrote in message news:...

Web resources about - SQL function using dynamic sql. - asp.net.sql-datasource

Wave function - Wikipedia, the free encyclopedia
... (a ball attached to a spring ) in classical mechanics (A-B) and quantum mechanics (C-H). In quantum mechanics (C-H), the ball has a wave function ...

TV binging, exercise skipping linked to poor cognitive function
... their fit, less TV-addicted counterparts. In particular, couch potatoes had slightly lower brain processing speeds and worse executive function, ...

iPhone best tricks and functions - Business Insider
Your iPhone can do a lot more than you think.

Learning F# – Passing Parameters to Functions
One of the first issues I faced when learning F# was finding out how to specify multiple parameters to a function. While this might sound obvious ...

HPE Offers Catalog of Virtual Network Functions for Telcos
The vendor's OpenNFV Solution Portal gives carriers a single place to find a range of VNFs that are pre-tested to run on their networks.

7 Useful Technical SEO Functions For Your E-commerce Website
This article was co-authored by Mate Dyekiss, SEO Strategist at IMI. An e-commerce website can create all the technical SEO issues onecan imagine: ...

Madras HC asks police to allow Tipu’s birth anniversary function
... Station on a petition filed by Ismail, General Secretary of Thamizhaga Makkal Jananayaga Katchi, seeking grant of permission to hold the function. ...

Colorado governor: This Planned Parenthood shooting may be a function of inflammatory rhetoric from bloggers ...
Via Grabien , skip to 4:45 of the clip below for the key bit. I naively thought we were (mostly) done with “climate of hate” stupidity after ...

Surprise: Binge-Watching TV May Cause a Decline in Brain Function
With the second season of Transparent only a few days away, some bad news awaits us: According to a recent study and common sense, probably, ...

1964 Archival Footage of British Soldiers Under the Effects of LSD Losing the Ability to Properly Function ...
1964 archival footage of British soldiers being given LSD-25 while training the the field. Over time they lost their ability to properly function, ...

Resources last updated: 12/25/2015 7:20:48 PM