Failure Details: parameter

Showing posts with label parameter. Show all posts

Thursday, March 29, 2012

Getting A Query Parameter from Elsewhere

I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?

Getting a parameter to depend on another parameter.

I'm putting together a crosstab showing some facts for a year, compared to the year before (so we're showing 2 years). Years in the columns and a dimension in the rows. Nothing fancy, but we do want to put in a parameter so the users can choose which year they want to see. So I put a range and get two parameters. We now want the first parameter, the "from", to take the value of the "to" - 1. I can get the same value in there, but appearantly getting the previous year isn't as simple as just taking the parameter - 1.
A helping hand would be great. This all seems a bit overkill for what to me feels like an easy report, so I feel like I'm missing something.

We're building this report on a cube.

Hi,

In the Report Parameters dialog, set the Default value of your second parameter to Non Queried and enter the following in the Expression Editor

Code Snippet

=CINT(Parameters!<Param1>.Value) - 1

Replace with the name of your parameter and this should achieve what you want to do.

HTH.

Cheers,

Leigh

|||That gives me an error while previewing. More precisely, when I select the "to" in preview, I get:

Code Snippet

An error occured during local report processing.
Error during processing of of "FromYear" report parameter.

Can it be because the first (to) depends on a time dimension?

Tuesday, March 27, 2012

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

Monday, March 26, 2012

GetReportParameters ignores language="en-gb"?

Hello

I've integrated my reports into an app using the ReportViewer control, and I've created custom parameter selection controls. I want the user to be able to enter dates in dd/mm/yy format into a textbox. I've set the language of my reports to en-gb.

The SetParametes method of the ReportViewer control copes with this fine, but when I pass a date string in dd/mm/yy format into GetReportParameters, I get a 'parameter value not valid for its type' error. But it works for date strings in mm/dd/yy format, so it's as if GetReportParameters is ignoring my language setting.

Has anyone got any idea as to where I'm going wrong?

Thanks in advance

Dominic

After a little more playing around with this I've found that, on my machine, the SetParameters method always wants dates in dd/mm/yy format, whilst the GetReportParameters web method of the Report Service always wants dates in mm/dd/yy format, regardless of what I set the language of the report to.

Does anyone know what's going on?

GetReportParameters -> 400 - Bad Request

