Transact SQL :: How To Execute Insert Statement With A Function Call

May 20, 2015

I have a function like below

CREATE FUNCTION [dbo].[UDF_GetCode] 
(
@TableName NVARCHAR(50)
)
RETURNS NVARCHAR(50)

[code]...

This function is called in insert statement like below. exec sp_executesql N'INSERT INTO Table ([Code], [Name]) VALUES (dbo.UDF_ GetGlobal ConfigCode (''TableName''), @Name)'I am getting following error.Only functions and some extended stored procedures can be executed from within a function.

View 3 Replies


ADVERTISEMENT

Trying To Call The Function A Web Service From Transact-SQL

Jun 2, 2006

I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.

I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?

Thanks for any help.



ALTER PROCEDURE [dbo].[pTAPServiceWeb]

@sUrl varchar(200),

@response varchar(8000) out

AS

DECLARE @obj int

DECLARE @hr int

DECLARE @status int

DECLARE @msg varchar(255)

EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

IF @hr < 0

BEGIN

RAISERROR('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1)

RETURN

END

EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod Open failed'

GOTO err

END

EXEC @hr = sp_OAMethod @obj, 'send'

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod Send failed'

GOTO err

END

EXEC @hr = sp_OAGetProperty @obj, 'status', @status OUT

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod read status failed'

GOTO err

END

-- IF @status <> 200

-- BEGIN

-- SET @msg = 'sp_OAMethod http status ' + str(@status)

-- GOTO err

-- END

EXEC @hr = sp_OAGetProperty @obj, 'responseText', @response OUT

IF @hr < 0

BEGIN

SET @msg = 'sp_OAMethod read response failed'

GOTO err

END

EXEC @hr = sp_OADestroy @obj

RETURN

err:

EXEC @hr = sp_OADestroy @obj

RAISERROR(@msg, 16, 1)

RETURN

GO

View 3 Replies View Related

Function Call In Insert Statment

Jul 3, 2006

Hi



i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@value, dbo.function(@value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db



thx for help

View 3 Replies View Related

How To Execute Insert Statement To Multiple DB's

Apr 21, 2008

I have the following insert how can i execute to multiple databases on same server:

insert into Tablerecords(labelkey,moduletype,english,spanish,updatedby)
values('hypUnderConstruction','MENU','Under Construction','Under Construction','admin')

databases: db1,db2,db3,db4,db5 etc

Thanks.

View 5 Replies View Related

Why These Two SQL Statement Return Differently? Using ROUND() Function In Transact-SQL

Sep 19, 2007

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
 

View 3 Replies View Related

Transact SQL :: Select Statement Not Working In User Defined Function

Nov 3, 2015

The select statement:

SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(Date AS DATETIME) + CAST(Time AS DATETIME))
   FROM [DataGapTest]

Gives the right output:

NULL
1
1
3548
0

However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).

CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time)
RETURNS INT
AS
  BEGIN
 DECLARE @timeInterval INT
   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))
   FROM dbo.[DataGapTest]
  RETURN @timeInterval
  END

View 5 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

Invalid Insert Statement In The Function

Apr 26, 2005

Dear Friends.

I m trying to use the insert statement with in the function !
and i m getting this errror !

Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16
Invalid use of 'INSERT' within a function.

Please help me how to rectify it and how i can use the Insert statement with in the function !

Here is the code for the function.


create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as
begin
Declare @valCost integer
Declare @TotService integer
Declare @TotParts integer
Declare @TotLabour integer
Declare @TotTravel integer
Declare @TotSubContract integer
select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no
select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no
insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1)
return (@valCost)
end

View 2 Replies View Related

Help Creating A Transact SQL Insert Statement

Jun 3, 2006

Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.

To explain, I have two web pages. search.asp and results.asp.

search.asp has the following.

Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName

results.asp has the following SQL which pulls all the results.

SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC

The database & form I want to insert into.

tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"

Please ask if u need more info.

Mally

View 1 Replies View Related

Can Insert Statement Works In Case When Function

Nov 5, 2007

Hi

Im beginer in sql,Please guide

can insert statement works fine in case when function

for example

case when condition1=true then (first insert statement based on some condition) when condition2=true then (second insert statement based on some other condition)
end

View 5 Replies View Related

Execute SQL Insert Statement From Script Task Using Package OLEDB Connection

Aug 1, 2007



Is there a way to directly do this in one step(Execute SQL Insert Statement from Script Task using package OLEDB Connection)?

Right now I'm using a script task to build a sql insert statement using package variables (to fill values) populated by certain logic in the package.

Then assigning this command string to a package variable.
Then using a sql execute task to execute this variable.

A link to an article or code would be greatly appreciated.

View 1 Replies View Related

Transact SQL :: Insert On Merge Statement Not Working

Oct 7, 2015

In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
 
[code]...

Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

View 10 Replies View Related

Transact SQL :: Data Validation Of All Insert Statement Inside Server

Jun 25, 2015

In C# .NET I have the possible to create some validations of my data, with regulary expressions.  Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?

View 5 Replies View Related

Transact SQL :: INSERT Array Of Integer Into Table - Wrong Return Value Of Statement In ODBC

Sep 30, 2015

I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:

#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>

[Code] ....

In my code, I would like to Insert the array and then count the number of rows:

SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;

Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of  existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.

Hint : If I monitor the database using SQL Profiler. It looks like this:

