My Query And User Defined Fuction Problem
Feb 22, 2006
I have a table which has six fields ID, dateDue, dateReceived, dueday. month, ContactFYE
my query looks like
select ID, DateDue, dateReceived, dueday, month, ContactFYE
from Report
where (dbo.Report.DateDue BETWEEN dbo.udfDisplayTime(dueday, month, ContactFYE) AND DateDue )
user defined function
CREATE FUNCTION dbo.udfDisplayTime ( @dueday int, @month int, @ContactFYE smalldatetime)
RETURNS smalldatetime AS
BEGIN
DECLARE @ReturnString AS smalldatetime
Declare @dueday1 as int
if (@month =1)
begin
set @dueday1 = @dueday -7
end
else if (@month =2)
begin
set @dueday1 = @dueday -14
end
else if (@month =3)
begin
set @dueday1 = @dueday -60
end
select @ReturnString = DATEADD ( dd, @dueday1, @ContactFYE)
Return @ReturnString
END
I got a incorrect result when using this query and user defined function.
the result that I got was out of between displaytime and datdue.
any idea for this , Thanks.
View 2 Replies
ADVERTISEMENT
Mar 15, 2007
Hi All
I am new to SSIS...
I have a function that I want to apply to many (around 50) columns in a Text File (Input)
Can I use my function in defining the Expression of all those columns?
TIA
View 7 Replies
View Related
Apr 30, 2008
I'm experiencing quite an interesting issue with our database. We're currently running SQL Server 2005 Standard (x64) (v9.00.3054.00) and I have a block of code that just insists on not working within a UDF. Everything I've read seems to point that I'm doing this corerctly but apparently I'm missing something.
Executing the query below:
DECLARE @sp DATETIME
DECLARE @ep DATETIME
DECLARE @trackType INT
SET @sp = '4/21/2008'
SET @ep = '4/28/2008'
SET @trackType = 1
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
Yields no issues however if I put this into a UDF, it runs and never finishes executing. I'm not quite sure what my issue was so I'm hoping someone could point out where my goof is.
Here is the function:
CREATE FUNCTION [dbo].[fnEmployeeSchedulingResults]
(
@sp DATETIME,
@ep DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT
c.unqempid, isnull(count(c.contactid),0) AS contacts, isnull(bookings.count,0) AS bookings, isnull(showed.count,0) AS showed, isnull(ow.totalPurchased,0) AS purchases, isnull(ow.totalSold,0) AS volume
FROM
contacts c WITH (NOLOCK)
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
b.unqempid
) AS bookings
ON bookings.unqempid = c.unqempid
LEFT JOIN
(
SELECT
b.unqempid, ISNULL(count(bookingid),0) AS count
FROM
bookings b WITH (NOLOCK)
INNER JOIN
contacts c WITH (NOLOCK)
ON c.contactid = b.relcontactid
WHERE
c.contactdt BETWEEN @sp AND @ep AND didshow > 0
GROUP BY
b.unqempid
) AS showed
ON showed.unqempid = c.unqempid
LEFT JOIN
(
SELECT
c.unqempid, count(leadid) TotalPurchased, sum(saleprice) AS TotalSold
FROM
ordermgmt.dbo.orders o WITH (NOLOCK)
INNER JOIN
ordermgmt.dbo.appointments a WITH (NOLOCK)
ON a.weborderid = o.orderid
INNER JOIN
contacts c WITH (NOLOCK)
ON c.personid = a.leadid
INNER JOIN
bookings b WITH (NOLOCK)
on b.relcontactid = c.contactid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid
) as ow
ON ow.unqempid = c.unqempid
WHERE
c.contactdt BETWEEN @sp AND @ep
GROUP BY
c.unqempid, bookings.count, showed.count, ow.totalPurchased, ow.totalSold
)
Any help would be greatly appreciated!
-Justin
View 19 Replies
View Related
Mar 11, 2008
What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view.
SELECT o.ordersid,
o.orders,
(SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total]
FROM order AS o
View 15 Replies
View Related
Apr 19, 2006
I have the following procedure, that calls a Padding function to pad characters to a field.
Here is what the procedure looks like
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
DECLARE @Query Varchar(5000)
SET @Query = 'UPDATE ' + @Table + '
SET ' + @Column + ' = dbo.Function_PadLeft(' + @Column + ', ''' + @PadChar + ''', ' + @PadToLen + ')'
EXECUTE(@Query)
GO
When I run this I get the error
Server: Msg 245, Level 16, State 1, Procedure Pad_Left, Line 13
Syntax error converting the varchar value 'UPDATE Lincoln
SET baths = dbo.Function_PadLeft(baths, '0', ' to a column of data type int.
But when I just run this query, it works
Code:
CREATE PROCEDURE [dbo].[Pad_Left]
@Table VARCHAR(255),
@Column VARCHAR(255),
@PadChar CHAR(1),
@PadToLen INT
AS
UPDATE Lincoln
SET Baths = dbo.Function_PadLeft(Baths, '0', 4)
GO
Why would one work but not the other? I don't understand, as they are the same thing, just one calls the function dynamically?
I must be missing something very obvious
Thanks for any help!
View 2 Replies
View Related
Nov 3, 2007
Hi all,
In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table:
dbo.LabTests Column_name Type Length Prec Scale
AnalyteID int 4 10 0 (Primary Key)
AnalyteName nvarch 510
CasNumber nvarch 510
Result numeric 5 8 2
Unit nvarch 510
SampleID int 4 10 0 (Foreign Key)
AnalyteID AnalyteName CasNumber Result Unit SampleID
1 Acetone 123-456-9 134.0 ug/L 1
2 Bezene 666-12-8 2.0 ug/L 1
3 Cobalt 421-008-7 10.0 ug/L 1
4 Acetone 123-456-9 201.0 ug/Kg 2
5 Bezene 666-12-8 1.0 ug/Kg 2
6 Cobalt 421-008-7 22.0 ug/Kg 2
7 Acetone 123-456-9 357.0 ug/L 3
8 Bezene 666-12-8 9.0 ug/L 3
9 Cobalt 421-008-7 56.0 ug/L 3
When I ran the following T-SQL code:
USE ChemAveRpd
GO
SELECT *
FROM dbo.LabTests as LabResults
Where AnalyteName = Acetone
GO
I got the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Acetone'.
Please help and tell me what right syntax I should write for [Where AnalyteName = Acetone] to generate a listing of LabResults for Acetone.
Thanks in advance,
Scott Chang
View 4 Replies
View Related
Apr 2, 2008
hai,
how can i identify the function is user defined or the system defined function....................
View 1 Replies
View Related
Jan 26, 2000
Our server has integrated security setup, upon startup of the server,
we have a continuous flow of error msg:
Login Failed: Reason: User '_' not defined as a valid user of trusted
connection.
The timing of these messages only around 30 seconds apart
The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ?
I recycle the server but didn't help.
I appreciate any help I can get ..
Thanks.
View 1 Replies
View Related
Apr 5, 1999
Hi,
i'm a newbie in SQL. I can't connect to SQL 6.5 server using ASP ADO. Below is the program code
<%
set conn=server.createobject("adodb.connection")
conn.open "dsn=Central;uid=sa;pwd="
%>
An error message appears
Login failed- User: Reason: Not defined as a valid user of a trusted SQL Server connection.
I've already created a ODBC System DSN named Central
parameter Server Local and used Trusted connection checked.
Then under sql Manager there is a sa with a blank password.
Please help.
View 1 Replies
View Related
Jan 10, 2007
I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE FUNCTION GetStartDate (@startdate datetime, @days int) RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules. If 4 business days means you count starting tomorrow, set counter to 0. If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
--If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from tFederalHoliday where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------
View 1 Replies
View Related
Aug 22, 2007
I've a view in which one of the fields were DATEDIFF(day,contract date,received) AS AgeOfAccount. I changed it to DATEDIFF(day, m.received, CASE WHEN m.clidlp > m.clidlc THEN m.clidlp ELSE m.clidlc END) * - 1 AS AgeOfAccount. When I execute the view I'm getting an error. But the results are getting displayed properly. That's what's puzzling me. Could someone tell me what the error is.
The following errors were encountered while parsing the contents of the sql pane.
Your entry cannot be converted to a valid date time value.
View 4 Replies
View Related
Sep 3, 2004
Hi,
I have a search returning many 'car' records (regno, mileage, color, etc)
In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.
Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.
I have written a function as such (with some help!):
ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END
I was hoping that I could simply use this in the 'car' SELECT statement.
ie:
SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc
I'm not even sure if the function works - How can I test in SQL analyzer?
any help much appreciated,
Pete
View 3 Replies
View Related
Aug 17, 2005
Hi everyone,
I am tring to pass acomma delimited string to a function and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this. How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks
View 1 Replies
View Related
Oct 11, 2000
Hi,
I know that we cannot have User Defined Functions in SQL 7.0, but is there a work around. I am trying to standardize the date according to the time zone using a function and use it as part of a select statement.
I cannot believe that is not possible in SQL 7.0, can somebody tell me what is the work around, I am running against time. I really appreciate any help on this one.
Satish.
View 1 Replies
View Related
Dec 14, 1999
How can I create a user defined function in SQL and call it inline from a SQL statement? I need the ability to execute a statement such as:
select myFunc(x,y) from table
I need myFunc(x,y) to return a value, not a recordset and use that value in the select statement. Any and all ideas would be appreciated.
View 1 Replies
View Related
Aug 2, 1999
Dear friends,
I am a new user for SQL server. Coming from an oracle background, I find it very difficult to live without user defined functions. At the moment, I urgently need to have a function returning an custom-made ID string, so that it can be used in one of my stored procedures. I have heard the rumours that SQL server 7 does NOT support user defined functions, (which SQL 6.5 does). I would be really grateful if anyone can clarify this for me, and if possible, suggest a get-around approach.
Thanks in advance!
Kai
View 2 Replies
View Related
Dec 16, 1999
all schedulled task on server failed for 18450 Process Exit Code 1. ...t Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 18450: [SQL Server] Login failed- User: sqlexec Reason: Not defined as a valid user of a trusted SQL Server connection.
Any information on how to solve it
View 1 Replies
View Related
Jan 5, 1999
Is it possible to change the owner of a User Defined Dataype? I've
recently had to change the owner of one of my databases and the
tables from a specific user to 'dbo'. The UDD's are the only objects
I am unable to change the ownership of.
Any suggestions anyone?
Thanks for any advice!
Toni Eibner
View 1 Replies
View Related
Jun 23, 2004
I am trying to compare the data from one table (shipments) with the data from a view. The point of the function is to match the correct territory with the account depending on the data. Basically, I this deals with accounts that are transfering from one territory to another. The transfers take effect on the first day of the month, retroactive, therefore we need to allocate the sales data to the correct territory in the shipments table. Here is my function. Can someone tell me how I can get this to work and insert a territory ID for the account that has transfered into the shipments table?
CREATE FUNCTION fnShipments ()
RETURNS @Shipments TABLE (AccountID CHAR(10), DateInvoice DateTime, DollarShipments Money, TerritoryID CHAR(10))
AS
BEGIN
INSERT @Shipments (AccountID, DateInvoice, DollarShipments, TerritoryID)
SELECT Shipments.AccountID, Shipments.DateInvoice, DollarShipments, ISNULL((SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth =
(SELECT MIN(EffectiveMonth)
FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND DatePart(m,Shipments.DateInvoice) < vwAccountTransfers.EffectiveMonth)),
(SELECT TerritoryID FROM vwAccountTransfers
WHERE Shipments.AccountID = vwAccountTransfers.AccountID
AND vwAccountTransfers.EffectiveMonth Is Null )
) AS Territory
FROM Shipments
ORDER BY Shipments.AccountID, Shipments.DateInvoice;
RETURN
END
View 1 Replies
View Related
Nov 10, 2004
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
I would also like now to be my getdate(), but it seems like you can't use it in UDF
View 1 Replies
View Related
Aug 18, 2006
Hi everyone
I am implementing an encrypted data system whereby captured data is encrypted using MS CAPICOM when written to DB and decrypted when displayed. It works well. However, each time you write the data back on update the encryption is different from the previous time.
I have a search screen that allows users to enter text data and submit it looking for matches. Of course, the user is entering unencrypted text and the DB data is encrypted.
This means that you can't encrypt the input data before you try to match because the encryption alogorithm used on the input data will not match that which was used to encrypt the DB data.
Are you with me so far?
So, you have to compare the uncencrypted input data with DECRYPTED DB data - in other words, decrypt the DB data on the fly within the where clause of your query.
I have accomplished this by writing a UDF that instantiates an instance of the CAPICOM encryption module and calling the UDF when applying the query
eg where udf(columnname1) = 'inputtext' or udf(columnname1) = 'inputtext'.
It works, I get the results that I want.
But, alas, the performance has taken a search severe hit.
Has anyone else ventured down this road?
Is there a better way of doing this?
Thanks
Ray
View 12 Replies
View Related
Jan 5, 2007
I am running a procedure that declares a table with a user defined variable called ZipCodes8000. This datatype is on the systypes table as a valid datatype that we created. Here is the code and the error that is returned. Can anyone help me understand why I receive the error that I do.
Code:
DECLARE @SortPlanBinZipCodeRanges TABLE
(
SortPlanBinZipCodeRangeIDINT IDENTITY (1, 1) NOT NULL,
SortPlanBinID INT NULL,
SortPlanID INT NULL,
BinTypeID TINYINT NULL,
BinFlagTypeID TINYINT NULL,
BinNumber INT NULL,
ZipCodeRanges ZipCodes8000 NULL
)
error:
Msg 2715, Level 16, State 7, Line 1
Column or parameter #7: Cannot find data type ZipCodes8000.
Thank you in advance.
Todd
View 8 Replies
View Related
Jan 16, 2008
I've been doing some reading on UDT's and have a question...
It was suggested in one of the pieces I read that you could create a UDT for, in their example, cities. And every table that had a reference to city could share this datatype. Now, my initial thought was "wow, what a great idea! I wouldn't have to remember the exact datatype for my primary keys (was it a char(5) or char(6)?) and have a "central depository" for my key datatypes.
So the first question is; what are the disadvantages of such a design?
And the second is; How do you update a UDT? If business requirements change and udt_city needs to be changed from varchar(30) to varchar(60), for example, what would be the way of echoing the change thoughout your database?
My gut reaction for the answers are
1) performance will decrease as effectively the dbms has to "parse" every insert/update to a UDT field in a different method.
2) create a new udt and alter any tables referencing the old one before dropping it.
What do we think?
View 14 Replies
View Related
Mar 18, 2008
Hello
Is there a way that I can create a user defined variable in my database? I have a fairly large chunk of xml that I would like to store in the database that can be updated by clients ( in rare cases ). I could use other mechinisms to allow access to it, but all other application related information is stored nicely in this one place, I hate to vary for this single piece.
Thank you for your time and thoughts.
Patrick
View 14 Replies
View Related
Jan 27, 2004
hi all,
I made myself a user defined function, it works great, but is there a way to make it available to all databases?
cheers,
alex
View 14 Replies
View Related
Feb 19, 2004
When I define a UDF in SQL 2000 I can't execute it without qualifiction. Here's the code:
CREATE FUNCTION GetLastShipDate(@DO_No CHAR(10))
RETURNS CHAR(10)
-- DROP FUNCTION GetLastShipDate
BEGIN
DECLARE @v_last_ship_date CHAR(10)
SELECT @v_last_ship_date = COALESCE(CONVERT(CHAR(10), MAX(Asn.Asn_Ship_DateTime), 110), '')
FROM Asn,Asn_Do
WHERE Asn_Do.Asn_Number=Asn.Asn_Number
AND Asn_Do.DO_Number = @DO_No
AND Asn.ASN_STATUS in ('SENT','RESENT')
RETURN @v_last_ship_date
END
So I have to execute this with:
SELECT dbo.GetLastShipDate('T010215004')
I want to execute it with
SELECT GetLastShipDate('T010215004')
What am I doing wrong?
View 7 Replies
View Related
Mar 20, 2004
I create the following UDF just for learning purposes and I cannot seem to get it to return a value.
Stored Procedure
REATE PROCEDURE usp_GetXSection
@fWidth float,
@fHeight float,
@fResult float OUTPUT
AS
BEGIN
set @fResult = [dbo].[XSECTION](@fWidth, @fHeight)
END
GO
Ane the following UDF
CREATE FUNCTION XSECTION
(@Width float, @Height float)
RETURNS float
AS
BEGIN
RETURN (@Width * @Height)
END
I call it using ADO in a c++ application, but all returns empty
Any ideas? C++ code to follow.
Mike B
void CFormTemplateSetup::OnCalculate()
{
_CommandPtr pCmd(__uuidof(Command));
_ParameterPtr pWidth(__uuidof(Parameter));
_ParameterPtr pHeight(__uuidof(Parameter));
_ParameterPtr pResult(__uuidof(Parameter));
try
{
pCmd->ActiveConnection = GetDocument()->GetConnection();
pCmd->CommandText = _T("usp_GetXSection");
pCmd->CommandType = adCmdStoredProc;
_variant_t vWidth, vHeight;
vWidth.vt = VT_R4;
vHeight.vt = VT_R4;
vWidth.fltVal = 10;
vHeight.fltVal = 10;
pWidth = pCmd->CreateParameter(_T("@fWidth"), adInteger, adParamInput, sizeof(int), vWidth);
pHeight = pCmd->CreateParameter(_T("@fHeight"), adInteger, adParamInput, sizeof(int), vHeight);
pResult = pCmd->CreateParameter(_T("@fResult"), adDouble, adParamOutput, sizeof(float), vtMissing);
pCmd->Parameters->Append(pWidth);
pCmd->Parameters->Append(pHeight);
pCmd->Parameters->Append(pResult);
_variant_t vResult = pResult->Value;
float fArea = pResult->Value.fltVal;
CString csArea;
csArea.Format("Area = %d", fArea);
::AfxMessageBox(csArea);
}
catch(_com_error& e)
{
::AfxMessageBox(e.Description());
}
}
View 1 Replies
View Related
Sep 8, 2005
Iam trying to create a geo-database.
For that i need to have a spatial data type called polygon which takes in values as int. so it can be declared as:
POLYGON(0 0, 100 0, 100 100, 0 100, 0 0)
How do i do that.
plz help.
---
Iam not real any more
Iam just an Illusion
---
View 20 Replies
View Related
Apr 15, 2007
Hi,
Iam new to sql server. I want to create a user defined function to get date in a year(starting day of a week-i.e sunday date) when we give week number as argument. can any body help on this issue?
View 13 Replies
View Related
Apr 17, 2007
Hi,
Iam working on one user defined function which will take week & year as argument and returns sunday date of that particular week in that year. for example if i give (15,2007) as argument it shud give 2007-04-08 as result. Plz anybody help this on this issue.
View 13 Replies
View Related
Jul 23, 2005
Is there a way to call a SQL user-defined function in a DTS, perhapsin an ActiveX transformation? I want the destination table to have avalue that was modified by a pre-defined function.-Noelle
View 1 Replies
View Related
Jul 6, 2006
Hi everyone,
Today while using user defined functions, I noticed that we must write dbo. before the function name which I did not conceive why it is used like that. Why are there an exception for functions ?
Why do we not have to use the same thing while using SP or not user defined functions ?
Thanks
View 4 Replies
View Related
Nov 15, 2006
declare @statusID varchar(100)
set @statusID = '1, 2'
--this works
select id from SplitWords(@statusID, ',')
--this doesn't
declare @sql nvarchar(2000)
set @sql = 'select * from Mast where ' + 'phmStatusID in ( select id from ' + 'SplitWords(@statusID, '', '')' + ') '
exec sp_executesql @sql
is what I'm trying to do not allowed? if so, whats the right way to do this?
any help appriciated. thanks.
View 4 Replies
View Related