Hello,
I want to iterate throught the parameter collection of a report on a report
server running in sharepoint integration mode. To do that I use this code:
ReportingService2006 rs = new ReportingService2006();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
foreach (ReportParameter parameter in
rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
null, null, null))
{
Response.Write(parameter.Name);
}
Where RS_ReportParameters is like this in the web.config:
<add key="RS_ReportParameters"
value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
This gives me a 400 - Bad Request error.
Does anyone know how to solve this' Running the actual report goes well...
Best regards,
PeterSome more information:
when I use this URL for the webservice:
http://vpc-ssrs:8080/ReportServer/ReportService2006.asmx
I get: The request failed with HTTP status 401: Unauthorized
When I use this:
http://vpc-ssrs/_vti_bin/ReportServer/ReportService2006.asmx
I get: Bad request and also other methods don't work.
So how do solve this 401 error?
Best regards,
Peter
-- Original Message --
From: "Peter" <peter@.giessen.remthisfornospam.nl>
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Sent: Tuesday, November 06, 2007 10:46 AM
Subject: GetReportParameters -> 400 - Bad Request
> Hello,
> I want to iterate throught the parameter collection of a report on a
> report server running in sharepoint integration mode. To do that I use
> this code:
>
> ReportingService2006 rs = new ReportingService2006();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> foreach (ReportParameter parameter in
> rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
> null, null, null))
> {
> Response.Write(parameter.Name);
> }
> Where RS_ReportParameters is like this in the web.config:
> <add key="RS_ReportParameters"
> value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
> This gives me a 400 - Bad Request error.
> Does anyone know how to solve this' Running the actual report goes
> well...
> Best regards,
> Peter|||Nevermind, solved it... thanks for reading anyway ;)
"Peter" <peter@.giessen.remthisfornospam.nl> wrote in message
news:6D4F92F8-9BFC-4C1B-9C29-BEC328685CB4@.microsoft.com...
> Some more information:
>
> when I use this URL for the webservice:
> http://vpc-ssrs:8080/ReportServer/ReportService2006.asmx
> I get: The request failed with HTTP status 401: Unauthorized
> When I use this:
> http://vpc-ssrs/_vti_bin/ReportServer/ReportService2006.asmx
> I get: Bad request and also other methods don't work.
> So how do solve this 401 error?
> Best regards,
> Peter
> -- Original Message --
> From: "Peter" <peter@.giessen.remthisfornospam.nl>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Sent: Tuesday, November 06, 2007 10:46 AM
> Subject: GetReportParameters -> 400 - Bad Request
>
>> Hello,
>> I want to iterate throught the parameter collection of a report on a
>> report server running in sharepoint integration mode. To do that I use
>> this code:
>>
>> ReportingService2006 rs = new ReportingService2006();
>> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
>> foreach (ReportParameter parameter in
>> rs.GetReportParameters(ConfigurationManager.AppSettings["RS_ReportParameters"],
>> null, null, null))
>> {
>> Response.Write(parameter.Name);
>> }
>> Where RS_ReportParameters is like this in the web.config:
>> <add key="RS_ReportParameters"
>> value="http://intranet/sitedirectory/rapportage/rapporten/testvoorparameters.rdl"/>
>> This gives me a 400 - Bad Request error.
>> Does anyone know how to solve this' Running the actual report goes
>> well...
>> Best regards,
>> Peter
>

GetReportParameters - How can I get the default value of the parameter?

I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2), and one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1) one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.

Thanks,

-- Damodar PeriwalCan someone from Microsoft verify if this is a bug in SQL Server 2005 or not? Thanks.sql

Friday, March 23, 2012

GetDate() parameter in Function.... Not working...

I have a function that has the following two parameters. When I try to
compile I get an error on the GetDate().
If I remove the () from GetDate, it compiles but doesn't return the correct
results.
Example.
ALTER FUNCTION dbo.SiteMaxFileByExt
(
@.Days int = -1,
@.Date datetime = GetDate()
)
RETURNS TABLE
AS
RETURN SELECT TOP 100 PERCENT
tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
MAX(dbo.a_SiphonDetail.FileName) AS FileName
from tmSystem.dbo.vw_SiteExtensionList
where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
Thanks,
Roghttp://www.aspfaq.com/2439
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
> I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>|||Look up "user-defined functions, creating" in BOL.
It explains everything there.
In a nutshell, you cannot use GetDate() inside a UDF.
"Roger" <davisro@.netins.net> wrote in message
news:uM4z3r8KFHA.3788@.tk2msftngp13.phx.gbl...
>I have a function that has the following two parameters. When I try to
> compile I get an error on the GetDate().
> If I remove the () from GetDate, it compiles but doesn't return the
> correct
> results.
> Example.
> ALTER FUNCTION dbo.SiteMaxFileByExt
> (
> @.Days int = -1,
> @.Date datetime = GetDate()
> )
> RETURNS TABLE
> AS
> RETURN SELECT TOP 100 PERCENT
> tmSystem.dbo.vw_SiteExtensionList.SiteID, dbo.a_SiphonDetail.FileExt,
> MAX(dbo.a_SiphonDetail.FileDate) AS MaxDate,
> MAX(dbo.a_SiphonDetail.FileName) AS FileName
> from tmSystem.dbo.vw_SiteExtensionList
> where tmSystem.dbo.vw_SiteExtensionList.FileDate < @.date
>
> Thanks,
> Rog
>

GetDate() in UDF

I understand that you can not use GetDate() in a UDF.
I also can not pass a parameter to the UDF (because this is an Access to SQL
Server conversion, and the program that calls this UDF does not pass any
parameter to it).
So, I am trying to create a View for GetDate() like the codes below.
The issue is, I also need to select from another table (tblA) besides
getting the GetDate() value.
Is the following codes correct and efficient on how to do that ?
Thanks.
create view get_date
as
select getdate()dt
CREATE function dbo.udftemp()
returns @.myTable TABLE(id varchar(10),datex datetime)
AS BEGIN
INSERT INTO @.myTable(id,datex)
select tblA.id,dt
FROM tblA, get_date --select from tblA and the view
WHERE tblA.colA <> 'XYZ'
return
end>I understand that you can not use GetDate() in a UDF.
? This works on my 2005 server:
CREATE FUNCTION dbo.Func1 ()
RETURNS datetime
AS
BEGIN
return getdate()
END
GO
select dbo.Func1()
William|||We are using SQL2000, and unfortunately it does not work there.
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
> --
> William
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> ? This works on my 2005 server:
> CREATE FUNCTION dbo.Func1 ()
> RETURNS datetime
> AS
> BEGIN
> return getdate()
> END
> GO
> select dbo.Func1()
Yes, but it does not work on SQL 2000.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||yes - that should be fine
not much else to do in this case, though [unless you're using sql2005,
which allows non-deterministic functions in UDFs]
fniles wrote:
> I understand that you can not use GetDate() in a UDF.
> I also can not pass a parameter to the UDF (because this is an Access to S
QL
> Server conversion, and the program that calls this UDF does not pass any
> parameter to it).
> So, I am trying to create a View for GetDate() like the codes below.
> The issue is, I also need to select from another table (tblA) besides
> getting the GetDate() value.
> Is the following codes correct and efficient on how to do that ?
> Thanks.
> create view get_date
> as
> select getdate()dt
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(id varchar(10),datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(id,datex)
> select tblA.id,dt
> FROM tblA, get_date --select from tblA and the view
> WHERE tblA.colA <> 'XYZ'
> return
> end
>|||If it is a migration, then why not migrate to 2005 instead of 2000? Just
curious.
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
> We are using SQL2000, and unfortunately it does not work there.
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:O97ruV0BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>|||He never said anything about 2000, so I gave it a shot.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9734F019C8C9DYazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> Yes, but it does not work on SQL 2000.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your reply.
Is SQL 2005 still a beta product or is it a release product ?
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
> If it is a migration, then why not migrate to 2005 instead of 2000? Just
> curious.
> --
> William Stacey [MVP]
> "fniles" <fniles@.pfmail.com> wrote in message
> news:e%23lFUa0BGHA.2644@.TK2MSFTNGP09.phx.gbl...
>|||RTM. You can get Sql Express 2005 for free currently and buy the Std and
Enterprise versions as normal.
http://www.microsoft.com/sql/default.mspx
William Stacey [MVP]
"fniles" <fniles@.pfmail.com> wrote in message
news:uOzdgy8BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your reply.
> Is SQL 2005 still a beta product or is it a release product ?
>
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:OynIJo0BGHA.228@.TK2MSFTNGP12.phx.gbl...
>|||was it the RTM? or a previous CTP release?
i created a function exactly like in RTM developer edition and it worked
fine.
i don't think edition should matter - but release might.
fniles wrote:
> I just install SQL 2005 Standard Edition, and try the GetDate function
> again, but it still gives me the same error.
> Here is my function:
> CREATE function dbo.udftemp()
> returns @.myTable TABLE(datex datetime)
> AS BEGIN
> INSERT INTO @.myTable(datex)
> select getdate()
> return
> end
> The error I got was: "Invalid use of 'getdate' within a function."
> Do I need SQL 2005 Enterprise Edition for the GetDate() to work ?
> Thanks.
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:uPy03j0BGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
>
>

GETDATE() in a Function

Hi again.
I'm trying to write a user-defined function that accepts only one parameter, a date.
The function then calculates the amount of days elapsed between the specified date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
calculate the difference, but GETDATE() keeps on causing an error.
Can one use GETDATE() in a function?
I tried to call a stored procedure from the same function, and an error states that
only extended stored procedures or functions can be called from within the function...
Any way to bypass this?
Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?
|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:

>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter, a date.
>The function then calculates the amount of days elapsed between the specified date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error states that
>only extended stored procedures or functions can be called from within the function...
>Any way to bypass this?
|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

GETDATE() in a Function

Hi again
I'm trying to write a user-defined function that accepts only one parameter, a date.
The function then calculates the amount of days elapsed between the specified date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to try an
calculate the difference, but GETDATE() keeps on causing an error
Can one use GETDATE() in a function
I tried to call a stored procedure from the same function, and an error states that
only extended stored procedures or functions can be called from within the function..
Any way to bypass this?Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:
>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter, a date.
>The function then calculates the amount of days elapsed between the specified date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error states that
>only extended stored procedures or functions can be called from within the function...
>Any way to bypass this?|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

GETDATE() in a Function

Hi again.
I'm trying to write a user-defined function that accepts only one parameter,
a date.
The function then calculates the amount of days elapsed between the specifie
d date,
and the current system-date. I'm using DATEDIFF together with GETDATE() to t
ry and
calculate the difference, but GETDATE() keeps on causing an error.
Can one use GETDATE() in a function?
I tried to call a stored procedure from the same function, and an error stat
es that
only extended stored procedures or functions can be called from within the f
unction...
Any way to bypass this?Hi,
No, You cannot use getDate() inside a function. Non deterministic values can
not be used inside a function.(Te value og getdate changes every milli
second)
Solution1 :
The solution is Create view as select getDate() as currdate
and then use the view inside the function.
Thanks
Hari
MCDBA
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?|||Another option is to add a datetime parameter to your
function and pass GetDate() as the value for the parameter
when calling the function.
-Sue
On Wed, 12 May 2004 02:21:04 -0700, "Rival"
<anonymous@.discussions.microsoft.com> wrote:

>Hi again.
>I'm trying to write a user-defined function that accepts only one parameter
, a date.
>The function then calculates the amount of days elapsed between the specifi
ed date,
>and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
>calculate the difference, but GETDATE() keeps on causing an error.
>Can one use GETDATE() in a function?
>I tried to call a stored procedure from the same function, and an error sta
tes that
>only extended stored procedures or functions can be called from within the
function...
>Any way to bypass this?|||Rival
Here is another approach
CREATE FUNCTION dbo.My_Fn(@.dt AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
GO
SELECT dbo.My_Fn (GETDATE())
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:C3083184-B34B-4E01-A5B5-72ACBEB45C61@.microsoft.com...
> Hi again.
> I'm trying to write a user-defined function that accepts only one
parameter, a date.
> The function then calculates the amount of days elapsed between the
specified date,
> and the current system-date. I'm using DATEDIFF together with GETDATE() to
try and
> calculate the difference, but GETDATE() keeps on causing an error.
> Can one use GETDATE() in a function?
> I tried to call a stored procedure from the same function, and an error
states that
> only extended stored procedures or functions can be called from within the
function...
> Any way to bypass this?

Wednesday, March 21, 2012

GetDate() as parameter to UDF

I understand that GetDate() cannot be used within the *body* of a
user-defined function because it is non-deterministic.
I now have a table-valued function with which I want to use DateAdd and
GetDate() as *parameters* -- thus:
Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
I get "Incorrect syntax near '('.
The same happens if I eliminate the DateAdd. If I use hard-coded dates,
e.g. '1/1/2006', the function works as expected.
What am I doing wrong?
Thanks.
Daniel WilsonDaniel Wilson a crit :
> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
Use a view instead...
here is an exemple :
CREATE VIEW dbo.V_DATE_TIME_DTM
AS
SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
GO
CREATE FUNCTION dbo.F_CURRENT_DATE ()
RETURNS DATETIME
AS
BEGIN
-- date/time with time to ZERO
DECLARE @.D DATETIME
SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
FROM dbo.V_DATE_DAT
RETURN @.D
END
GO
A +

> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Declare variables and set their values and pass them to the function:
declare @.dt datetime
set @.dt = getdate()
select * from dbo.myfn(@.dt)
"Daniel Wilson" wrote:

> I understand that GetDate() cannot be used within the *body* of a
> user-defined function because it is non-deterministic.
> I now have a table-valued function with which I want to use DateAdd and
> GetDate() as *parameters* -- thus:
> Select * From dbo.MyFunction(DateAdd(mm,-6,getdate()), getdate()).
> I get "Incorrect syntax near '('.
> The same happens if I eliminate the DateAdd. If I use hard-coded dates,
> e.g. '1/1/2006', the function works as expected.
> What am I doing wrong?
> Thanks.
> Daniel Wilson
>
>|||Thank you, both.
We're working with the view solution b/c we're using the function within a
view.
dwilson
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:ee4GCboUGHA.4792@.TK2MSFTNGP14.phx.gbl...
> Daniel Wilson a crit :
> Use a view instead...
> here is an exemple :
> CREATE VIEW dbo.V_DATE_TIME_DTM
> AS
> SELECT CURRENT_TIMESTAMP AS DTM_TIMESTAMP
> GO
>
> CREATE FUNCTION dbo.F_CURRENT_DATE ()
> RETURNS DATETIME
> AS
> BEGIN
> -- date/time with time to ZERO
> DECLARE @.D DATETIME
> SELECT @.D = dbo.F_DATEONLY(DAT_DATE)
> FROM dbo.V_DATE_DAT
> RETURN @.D
> END
> GO
>
> A +
>
>
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

GetDate as Parameter for UDF Function returns table

Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql

GetDate as Parameter for UDF Function returns table

GetBytes

I'm creating an array of SqlParameters to send to SqlCommand.

Every parameter has to have a size, or I get an error.

Would it be efficient to get the bytes of every Object (parameter value) and pass it to the array?

What about truncation?

Does someone know a better method of doing this?

You can get length of all parameters using SqlCommandBuilder.DeriveParameters function. For more information you see http://www.davidhayden.com/blog/dave/archive/2006/11/01/SqlCommandBuilderDeriveParameters.aspx

Get Year

Posted - 01/08/2007 : 12:19:15

Hi,
In my report i have a parameter which displays the Year like this:2000,2001,..,2006.
When it is '1/1/2007' 2007 is automatically need to be add to Year parameter.This should automatically populate with each new year.
How to write the code for achieving this.

Thanks in advance

This 'should' provide the year for you.

SELECT year( getdate() )

|||

Hi,

I did this with this query:

Create Procedure rptCaseDetail_GetYear

Begin

Declare @.Year int

Set @.year=2000

Declare @.Current int

SET @.Current=Year(getdate())

declare @.jyear table

(

Year int

)

While (@.year<=@.Current)

Begin

insert into @.jyear Values( @.year)

SET @.Year=@.year+1

End

select * from @.Jyear order by year desc

End

|||I would create a function as it it easier to handle, joinable and more reusable:

ALTER FUNCTION dbo.NextYears

(

@.StartingYear SMALLINT,

@.AmountOfYears SMALLINT

)

RETURNS @.NextYears TABLE

(

TheYear SMALLINT

)

BEGIN

WHILE @.AmountOfYears >= 0

BEGIN

INSERT INTO @.NextYears

SELECT @.StartingYear + @.AmountOfYears

SET @.AmountOfYears = @.AmountOfYears -1

END

RETURN

END

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Monday, March 19, 2012

Get Value of Parameter whos name is in a variable

If I have a varialbe that contains the name of a Parameter in my stored
procedure is it possible to get the value of that parameter
Example:
CREATE PROCEDURE test
@.myParam1 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam1'
EXEC ('SELECT ' + @.ParamName)
the above code doesn't work but is there a way to do this?Don't enclose variables in quotes. Also, you need to use SET to assign a
value.
SET @.ParamName = @.myParam1
Select @.ParamName
will give you the result for debugging purposes.
If you plan on using dynamic SQL - i.e. EXEC(SQLStatement)
then check out Erland's article on it...
http://www.sommarskog.se/dynamic_sql.html
<regmellon@.gmail.com> wrote in message
news:1148326918.904046.55660@.g10g2000cwb.googlegroups.com...
> If I have a varialbe that contains the name of a Parameter in my stored
> procedure is it possible to get the value of that parameter
> Example:
> CREATE PROCEDURE test
> @.myParam1 varchar(50)
> AS
> DECLARE @.ParamName varchar(50)
> @.ParamName = '@.myParam1'
> EXEC ('SELECT ' + @.ParamName)
>
> the above code doesn't work but is there a way to do this?
>|||well that was a simplified example of what I am trying to do I am
actually looping through a record set of all the parameters of a stored
proc and then trying to store the values that were passed into the
stored proc in a table to make debuging easyer. So the parameter name
is stored in @.ParamName not the value of that parameter.
A better example would be :
CREATE PROCEDURE test
@.myParam1 varchar(50) ,
@.myParam2 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam' + cast(1, varchar(10))
EXEC ('SELECT ' + @.ParamName)
@.ParamName = '@.myParam' + cast(2, varchar(10))
EXEC ('SELECT ' + @.ParamName)|||well that was a simplified example of what I am trying to do I am
actually looping through a record set of all the parameters of a stored
proc and then trying to store the values that were passed into the
stored proc in a table to make debuging easyer. So the parameter name
is stored in @.ParamName not the value of that parameter.
A better example would be :
CREATE PROCEDURE test
@.myParam1 varchar(50) ,
@.myParam2 varchar(50)
AS
DECLARE @.ParamName varchar(50)
@.ParamName = '@.myParam' + cast(1, varchar(10))
EXEC ('SELECT ' + @.ParamName)
@.ParamName = '@.myParam' + cast(2, varchar(10))
EXEC ('SELECT ' + @.ParamName)|||TRY THIS,IT WILL SURELY WORK and can u plz specify wat type of parameter.Plz
give an example.
CREATE PROCEDURE test
@.myParam1 varchar(50)
AS
DECLARE @.ParamName varchar(50)
SET @.ParamName = @.myParam1
EXEC ('SELECT ' + @.ParamName)
--
MEHAK
"regmellon@.gmail.com" wrote:

> If I have a varialbe that contains the name of a Parameter in my stored
> procedure is it possible to get the value of that parameter
> Example:
> CREATE PROCEDURE test
> @.myParam1 varchar(50)
> AS
> DECLARE @.ParamName varchar(50)
> @.ParamName = '@.myParam1'
> EXEC ('SELECT ' + @.ParamName)
>
> the above code doesn't work but is there a way to do this?
>

Thursday, March 29, 2012

Getting A Query Parameter from Elsewhere

Getting a parameter to depend on another parameter.

Tuesday, March 27, 2012

Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Monday, March 26, 2012

GetReportParameters ignores language="en-gb"?

GetReportParameters -> 400 - Bad Request

GetReportParameters - How can I get the default value of the parameter?

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

getProcedureColumns behavior changed from SQLServer 2000 to 2005?

Friday, March 23, 2012

GetDate() parameter in Function.... Not working...

GetDate() in UDF

GETDATE() in a Function

GETDATE() in a Function

GETDATE() in a Function

Wednesday, March 21, 2012

GetDate() as parameter to UDF

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

GetDate as Parameter for UDF Function returns table

GetBytes

Get Year

Monday, March 19, 2012

Get Value of Parameter whos name is in a variable

Failure Details

Blog Archive

About Me