Why this statement doesn't work correctly?

View 7 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

HOw Use DTS Call The VBA Code Execute?

Feb 17, 2004

Hi all,

I have some problem to need you all help. My problem is i have a Excel Worksheet with VBA coding, in VBA code it have modules,function and sub procedures. after that, i change the VBA coding in add-in file and save as .xla to make the user friendly.

But now, i need to use DTS to automatic to execute the excel file that contain VBA code every week. This is because the VBA in excel cannot automation excute by every week. So, i need use DTS to make it automation. The problem is i don't know how to use the DTS function to call the addin excel to execute? Which the connection and task compenent should i use? Or how do i paste the VBA code in DTS?

DTS is very new for me, are any help can provide me the solution? thanks

Regards,
Andy :confused:

View 5 Replies View Related

Dts : Call 2 Procs Within One 'execute Sql Task'

Jul 20, 2005

Hi :Can i call 2 procs within one task?I have sp_proc1 ? (and have declared one global variable as inputparameter)now i have another sp_proc2 which uses same input parameterbut if i write two statements like this within one task, i get anerrorexec sp_proc1 ?exec sp_proc2 ?I can solve the problem by writing them in 2 separate tasks, but wouldlike one task.Please help..thanksRashika

View 1 Replies View Related

Multiple Insert Call For A Table Having Insert Trigger

Mar 1, 2004

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

Thanks

View 10 Replies View Related

Can You Call/execute A Stored Procedure From An Expression?

Jun 6, 2007

I want to use Navigation to click-through to another report.

However, the source report is an MDX summary report, and the report I'm calling needs the entire Customer MDX tuple as a parameter. (the entire customer tuple is not available in my cube)

The text box has enough information to identify the customer, but not the entire tuple.

I want to pass the text box contents to a SP which returns the MDX string, and pass that to the new report.

I do not see how using another data source can help me accomplish this.

Basically, I am wondering is there a way I can have an expression similar to

=EXECUTE(spX, parm=txtbox.Value)

Thanks In Advance for any help...

View 1 Replies View Related

How To Call Stored Procedure In Execute SQL Task

Jan 8, 2008

Dear all!
I have a SSIS packet. I declare a parameter that have scope of packet. I want to pass this parameter into a stored procedure and call this stored procedure from "Execute SQL Task".
Please help me how to call stored procedure with parameter from "Execute SQL Task".
Thank you very much

View 4 Replies View Related

How Do Call Winzip From Execute Process Tasks?

Jun 12, 2006

Hi,

In my ETL project, l need to extract rawdata using winzip, and the DOS command l use is
c:program filesWinzipwinzip32.exe -min -e c:
awdata est.zip c:
awdata
where -min for minimize and -e for extract, c:
awdata est.zip for source file and c:
awdata for destination. It works fine by using DOS command.

l configure the Execute Process Tasks with the following parameters, i.e.
RequiredFullFileName : TRUE
Executable : c:program filesWinzipwinzip32.exe
Arguments : -min -e
WorkingDirectory : c:program filesWinzip
StandardInputVariable : User::gsRawFile
StandardOutputVariable : User::gsDestDir
Where User::gsRawFile = c:
awdata est.zip and User::gsDestDir = c:
awdata

But it can't work....no error return.

Any idea when l make the mistake?

Thanks.

Regards,
Yong Boon, Lim

View 3 Replies View Related

Function Call

Mar 28, 2006

can i make a function call from stored procedure

View 3 Replies View Related

Call A Function

Feb 26, 2008

Does anybody knows how to call a function from one VB source file to another VB source file??

I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??

View 1 Replies View Related

Need Help Understanding A Call To A Sql Function

Feb 25, 2008

Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
 
1  ALTER              PROCEDURE [dbo].[GetPortalSettings]2  (3     @PartyId    uniqueidentifier,45  AS6  SET NOCOUNT ON7  CREATE TABLE #Groups8  (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)

View 4 Replies View Related

Call Custom SQL Function In ASP.NET

May 9, 2006

I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function

SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length

View 1 Replies View Related

SQL Server Call C++ Function?

Dec 11, 2001

Hi,

Does anyone know if/how SQL server can call a function in a C++ library?

Cheers,

Xiaobing

View 4 Replies View Related

Overload Function Call

Aug 1, 2006

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions



thanks,

View 11 Replies View Related

Call Function For Inner Join

Aug 10, 2007



I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {


select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c

on c.Centerid = client.Centerid


Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid





I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View 1 Replies View Related

Call Function From Stored Procedure

Jan 19, 2007

Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible.  So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures.  When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno.  I want to put that current batchno into the Update statement and update the record with the current batchno.  Can someone point me in the right direction?  Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View 2 Replies View Related

Trying To Call A Function On A Weekly Timer

Sep 17, 2007

I'm not sure this is the place for this question, but not sure where else to go.  I've written asp.net  code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server? 

View 6 Replies View Related

How To Call A Function In Stored Procedure

Sep 12, 2012

How to call a sql function in stored procedure using Sqlserver 2008?

View 4 Replies View Related

How To Call A Userdefined Function Within A Stored

Aug 27, 2007

Hello All,
How do i call a user defined function from within a stored procedure,
I have created a simple function which takes firstname and lastname as parameters and returns the concatenated name string.
That part works.


declare @fullname varchar(400)
@fullName=getFullName(@firstname,@lastname)


As always thanks for all your input

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved