User-Defined Func Syntax For Table Return
Oct 30, 2006
Hi all!
I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:
SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )
But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):
CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
GO
INSERT INTO tUser VALUES (111, 'Me')
INSERT INTO tAcctGroup VALUES (1, 'NY')
INSERT INTO tAcct VALUES (11, 1, 'New York City')
INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
INSERT INTO tAcctGroup VALUES (2, 'GA')
INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
INSERT INTO tAcct VALUES (22, 2, 'Savannah')
INSERT INTO tAcct VALUES (23, 2, 'Augusta')
INSERT INTO tAcctGroup VALUES (3, 'TX')
INSERT INTO tAcct VALUES (31, 3, 'Dallas')
INSERT INTO tAcct VALUES (32, 3, 'Houston')
INSERT INTO tAcct VALUES (33, 3, 'El Paso')
INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
INSERT INTO tAcctGroup VALUES (4, 'CA')
INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
INSERT INTO tMapUserToGroup VALUES (111,2)
INSERT INTO tMapUserToGroup VALUES (111,4)
GO
CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
AS
BEGIN
INSERT INTO @tAcct
SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
RETURN
END
GO
I know that I can do:
SELECT * FROM TestDB.dbo.ufnGetAcctList(4)
But I want the equivalent of:
SELECT AcctID, AcctName FROM tAcct
WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)
Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
AcctID AcctName
-----------------------------
21 Atlanta
22 Savannah
23 Augusta
41 Los Angeles
42 San Francisco
Any thoughts?
Thanks in advance for your help.
Cat
View 7 Replies
ADVERTISEMENT
Sep 2, 2005
In the script below is the DDL to create some tables and a UDF.What I'm interested in is the UDF at the end. Specifically, these fewlines:--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0If I uncommment CLOSE and DEALLOCATE and check the syntax I get amessage:"Incorrect syntax near keyword ELSE"Being a good little footsoldier, I want to release resourcesexplicitly, but clearly I'm putting the CLOSE and DEALLOCATE statementsin the wrong place.Could someone please tell me where I ought to put them so that thecursor is CLOSEd and DEALLOCATEd correctly.By the way, I am not after negative comments on the data design, or thelogic (or lack of it) in the function, just why the syntax erroroccurs.Thanks as everEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Employee]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PurchaseOrder]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[TimesheetItem]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Work]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[WorkOTRate]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[WorkOTRateDefaults]GOCREATE TABLE [dbo].[Employee] ([EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[DepartmentID] [int] NOT NULL ,[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StartDate] [smalldatetime] NOT NULL ,[EndDate] [smalldatetime] NULL ,[DefaultRatePerHour] [smallmoney] NULL ,[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[UserGroupID] [int] NOT NULL ,[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LastLogon] [datetime] NULL ,[PasswordChange] [smalldatetime] NULL ,[PreviousPassword1] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword2] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword3] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword4] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword5] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PurchaseOrder] ([WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[OrderDate] [datetime] NOT NULL ,[OrderValue] [money] NOT NULL ,[FixedPrice] [bit] NOT NULL ,[Prepaid] [bit] NOT NULL ,[AllocatedHours] [int] NULL ,[RatePerHour] [money] NULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[TimesheetItem] ([ItemID] [int] IDENTITY (1, 1) NOT NULL ,[EmployeeID] [int] NOT NULL ,[TypeID] [int] NOT NULL ,[Start] [smalldatetime] NOT NULL ,[DurationMins] [int] NOT NULL ,[WorkID] [int] NULL ,[WorkComponentID] [int] NULL ,[WorkItemID] [int] NULL ,[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[OffSite] [tinyint] NULL ,[TravelTo] [smalldatetime] NULL ,[TravelToMins] [int] NULL ,[TravelFrom] [smalldatetime] NULL ,[TravelFromMins] [int] NULL ,[TravelMileage] [int] NULL ,[NonChargeableMins] [int] NULL ,[OTAuthorisedID] [int] NULL ,[OTAuthorisedDate] [smalldatetime] NULL ,[Abroad] [bit] NULL ,[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ApprovalID] [int] NULL ,[AprovalDate] [smalldatetime] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Work] ([WorkID] [int] IDENTITY (1, 1) NOT NULL ,[WorkTypeID] [int] NULL ,[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Chargeable] [bit] NOT NULL ,[Complete] [bit] NOT NULL ,[ClientID] [int] NULL ,[ClientContactID] [int] NULL ,[Entered] [smalldatetime] NULL ,[ApprovalRequired] [tinyint] NULL ,[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRate] ([WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NOT NULL ,[TimeTo] [datetime] NOT NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRateDefaults] ([PKID] [int] IDENTITY (1, 1) NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NULL ,[TimeTo] [datetime] NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGO/*Function to determine the actual cost, in minutes, of a particularsegment of work. This is what it does, or is supposed to do.1. From the PARAMETER WorkID, determine the conclusion of the workblock associated with the TimesheetID - i.e. StartTime + DurationMins2. Establish whether there are records in the WorkOTRate tablecorresponding to this particular WorkID, weekday and time period3. If there are, get the amount of minutes by which the work blockcoincides.4. If there are no such records, get the default values from theWorkOTRateDefaults table5. If the block doesn't cross any boundaries then it's just regularwork, so just count the minutes.25/08/2005 EC*/CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@TimesheetID int)RETURNS floatASBEGINDECLARE@OTRateTimeFrom datetime,@OTRateTimeTo as datetime,@OTRateMultiplier as float,@EndTime datetime,@ReturnValue as float,@OrderRatePerHour as money,@EmployeeRatePerHour as smallmoney,@NumRecords as int,@WorkID as int,@EmployeeID as int,@StartTime as smalldatetime,@Duration as int,@Found as int,@Chargeable as bit-- Get the various bits and bobs needed for the calculationSET @ReturnValue = 0SET @Found = 0SELECT@WorkID = WorkID,@EmployeeID = EmployeeID,@StartTime = Start,@Duration = DurationMinsFROMTimesheetItemWHEREItemID = @TimesheetID-- If this work is NOT chargeable, return 0SELECT@Chargeable = ChargeableFROM[Work]WHEREWorkID = @WorkIDIF @Chargeable = 1BEGINSET @EndTime = DATEADD(mi, @Duration, @StartTime)-- Get the rate per hour for this workSELECT@OrderRatePerHour = RatePerHourFROMPurchaseOrderWHEREWorkID = @WorkID-- Get the rate per hour for the employeeSELECT@EmployeeRatePerHour = DefaultRatePerHourFROMEmployeeWHERE(EmployeeID = @EmployeeID)-- Find out if there's an OT Rate set up for this WorkIDSELECT@NumRecords = Count(*)FROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dd, @StartTime)))IF @NumRecords > 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dw, @StartTime)))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDEND--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateDefaultsWHERE(WorkDay = DATEPART(dw, @StartTime))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDENDCLOSE OTRateDEALLOCATE OTRate-- If there were no matching OT records, it's just a regular blockof work in normal hoursIF @Found = 0SET @ReturnValue = @DurationEND-- Finally we factor in the relation between the Employee's rate andthe Order's stated rate.RETURN (@ReturnValue * (@EmployeeRatePerHour / @OrderRatePerHour))ENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 3 Replies
View Related
Dec 29, 2014
What I want to do is return a row of data when my query doesn't return a record. I have two tables:
CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
[Code] ....
So when I run the following query:
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
abc-1-200.00
abc-1-500.00
def-0-0.00
View 4 Replies
View Related
Jul 20, 2005
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View 2 Replies
View Related
Apr 13, 2006
Hi!
I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.
CREATE TYPE employee_t AS OBJECT (
name VARCHAR(10)
jobDesc VARCHAR(15)
...
)
CREATE TABLE Employee of employee_t
Regards,
Timo
View 3 Replies
View Related
Oct 30, 2003
This is part of the codes of a stored procedure:
-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)
However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.
This user-defined data type is created in a user database only.
I tried to created the user-defined type into BOTH tempdb and master but same error.
How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?
In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)
and cerate udt_PRNum in tempdb only could solve it.
But now i want to use table variable.
It seems NOT work for my situation.
Any idea?
View 1 Replies
View Related
Aug 18, 2015
INSERT INTO @TESTTAB SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)
Note....The stored function returns a table.
Why doesn't this work ?:
SET @TESTTAB = (SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit))
I wonder if I need to establish a user-defined table type ?I really just want a pointer to the table, and not to have to create a new copy.
View 8 Replies
View Related
Dec 12, 2014
I've got a user defined table type called StringDictionaryTVP:
CREATE TYPE [dbo].[StringDictionaryTVP] AS TABLE(
[key] [varchar](500) NULL,
[value] [varchar](500) NULL
)
Ideally I would like to be able to have a # of columns and directions in this table like so:
DECLARE @OrderByClause StringDictionaryTVP
INSERT INTO @OrderByClause([key], [value])
values('gender','desc')
INSERT INTO @OrderByClause([key], [value])
values('name','asc')
Since our database can be a bit sizable, I'd also like to use Common Table Expressions so I can page through them fairly easy.So my standard cte is something like this:
DECLARE @PageIndex INT = 0
DECLARE @PageSize INT = 20
;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY name ) RowNum
from Users U)
SELECT * FROM results_cte
WHERE RowNum > @Offset AND RowNum <= @Offset + @PageSize
So where 'ORDER BY name' is I'd like to use the @OrderByClause in some sort of dynamic way. I've tried all kinds of stuff but even something like this doesn't get the actual column name I need
;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY (select top 1 [key] +' '+ [value] from @OrderByClause) ) RowNum
from Users U)
I may be chasing the wrong stick, but outside of dynamic sql, is something like this possible?
View 2 Replies
View Related
Mar 13, 2014
I have my defined table type created with
CREATE TYPE dbo.MyTableType AS TABLE
(
Name varchar(10) NOT NULL,
ValueDate date NOT NULL,
TenorSize smallint NOT NULL,
TenorUnit char(1) NOT NULL,
Rate float NOT NULL
PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);
and I would like to create a table of this type. From this answer [URL] .... the suggestion was to try
CREATE TABLE dbo.MyNewTable AS dbo.MyTableType
which produced the following error message in my SQL Server Express 2012:
> Incorrect syntax near the keyword 'OF'.
Is this not supported by SQL Server Express? If so, could I create it some other way, for example using `DECLARE`?
View 1 Replies
View Related
Sep 28, 2014
I have a user defined table type with two columns: ID: int, Value: float.Also, I have a table with different columns.I have a stored procedure:
ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
[code]....
I want to add "order by Value" to this stored procedure. Like below:
ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
[code]....
But this way is not true, and I get error when i debug my application.I fill this user defined table type in c# with data of a DataTable.
View 4 Replies
View Related
Dec 4, 2007
I am a SSIS novice, and need help to do the following:
Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.
I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?
One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query
exec( 'select * from ' + @table)
or just
select * from @table
Any suggestions will be appreciated.
View 10 Replies
View Related
Jun 6, 2007
Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.
there is one function in SQL : €œSELECT * FROM fn_my_permissions('TableName', 'OBJECT')€?
This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.
Is tehre any way to acheive that?
-Mani
View 1 Replies
View Related
Dec 29, 2005
Hi,
Following is the user defined function I want to get maximum value. It gives man an error "@strTableName must declare"
CREATE FUNCTION dbo.GetMaximum
(
@strFieldNamenvarchar(255),
@strTableName nvarchar(255)
)
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @maxID int
SELECT @maxID=(SELECT IsNull(MAX(@strFieldName),0)+1 FROM @strTableName )
RETURN (@maxID)
END
View 5 Replies
View Related
Aug 9, 2014
I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...
CREATE FUNCTION count_rows (@tablename varchar(100)
RETURNS int AS
BEGIN
DECLARE @emp_count AS int
declare @declaration varchar(100)
[Code] ....
The errors I am getting are as follows:
Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14
A RETURN statement with a return value cannot be used in this context.
View 9 Replies
View Related
Nov 4, 2015
Change structure of a User Defined Table Types
     field
([idConteudo] [int]
NULL)     to    Â
 ([idConteudo] [BigInt]
NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE(
[dsConteudo] [varchar](64) NOT NULL,
[idConteudo] [int] NULL,
[dtConteudo] [datetime] NULL DEFAULT (getdate())
)
GO
View 4 Replies
View Related
May 20, 2008
Hello,
We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .
What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.
Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:
Sec / Actual Rows Returned
0 0
10 237
20 447
30 481
40 585
50 655
60 725
70 793
80 860
90 940
100 1013
110 1081
120 1115
130 1151
140 1217
150 1250
160 1325
170 1325
180 1430
190 1467
200 1502
210 1539
220 1574
230 1610
240 1645
250 1679
260 1715
270 1750
280 1787
290 1822
300 1857
310 1892
320 1923
330 1956
340 1988
350 1988
360 2022
370 2060
380 2094
390 2094
400 2130
410 2160
420 2209
430 2237
440 2237
450 2274
460 2274
470 2308
480 2342
490 2380
500 2380
510 2418
520 2418
530 2451
540 2480
550 2493
560 2531
570 2566
It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).
The minute breakdown during my trial is as follows:
1 = 655 (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)
As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.
Allow me to explain the code in further detail:
[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{
ArrayList listItems = new ArrayList();
SPToSQLService service = new SPToSQLService();
[...]
DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service
//Load the DS to the ArrayList
return listItems;
}
public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{
ArrayList item = (ArrayList) obj;
col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}
As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.
Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.
Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.
Thanks,
Dragan Radovic
View 7 Replies
View Related
Aug 25, 2014
T-SQL script for the following request ?
Generate script for view dependencies of user defined table types ?
View 2 Replies
View Related
Nov 22, 2015
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
Â
- First_Name
 - Last_Name
 - Email
 - User_Field_2
 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
View 5 Replies
View Related
May 29, 2008
is there a way to create
"CREATE TABLE @table ( blah blah blah...."
from a table value function?
View 1 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
May 5, 2008
How do I return a list of all user tables in a database where table name starts with EmpID_
for example the table names are:
EmpID_Countries
EmpID_Regions
EmpID_States
EmpID_Colorado
EmpID_Arizona
etc etc...........
I am using SQL Server 2005. Thanks.
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
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Dec 11, 2007
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
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