How to pass a field parameter into a table function?

[BrianD]

I'd like to return a table from a function using two passed parameters. One parameter works for a start date. The other, representing a parameter for a field, returns a false result.

I believe the right question to ask is how to pass a parameter for a field.

The requirement for a project is to create a job that runs an SSIS routine to export the most recent updated records from a table. For security reasons, I have very limited access to the database, and will walk in on a scheduled date to install the package and create the job.

As the SSIS package must return records by passing in a start date, or start key parameter, from which records will be extracted, it seems a user-defined function is the best option.

I'm beginning with the start date scenario. Start ID hasn't been considered yet, but I'm attempting to leave that option open by using @UserField parameter. There would also be @UserTable as a parameter (because I don't yet know which tables to query). For now, I'm still in troubleshooting mode, so I'm reducing the possible sources for error.

The function below should work (I think), but doesn't. Instead, it returns all records.

When I remove the @UserField parameter, it does correctly return a subset of the records after the passed start date.

I'm stumped and can't get past this problem. I could really use a little assist. Can anyone suggest a solution to the @UserField parameter issue?

Create table procedure and records are below as well.

Any help is very much appreciated.

Cheers.



--#########################
--CREATE PARAMETER FUNCTION
--RETURNS TABLE

USE OPCDC;
GO
IF OBJECT_ID (N'fn_ExportByStartParameter', N'IF') IS NOT NULL
DROP FUNCTION fn_ExportByStartParameter;
GO
CREATE FUNCTION fn_ExportByStartParameter (@UserField CHAR(30), @UserValue CHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM
OPCDC.dbo.Dummy
WHERE @UserField >= @UserValue
);
GO



--#########################
--CREATE DUMMY TABLE

USE [OPCDC]
GO

/****** Object: Table [dbo].[Dummy] Script Date: 08/24/2012 11:14:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Dummy](
[DummyPK] [int] IDENTITY(1,1) NOT NULL,
[NumberInteger] [int] NULL,
[Description] [nvarchar](20) NULL,
[DateStamp] [date] NULL,
[DateStamp2] [date] NULL
) ON [PRIMARY]

GO



DummyPK NumberInteger Description DateStamp DateStamp2
1 1 One 2012-01-01 2012-08-01
2 2 Two 2012-02-01 2012-08-02
3 3 Three 2012-03-01 2012-08-03
4 4 Four 2012-04-01 2012-08-04
5 5 Five 2012-05-01 2012-08-05
6 6 Six 2012-06-01 2012-08-06
7 7 Seven 2012-07-01 2012-08-07
8 8 Eight 2012-08-01 2012-08-08
9 9 Nine 2012-09-01 2012-08-09
10 10 Ten 2012-10-01 2012-08-10
11 11 Eleven 2012-11-01 2012-08-11
12 12 Twelve 2012-12-01 2012-08-12

[b***@gmail.com]

Or ...

Maybe the recommendation would be to back up to the requirement, and reconsider the use of a function.

Would more seasoned MSSQL programmers recommend a stored procedure, e.g., with one parameter for start date:


CREATE PROCEDURE [dbo].[sp_ExportByStartParameter] @spDate varchar(10)
AS
SELECT *
FROM OPCDC.dbo.Dummy
WHERE DateStamp >= @spDate
GO

[Erland Sommarskog]

Post by BrianD
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM
OPCDC.dbo.Dummy
);

This SELECT will return either all rows in the table, or none of them,
depending on the condition @UserField >= @UserValue, Normally a WHERE
clause includes one or more conditions on columns in the table.

Judging from your text, it appears that you hope that SQL Server will
do what you mean, but computers rarely do that. They do what they are
told. You give SQL Server two variables and asks it compare them. And
that is exactly what it does.

If I understand you requirements correctly, you will either extract rows per
date or per ID.

That is essentially two different queries, and two different functions:

CREATE FUNCTION fn_ExportByStartID (@startid int)
RETURNS TABLE AS
RETURN (SELECT * FROM dbo.tbl WHERE ID >= @startid)

CREATE FUNCTION fn_ExportByStartDate (@startdate datetime2(3))
RETURNS TABLE AS
RETURN (SELECT * FROM dbo.tbl WHERE datecol >= @startdate)

You may note that the parameters have different data types. You should avoid
relying on different data types, as this can result in unpleasant surprises.

As for whether this should be functions or stored procedures, I would say
stored procedures, since stored procedures permit to put in multiple
statements and has less restrictions than functions. It may not matter in
this simple case, but you may run into more complex cases further afield.

[BrianD]

Post by Erland Sommarskog

Post by BrianD
CHAR(10))
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM
OPCDC.dbo.Dummy
);

This SELECT will return either all rows in the table, or none of them,
clause includes one or more conditions on columns in the table.
Judging from your text, it appears that you hope that SQL Server will
do what you mean, but computers rarely do that. They do what they are
told. You give SQL Server two variables and asks it compare them. And
that is exactly what it does.
If I understand you requirements correctly, you will either extract rows per
date or per ID.
RETURNS TABLE AS
RETURNS TABLE AS
You may note that the parameters have different data types. You should avoid
relying on different data types, as this can result in unpleasant surprises.
As for whether this should be functions or stored procedures, I would say
stored procedures, since stored procedures permit to put in multiple
statements and has less restrictions than functions. It may not matter in
this simple case, but you may run into more complex cases further afield.
--
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Perhaps not a coincidence that I should hear from you, Erland. I was reading earlier two of your essays covering aspects of my question. They contributed to my concern about receiving validation for the best approach.

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dyn-search-2008.html

As such, I'm very pleased to hear from you.

With respect to your guidance that @UserField >= @UserValue only evaluates the two variables against one another (True or False), I hadn't considered that. Perhaps I might change the field variable to a string before inserting it into the SQL statement.

An If/Then or Select Case statement might help with choosing between date and key values. I had considered that. I'd prefer, I guess, to keep the logic outside of the function -- in this case, allowing the calling process to pass the required values based upon an evaluation in that calling process -- possibly variables in the SSIS package. This isn't a requirement. I'm receptive to any approach that works best.

As for data types, I was planning to use varchar (or nvarchar as may be required) for both date and key values, converting as necessary inside the function or procedure based upon an IsDate test, or evaluating the field name. Then again, there's a logical evaluation in this approach, which points back to the solution you offered. These values should always be consistent as they will be generated by the server, not a user.

I most appreciate that tip to pay attention to how the parameters are treated inside the SQL statement. I'll need to work on that.

Thanks for your thoughts, Erland. They're very much appreciated.

Cheers,
Brian