MS SQL Server :: Use Of User Defined In SELECT Clause

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
  select 1 id
  union select 2 id
  union select 11 id
) v
cross join (
  select 1 id
  union select 2 id
  union select 11 id
) u
order by
v.id
    ,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)   May 3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)   Feb 9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View Replies !

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma

View Replies !

How To Use A User Defined Stored Procedure/function In A Select Statement
How to use a User defined Stored Procedure/function In a Select statement.
eg. Select myproc(column1) from table1
How to register user defined functions( dlls) in SQL Server6.5/7 .
with Regards
K.Nageswara Rao

View Replies !

Gridview Select Statement With User.identity.name In The Where Clause
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated

View Replies !

User-defined Fun Or The System-defined Fun
hai,

how can i identify the function is user defined or the system defined function....................

View Replies !

Login Failed: Reason: User '_' Not Defined As A Valid User Of Trusted
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 Replies !

Login Failed- User: Reason: Not Defined As A Valid User Of A Trusted SQL Server Connection.
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 Replies !

Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave

View Replies !

Is User Defined Changes Possible?
I have installed to our server win2003 MSSQL Reporting service and client version on my pc together with visul studio. Everyting works well.

My questions are several.

1. When we use subscription by email. RS sends report by email as we selected.But i couldnt find the way to change the email layout.

2. Is it possible to change web layout. I couldnt find the cs source codes of the report server.

3. I want to remove refresh button from the users view. Is there any configuration for the layout where we can change it?

View Replies !

SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'

View Replies !

SQL User Defined View
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 Replies !

Help With User Defined Function
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 Replies !

User Defined Function
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 Replies !

User-defined Functions - Help!
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 Replies !

User-Defined Functions In DTS
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 Replies !

User Defined Function Help!
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 Replies !

User Defined Function
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 Replies !

RE-User Defined Function (2nd)
Can someone help me please, I have this function and it returns hours between 8h00 and 16h00 during the week

The help I need is, I need to return minutes instead of hours and it must not return any minutes in the following holiday table since we are not working
Holiday
2004-08-09 00:00:00.000
2004-09-24 00:00:00.000
2004-12-16 00:00:00.000
2004-12-26 00:00:00.000
2004-12-25 00:00:00.000
2005-01-01 00:00:00.000

CREATE FUNCTION dbo.fn_CalculateHours
(@DateFrom datetime, @DateTo datetime)
RETURNS int
AS
BEGIN
DECLARE @hFrom int, @hTo int
SET @DateFrom = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,1,@DateFrom),120) + ' 08:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,2,@DateFrom),120) + ' 08:00:00'
ELSE @DateFrom
END)
SET @DateTo = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,-2,@DateTo),120) + ' 16:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,-1,@DateTo),120) + ' 16:00:00'
ELSE @DateTo
END)
SELECT @hFrom = DATEPART(hour,@DateFrom)
SELECT @hTo = DATEPART(hour,@DateTo)
RETURN ((DATEDIFF(day,@DateFrom,@DateTo) - (DATEDIFF(week,@DateFrom,@DateTo) * 2) - 1) * 8) +
(CASE WHEN @hFrom < 8 THEN 8
WHEN @hFrom > 16 THEN 0
ELSE 16 - @hFrom
END) +
(CASE WHEN @hTo < 8 THEN 0
WHEN @hTo > 16 THEN 8
ELSE @hTo - 8
END)
END

View Replies !

User Defined Functions And OLE
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 Replies !

User Defined Variables
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 Replies !

User Defined Types
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 Replies !

User Defined Variable
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 Replies !

User Defined Functions
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 Replies !

User Defined Functions
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 Replies !

User Defined Function Help?
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 Replies !

User Defined Datatypes
Does anyone have a script or know a site that has standard datatypes already defined?
ie. datatypes for FullName, company, city, state, phone#, email...etc
If you know of any tried and true datatypes and maybe even the scripts to generate them,
I think that would be useful to anyone that visits this forum.

thanks,
Mike

View Replies !

User Defined Datatypes
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 Replies !

User Defined Function

View Replies !

User Defined Function
I only just been given this job as SQL server 7 "administrator". One of the many problems I am facing is trying to find out a way to write user defined functions - one of the millions of things you can do easily with Oracle. I have heard the rumours that SQL 7 does NOT come with "User Defined Functions" as a features. Please tell me this is not true.

And if it is true, please can anyone tell me a way to get around this?

Many thanks in advance

View Replies !

Help Pls!! 'Not Defined As Valid User"
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 Replies !

User Defined Function
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 Replies !

User-Defined Functions
Is there any way to create procedures that will act like functions so that they can be invoked within other SQL statements? All the documentation I have been able to find suggests coding a subquery to replace what a function would do. This is inefficient, cumbersome and creates a maintenance nightmare.
Perhaps there are third party tools? Workarounds? HELP!!!

View Replies !

User Defined SQL Function
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 Replies !

User Defined Datatypes
We are in the process of optimizing and setting standards for our databases.
I was told that using User Defined datatypes would help in performance as
well as standards.

Has anyone out there used these. And can you tell me how it has helped/hurt
your performance?

I trust and value everyone’s opinion on this discussion line. I have always
gotten great advice for you guys.

Thanks,
Dianne

View Replies !

User Defined Functions
Hi
I built an SQL2K database using computed columns that call user defined functions. These do either calculation based on a some fields in the table or a lookup on another table (the fields are arguments to the function)

Now I learn we are moving back to SQL7.0 for support reasons.

What is the best way to implement the above scenario in SQL7
(which means, minus the functions)

thanks
Liju

View Replies !

User Defined Functions In SQL 7.0
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 Replies !

User Defined Functions In SQL 2k
I expected that a parameter in the new udf's could be used dynamically. However, I am finding out that mabey they can't.
For instance:
Select * from udf_GetElection(2001,1234,1,'')
will return the election of person 1234 made in the year 2001 for plan1.

however if i only want one value from the table and want to include the function as a return field in a select, select errors out.

ie:
select top 10 ee.num,lname,fname,ssn, (SELECT optionid FROM UDF_GETELECTION(2001,7126,1,'')) as medoc
from employee ee

gives me:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ee'.

Yet a nested select which is what the udf really is works like this:

select top 10 num,lname,fname,ssn, (SELECT optionid FROM election where planyear =2001 and num = ee.num and planid =1 and electstatus = 'C') as medoc
from employee ee

returns valid data

Is there something I don't understand?

View Replies !

User Defined Functions
Hello,

Please help with UDF...

I have this UDF "fnEmpBirthDay", code is below:
CREATE function fnEmpBirthDay (@aFrom integer, @aTo integer)
returns table
as
Return
(
select * from vwEmpBirthDay
where Cast(substring(Convert(char(8),[emp birth date],112),5,4) as int) between @aFrom and @aTo
)

However, when i compare two queries like below:

1. select * from fnEmpBirthDay(101, 1031)

2. select * from vwEmpBirthDay
where Cast(substring(Convert(char(8),[emp birth date],112),5,4) as int) between 101 and 1031

They dont have same result. Why is it like that? is there something wrong with my query or something refresh...

However, When I edit the UDF fnEmpBirthDay and save it without any changes. Then two queries have the same results...

Please advise why is it happening like that?

Thanks in advance...

View Replies !

XML And User Defined Function
Hi,

Please answer to the following questions:
Q1. Is there any way to get text data type from a user defined function ?

Q2. How can result of a select query with "FOR XML RAW" clause be converted into a single string?

-----------
Background of the above 2 questions:

I want to execute a simple select query which besides selecting different columns also calls a user defined function which should execute a select query and return back the result as XML string to the calling select query.

View Replies !

User Defined Functions
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 Replies !

User Defined Functions
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 Replies !

Need Help With User Defined Function
I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

    If IFSDate < (Date + 14) Then
        BuyerDeltas = IFSDate - 3 - PODate
    ElseIf IFSDate < (Date + 29) Then
        BuyerDeltas = IFSDate - 7 - PODate
    ElseIf IFSDate > (Date + 28) Then
        BuyerDeltas = IFSDate - 10 - PODate
    Else
        MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
    End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

View Replies !

Help With User Defined Functions
Dear all,
I was given a project to transfer our database into sql server database.

In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .

Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \let int4 be a user definded function.

If i can€™t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?

Thanks in advance,
Best regards,
Hellen

View Replies !