function using comparing dates not working right

Hi,

I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows:
iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll
187189 3440 1 2006-04-24 note1
187189 3545 1 2006-06-22 note2
187189 3547 1 2006-06-22 note3
187189 3653 1 2006-08-10 note4
187189 3653 2 2006-08-10 note5

funtion will return = 2006_08-10 note4 note5 for iincidentid=187189
----------------
CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.incidentId int
declare @.worknoteid int
declare @.worknotesaveid int
declare @.seqnum int
declare @.dtEntryDate smalldatetime
declare @.worknoteall varchar(8000)
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime

select @.allnotes=''
select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks

declare CursorIncident CURSOR
LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID order by iWorkNoteId

OPEN CursorIncident
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall

--store 1st record of cursor
select @.worknotesaveid =@.worknoteid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
if @.dtEntryDate >=@.beginWEDate AND @.dtEntryDate <= @.currentWEDate
Begin
if @.worknotesaveid <> @.worknoteid
Begin
Select @.allnotes = @.allnotes + @.dtEntryDate + @.worknoteall
End
else
BEgin
select @.allnotes = @.allnotes + @.worknoteall
End

select @.worknotesaveid = @.worknoteid --save next worknoteId
End
else
Begin
select @.allnotes=''
End
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall
END --WHILE (@.@.FETCH_STATUS=0)

CLOSE CursorIncident
DEALLOCATE CursorIncident

return @.allnotes
END

----
Function not working right. I appreciate any help.
Thanks in advance.There is a problem with you logic. If there is more than one dtEntryDate value for an incidentId within the past four weeks, which date should your function return? It is only able to return a single date value.

Think about it.

In the meantime, here is a bit of wisdom: If you find yourself using a cursor, you are either an SQL guru or you are doing something wrong.

This is the type of logic you want to use (returns only notes, because unclear of date issue above):CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime

select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks

select @.AllNotes = Coalesce(@.AllNotes, '') + worknoteall
from dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID
and dtEntryDate between @.beginWEDate and @.currentWEDate
order by iWorkNoteId

return @.AllNotes
End|||Hi,
In my example above, it should return only the date for note4 and note5 (they are the same) since they have the same iWorkNoteId. In my code I'm checking that; otherwise, if the iWorkNoteId is different, then the date for that note is added to the @.allnotes.

I originally have a similar code as yours, but then I need to modify it because the users need the last 4 weeks notes in which the date is also a part of the allnotes.

I ran your code but it's alwyas giving NUll, although there is data. I think the date comparison is not working.

Thanks.|||Fine...what you supplied with the sample data and expected results is perfect...I just decided not to go through your code...

But this does what you want

USE Northwind
GO

CREATE TABLE myTable99(Incident int, col2 int, col3 int, col4 datetime, note char(10))
GO

INSERT INTO myTable99(Incident, col2, col3, col4, note)
SELECT 187189, 3440, 1, '2006-04-24', 'note1' UNION ALL
SELECT 187189, 3545, 1, '2006-06-22', 'note2' UNION ALL
SELECT 187189, 3547, 1, '2006-06-22', 'note3' UNION ALL
SELECT 187189, 3653, 1, '2006-08-10', 'note4' UNION ALL
SELECT 187189, 3653, 2, '2006-08-10', 'note5'
GO

SELECT * FROM myTable99
GO

SELECT *
FROM myTable99 o
WHERE EXISTS (
SELECT Incident
FROM myTable99 i
WHERE i.Incident = o.Incident
GROUP BY Incident
HAVING o.Col4 = MAX(i.Col4))
GO

DROP TABLE myTable99
GO

Function to return week number

Does anyone now how I can create a SQL function to return a w
number for any date with the following guidlines?
-Ws begin on a Thursday and end on a Wednesday.
-1st January is always in w 1.
-W 1 for this year would go from 30/12/2004 to 05/01/2005
I have been going round in circles trying to crack this one. I would
be very grateful if anyone has any ideas.Mark
Use DateFirst and DatePart
an Example in T-SQL:
Set DateFirst 4
Declare @.D DateTime Set @.D = '20050101'
Select DatePart(wk, @.d)
To make a function:
-- **********************************
Create Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return @.WkNo
End
-- ---
Use it like this:
Select dbo.WNumber('20050106',4)
"Mark Powell" wrote:

> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.
>|||The you tried the built-in DATEPART after setting appropriate SET DATEFIRST?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Powell" <mark@.muddyboots.com> wrote in message
news:f10f176.0503300059.5537cf67@.posting.google.com...
> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.|||Thanks - that's much better than anything I could come up with. The
only problem with this function is that 30/12/04 and 31/12/04 are
returning w 53 and I want them to return w 1.|||That IS counterintuitive, that the last days of 2004 should be considered as
being in the first w of 2005, But if you want it that way, modify
function so that if value calculates to 53, it returns 1 instead
Alter Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return Case @.WkNo When 53
Then 1 Else @.WkNo End
End
"mark@.muddyboots.com" wrote:

> Thanks - that's much better than anything I could come up with. The
> only problem with this function is that 30/12/04 and 31/12/04 are
> returning w 53 and I want them to return w 1.
>|||It doesn't make sense to me either, exept that it keeps the same w
number across the year end. It is how our client wants it, so it's the
way it needs to be done.
The next problem I have found is that the w number does not return
the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
imagine the +4 in your procedure should be a variable based on the
year, but I am not sure what it does?
Thanks for your help.
Mark|||Mark,
This was much harder than I thought at first, but this is it... Give it a
shot...
ALTER FUNCTION dbo.WNumber
(@.D DateTime, @.FDOW TinyInt)
Returns TinyInt
As
Begin
Declare @.FDOY Smallint, @.Shft Smallint, @.Yr SmallInt
Set @.Yr = Year(@.D)
Set @.FDOY = DatePart(dw, Str(@.Yr,4,0) + '0101') +
(@.@.DateFirst%7) - 1
Set @.Shft = (@.FDOY - @.FDOW + 7) % 7
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d) + @.Shft + 6) / 7
-- Now adjust for last partial w
Return Case When @.WkNo < 53 Then @.WkNo
When @.WkNo > 53 Or @.Shft < 5 Then 1
When @.Shft = 6 Then 53
-- Leap Year Consideration
When DatePart(dy, Str(@.Yr,4,0) + '1231') = 365
Then 1
Else 53 End
End
-- ****************************************
***************
And here is the code to test it...
Declare @.Y SmallInt Set @.Y = 2000
Set NoCOunt On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 4
Declare @.DP TinyInt,@.DF TinyInt,
@.DY SmallInt, @.I TInyInt
Declare @.DTs Table(DT TinyInt)
Set @.I = 0
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
--Set @.D = STR(@.Y, 4,0) + '0101'
Set @.DF = @.@.DateFirst
--Set @.DP = datepart(wday, @.D)
--Set @.DY = datepart(dy, @.D)
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' + Replace(Str(DT, 2,0),' ', '0')),2)
+
' ' + Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),' ', '0'),8) +
' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),'
', '0'),2), 4) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' + Replace(Str(DT+24, 2,0),' ',
'0')),2) +
' ' + Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24, 2,0),' ', '0'),8)
+ ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24,
2,0),' ', '0'),2), 4)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>|||Sorry , error in test script...
Use the following to test UDF In Prev Post
-- ****************************************
********
Set NoCount On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 2
Declare @.Y SmallInt Set @.Y = 2000
-- --
Declare @.I TInyInt Set @.I = 0
Declare @.DTs Table(DT TinyInt)
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
-- ---
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' +
Replace(Str(DT, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),2), @.FDOW) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' +
Replace(Str(DT+24, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),2), @.FDOW)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
-- ****************************************
********
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>

function to return table variable

my question has to do with the performance of user-defined function that
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:

> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>

Function to return substring letters only

I am hoping someone can help me with an example of a user function that will
return just the letters of a string. For example, sending 1RMB23 will return
RMB. Thank you.CREATE FUNCTION ReturnNonNumeric (@.Source varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @.ReturnNonNumeric varchar(100)
SET @.ReturnNonNumeric = REPLACE(@.Source, '0', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '1', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '2', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '3', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '4', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '5', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '6', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '7', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '8', '')
SET @.ReturnNonNumeric = REPLACE(@.ReturnNonNumeric, '9', '')
RETURN (@.ReturnNonNumeric)
END
--kludgy, but will do what you want. Stu|||quickly wrote this (just for fun)
CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
RETURNS varchar(255)
AS
BEGIN
Declare @.nPos Int,@.Strip varchar(150)
Set @.nPos=1
Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
While @.nPos<=Len(@.Strip)
Begin
Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
Set @.nPos=@.nPos+1
End
Return @.String
END|||Wow; that's nice.
<golf clap>
Stu|||Thanks, but if I thunk'd a moment longer, I would have trapped the single
quotes.
Return Replace(@.String,'','')|||Elegant, thank you. And thanks to Stu. I learned from yours also.
"John Cappelletti" wrote:

> quickly wrote this (just for fun)
> CREATE FUNCTION [dbo].[udf_AlphaOnly] (@.String varchar(255))
> RETURNS varchar(255)
> AS
> BEGIN
> Declare @.nPos Int,@.Strip varchar(150)
> Set @.nPos=1
> Set @.Strip='0123456789!@.#$%^&*()-_=+[]{}\|;:"<>,./?'
> While @.nPos<=Len(@.Strip)
> Begin
> Set @.String=Replace(@.String,Substring(@.Strip
,@.nPos,1),'')
> Set @.nPos=@.nPos+1
> End
> Return @.String
> END
>|||Sorry, I was clear until your "moment longer." You've got all single quotes.
You're trapping a string of 5 single quotes and replacing with two single
quotes? Where does that come from?
"John Cappelletti" wrote:

> Thanks, but if I thunk'd a moment longer, I would have trapped the single
> quotes.
> Return Replace(@.String,'','')
>|||I omitted the single quote from the strip string (and space).
Since I grab only 1 character at a time from the strip string, one final
replace is required, and the only way to put a single quote within single
quotes is to double them up.
The 5 should be 4 (I'm distracted by a bad movie)
Return Replace(@.String,'''','')
Good catch!
John
"richardb" wrote:
> Sorry, I was clear until your "moment longer." You've got all single quote
s.
> You're trapping a string of 5 single quotes and replacing with two single
> quotes? Where does that come from?
> "John Cappelletti" wrote:
>

function to return multi-selected values?

Is there a function to return the multi-selected parm values in a comma
delimited string? Using RS2005. dataset1 is the source of a parameter I'll
call STORE. I have this defined as a multi-select parm. Getting error
(cannot add multi value query parameter 'STORE' for dataset 'dataset2'
because it is not supported by the data extension) on trying to select parms
that are needed for my next dataset query unless I map the parameter for
dataset2 to an expression like this STORE = Parameters!STORE.Value(0) + "," +
Parameters!STORE.Value(1)
I cant do that in reality though - I was just seeing if it would work. I
need a function that I can map the parameter to which will feed to dataset2
all the STORE values chosen (similar to above). Why does it say that it is
not supported when I can type in comma delimited values in that parameter
field and run the report? Thanks in advanceIf I understand you have this...
Parameter 1, STORE, multi-select. Source is from query (dataset1) -
something like SELECT STORE_NAME FROM STORE.
dataset2 feeds the data in your report. It has to have a where clause
based on the values selected from the STORE parameter.
You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
are using a supported database: SQL Server or... i think... Oracle).
If you are using another dbms (we use sybase for some of our reports,
and we get that error) you have to be creative...
In that case, for dataset2, I have done the following.
="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
Join(Parameters!STORE.Value, "','") & "'"
There are single quotes in there to put them around each value in STORE
(assuming it is a string). If they are integers, you can lose the
single quotes.
Good luck,
Regards,
Dan|||Thanks Dan,
Actually I am using db2 version 8.1 (database lives on a unix box). You
understood correctly that I have dataset1 which provides the selections for
the parm STORE. The parm is then mapped to a query parm that is needed for
dataset2 which is a stored procedure (no sql to manipulate there). Any ideas
there? Again ... thanks
"Dan" wrote:
> If I understand you have this...
> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> something like SELECT STORE_NAME FROM STORE.
> dataset2 feeds the data in your report. It has to have a where clause
> based on the values selected from the STORE parameter.
> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> are using a supported database: SQL Server or... i think... Oracle).
> If you are using another dbms (we use sybase for some of our reports,
> and we get that error) you have to be creative...
> In that case, for dataset2, I have done the following.
> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> Join(Parameters!STORE.Value, "','") & "'"
> There are single quotes in there to put them around each value in STORE
> (assuming it is a string). If they are integers, you can lose the
> single quotes.
> Good luck,
> Regards,
> Dan
>|||When you pass a multi-select parameter to a stored procedure you are sending
a comma separated string of values. Why your stored procedure is having
trouble with it I don't know.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> Thanks Dan,
> Actually I am using db2 version 8.1 (database lives on a unix box). You
> understood correctly that I have dataset1 which provides the selections
> for
> the parm STORE. The parm is then mapped to a query parm that is needed
> for
> dataset2 which is a stored procedure (no sql to manipulate there). Any
> ideas
> there? Again ... thanks
> "Dan" wrote:
>> If I understand you have this...
>> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
>> something like SELECT STORE_NAME FROM STORE.
>> dataset2 feeds the data in your report. It has to have a where clause
>> based on the values selected from the STORE parameter.
>> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
>> are using a supported database: SQL Server or... i think... Oracle).
>> If you are using another dbms (we use sybase for some of our reports,
>> and we get that error) you have to be creative...
>> In that case, for dataset2, I have done the following.
>> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
>> Join(Parameters!STORE.Value, "','") & "'"
>> There are single quotes in there to put them around each value in STORE
>> (assuming it is a string). If they are integers, you can lose the
>> single quotes.
>> Good luck,
>> Regards,
>> Dan
>>|||I dont know either. If I take the multi-value off of that parm and type in 2
comma separated values then it works just fine. Something about the
multi-value it doesnt like or I am setting up wrong. This is db2 v8.1 ...
the stored proc works to accept comma-separated values when typed in ... just
not when selected as multi-value ... I cant see what I would be doing wrong?
"Bruce L-C [MVP]" wrote:
> When you pass a multi-select parameter to a stored procedure you are sending
> a comma separated string of values. Why your stored procedure is having
> trouble with it I don't know.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> > Thanks Dan,
> > Actually I am using db2 version 8.1 (database lives on a unix box). You
> > understood correctly that I have dataset1 which provides the selections
> > for
> > the parm STORE. The parm is then mapped to a query parm that is needed
> > for
> > dataset2 which is a stored procedure (no sql to manipulate there). Any
> > ideas
> > there? Again ... thanks
> >
> > "Dan" wrote:
> >
> >> If I understand you have this...
> >>
> >> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> >> something like SELECT STORE_NAME FROM STORE.
> >>
> >> dataset2 feeds the data in your report. It has to have a where clause
> >> based on the values selected from the STORE parameter.
> >>
> >> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> >> are using a supported database: SQL Server or... i think... Oracle).
> >>
> >> If you are using another dbms (we use sybase for some of our reports,
> >> and we get that error) you have to be creative...
> >>
> >> In that case, for dataset2, I have done the following.
> >>
> >> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> >> Join(Parameters!STORE.Value, "','") & "'"
> >>
> >> There are single quotes in there to put them around each value in STORE
> >> (assuming it is a string). If they are integers, you can lose the
> >> single quotes.
> >>
> >> Good luck,
> >>
> >> Regards,
> >>
> >> Dan
> >>
> >>
>
>|||Bruce may be better versed in some of this than I am, but from what I
understand, a comma separated list is only sent through to the dbms
through a data extension that supports multi-value parameters. I don't
believe db2/odbc does.
What does the text from your dataset 2 look like?
If you changed it to something like the following, it should work.
="exec storedprocname '" & Join(Parameters!STORE.Value, "','") & "'"

Function to return all days in a month

Hi,

What I need to do is to join a table with all days in a range that the users inform with another table that has the values so I can have a montly report separated day by day...I don't want to create the table manually cause the range is different...

I tried to do with cursor but it's to slow..

Any better solution?

Thanks

You don't say what version of SQL Server you are using. If you are using SQL Server 2005 then you can use a recursive common table expression to do what you want. Here is a sample:

Code Snippet

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

-- Use a common table expression to loop over a set of years

;WITH DayList (DayDate) AS

(

SELECT @.RepMonth

UNION ALL

SELECT DATEADD(d, 1, DayDate)

FROM DayList

WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @.RepMonth)))

)

SELECT *

FROM DayList

|||

OK thanks. It solves for my other program....But I still need it to sql2000... =)

Thanks

|||

Been thinking about it since posting previously and I came up with the following code which works if you don't have SQL Server 2005. It depends upon having a table which simply contains the values from 0 to at least 30 (giving 31 records for 31 days). I put up to 32 in to show check the logic worked for any month - and that it can have any number of records in it. In the sample this is a table variable - but in a real system this would probably just be a little standard table.

Code Snippet

DECLARE @.ValueList table

(

ValueID int

)

SET NOCOUNT ON

INSERT INTO @.ValueList Values ( 0)

INSERT INTO @.ValueList Values ( 1)

INSERT INTO @.ValueList Values ( 2)

INSERT INTO @.ValueList Values ( 3)

INSERT INTO @.ValueList Values ( 4)

INSERT INTO @.ValueList Values ( 5)

INSERT INTO @.ValueList Values ( 6)

INSERT INTO @.ValueList Values ( 7)

INSERT INTO @.ValueList Values ( 8)

INSERT INTO @.ValueList Values ( 9)

INSERT INTO @.ValueList Values (10)

INSERT INTO @.ValueList Values (11)

INSERT INTO @.ValueList Values (12)

INSERT INTO @.ValueList Values (13)

INSERT INTO @.ValueList Values (14)

INSERT INTO @.ValueList Values (15)

INSERT INTO @.ValueList Values (16)

INSERT INTO @.ValueList Values (17)

INSERT INTO @.ValueList Values (18)

INSERT INTO @.ValueList Values (19)

INSERT INTO @.ValueList Values (20)

INSERT INTO @.ValueList Values (21)

INSERT INTO @.ValueList Values (22)

INSERT INTO @.ValueList Values (23)

INSERT INTO @.ValueList Values (24)

INSERT INTO @.ValueList Values (25)

INSERT INTO @.ValueList Values (26)

INSERT INTO @.ValueList Values (27)

INSERT INTO @.ValueList Values (28)

INSERT INTO @.ValueList Values (29)

INSERT INTO @.ValueList Values (30)

INSERT INTO @.ValueList Values (31)

INSERT INTO @.ValueList Values (32)

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

SELECT DATEADD(d, ValueID, @.RepMonth)

FROM @.ValueList

WHERE (DATEADD(d, ValueID, @.RepMonth) < DATEADD(m, 1, @.RepMonth))

Put an order by on the selects if you need the records in a particular order.

|||

You might want to give consideration to using a calendar table; you can find an article that describes this type of table here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

GREAT !!! Thanks..you're the master....

Do you have msn....I'll add you to never forget !(or add me...it's my nickname @.hot....

|||

You may find having a 'Calendar' table to be constantly useful.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

create function udf_MakeDatesTable(

@.dtInput smalldatetime

,@.Months tinyint = 1

)

/*

this function will allow you get a table of dates

for any range of months you like,

starting at the input date.

See below for the usage

*/

returns @.TableOfDates table(dtTemp smalldatetime)

as

begin

declare @.dtEnd smalldatetime

--declare @.dtInput smalldatetime

--set @.dtInput = '1/5/2005 05:12'

--This line will strip out the Time component

set @.dtinput = convert(varchar,@.dtInput,106)

--Find our end date. Always @.Months after @.dtInput

set @.dtEnd = dateadd(m, @.Months, @.dtInput)

--declare @.TableOfDates table(dtTemp smalldatetime)

while @.dtInput < @.dtend

begin

insert into @.TableOfDates select @.dtInput

set @.dtInput = @.dtInput + 1

end

return

end

go

select * from dbo.udf_MakeDatesTable('1/15/2005',1)

Function to return "remaining" of field after it finds a character in the field.

Hi,

another problem I have is that have compounded fields in my sql table.

Example

product@.customer

I need a simple function to return "customer", so it should return the value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.

regards
JorgenSolutions was :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [sapserviceaccount].[UNTRUNK2] (@.inp as varchar(100))

RETURNS varchar(20) AS

BEGIN

declare @.out varchar(20);

if LEN(@.inp) 0

begin

set @.out = right( @.INP, (LEN(@.INP)-CHARINDEX('@.', @.INP)))

end

else

begin

set @.out = @.inp;

end

return @.out

END

"Jorgen [DK/2600]" <nyhedsgruppe_hejhej_@.gmail.comwrote in message
news:45daf1e7$0$90272$14726298@.news.sunsite.dk...

Quote:

Originally Posted by

Hi,
>
another problem I have is that have compounded fields in my sql table.
>
Example
>
product@.customer
>
I need a simple function to return "customer", so it should return the
value
after "@.", unfortunate "@." will sometimes be character number 6, sometimes
character number 7 etc.
>
regards
Jorgen
>
>
>

function to format Time (24 hours- 5 digits)

Hi

I know this is not a complicated matter.

I'm using this code in my SP to return the time formated in 24 hours

DECLARE @.Hora VARCHAR(5)
SET @.Hora = CONVERT(char(2), DatePart (hh,GetDate())) + ':' + CONVERT(char(2), DatePart (mi,GetDate()))
print @.Hora

te problem is that when the time is any before 10 am for instance 7:23 am the sp returns 7 :23 and I need it ro return 07:23.

What function allows me to insert that '0' to complete the 5 digits format in this case?

thanks

Try this instead:

SELECT My24HrTime = convert( char(5), getdate(), 114 )

For future reference, you may wish to refer to the 'style' chart in Books Online, Topic: 'Cast and Convert'.

(The number 114, above is the 'style' used in the convert() function.)

Function to find character in string

Hi:
I was looking through BOL for a function that would return the
numerical place where a string first occurs (similar to Crystal
Report's 'instr' function):
so
FUNKYFUNCTION('abcdefgd','d',1)
would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
argument is the starting place in the string.
Basically, what I want to do is trim everything after the first space
is found. I might have certain field values that end up as:
5445 UNWANTEDSTRINGETXT
I want this field to be changed to '5445' when it finds unwanted text.
So having this function would make this easy. Or maybe there is another
way in the absence of such a function?
Thanks for the help,
Kaydadeclare @.str varchar(80)
set @.str = '5445 UNWANTEDSTRINGETXT'
SELECT CHARINDEX(' ', @.str),
CASE WHEN CHARINDEX(' ', @.str) = 0
THEN @.str
ELSE SUBSTRING(@.str,1,CHARINDEX(' ', @.str)-1)
END
On 16 Feb 2006 16:13:03 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>I was looking through BOL for a function that would return the
>numerical place where a string first occurs (similar to Crystal
>Report's 'instr' function):
>so
>FUNKYFUNCTION('abcdefgd','d',1)
>would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
>argument is the starting place in the string.
>Basically, what I want to do is trim everything after the first space
>is found. I might have certain field values that end up as:
>5445 UNWANTEDSTRINGETXT
>I want this field to be changed to '5445' when it finds unwanted text.
>So having this function would make this easy. Or maybe there is another
>way in the absence of such a function?
>Thanks for the help,
>Kayda|||The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.
Regards,
Willson
"Kayda" wrote:

> Hi:
> I was looking through BOL for a function that would return the
> numerical place where a string first occurs (similar to Crystal
> Report's 'instr' function):
> so
> FUNKYFUNCTION('abcdefgd','d',1)
> would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
> argument is the starting place in the string.
> Basically, what I want to do is trim everything after the first space
> is found. I might have certain field values that end up as:
> 5445 UNWANTEDSTRINGETXT
> I want this field to be changed to '5445' when it finds unwanted text.
> So having this function would make this easy. Or maybe there is another
> way in the absence of such a function?
> Thanks for the help,
> Kayda
>

Function that returns table

Hello,
Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter?

This function will return a variable temp table...

GO

SETANSI_NULLSOFF

GO

SETQUOTED_IDENTIFIERON

GO

CREATEFUNCTION [dbo].[UDF_ADMIN_GetNewSortList]

(

@.tText TEXT,

@.iSortStart INT

)

RETURNS @.NewList TABLE

(

iNewSortOrder INT

)

AS

BEGIN

Declare @.tmpList TABLE

(

iOldSortOrder INT

)

INSERTINTO @.tmpList(iOldSortOrder)

SELECT

[cValue]

FROM

[CPDB].[dbo].[udfCharListToTable]

(

@.tText

,','

)

DECLARE @.iCount INT

SELECT @.iCount =Count(*)

FROM @.tmpList

DECLARE @.iStart INT

SET @.iStart = 1

WHILE @.iStart <= @.iCount

BEGIN

INSERTINTO @.NewList(iNewSortOrder)

VALUES(@.iSortStart + @.iStart)

Set @.iStart = @.iStart + 1

END

RETURN

END

|||Yes, but a table with one column. I want to return a table that has 0 or more columns (depends on the application). In your example the function returns a table with only one column (iNewSortOrder).
Now I have:
CREATE PROCEDURE dbo.GetConfiguration
(
@.type varchar(MAX)
)
AS
IF @.type = 'Tank' SELECT * FROM Tank();
That stored procedure returns a table. Now I want to use this table in a Function... eg:
CREATE FUNCTION dbo.Configuration
(
@.type varchar(max)
)
RETURNS TABLE
AS
RETURN SELECT * FROM GetConfiguration @.type
Obviously that doesn't work. Thanks for any ideas!!!
|||"That does not work" answers are welcome, too (so I know that there's no way) ;-)
|||

Looking at the options for Create Function there are two choices that return a table. The first is an in-line function. In this case you do not have to define the columns of the table, but you can only have a single select statement. So, that won't work.

It is possible to have a general multi-statement function that returns a table, but that requires that the columns be known in advance. Assuming that there was value in having a function that had constant columns, but came from different tables -- maybe you have a number of name-value-pair type tables -- you now have another problem of how to populate the table variable. The most general approach would be to use dynamic SQL, but you cannot populate table variables with dynamic SQL. You could use a series of IF statements, but now you are pretty far away from your original goal.

In general, this is not the correct approach to take.

Have you looked into using Dynamic SQL to solve your problem?

Function Returning error during compilation.....

Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@.cg1 varchar(25)) returns @.rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @.t1_sys char(10),@.t1_all varchar(11)
declare @.temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@.cg1,1,2)='Q$')
set @.cg1 = (select substring(@.cg1,3,len(@.cg1)) where substring(@.cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @.temp_qcard values(@.cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
WHILE @.@.FETCH_STATUS = 0
BEGIN

insert into @.temp_qcard values(@.t1_all)

declare @.t2_sys char(10),@.t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t2_all)

declare @.t3_sys char(10),@.t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.temp_qcard values(@.t3_all)
FETCH NEXT FROM c3 INTO @.t3_sys,@.t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @.t2_sys,@.t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @.t1_sys,@.t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @.rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @.temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ...
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.Try changing the declaration of all your cursors like this

DECLARE c1 SCROLL CURSOR READ_ONLY FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @.cg1 and groups_alldata like 'Q$%' and groups_seq>=1

Dont write "FOR READ_ONLY" at the end of the DECLARE. Instead, write it before the "FOR select...."

Function Return Value

I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @.MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "\" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQL\Data" instead of "D:\MSSQL\Data" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

Any suggestions? Thank you.

HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

BEGIN

--Extract the file path for the database MDF physical file.
SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END

HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON

DECLARE
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.

SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATHAny suggestions?

YEah, rethink what you're doing...

MOO|||If you didn't have any information that would possibly be helpful in my question, please leave future posts to those who would be more intelligent in their responses.

If you see something that I am doing wrong, then why not offer a suggestion instead of either keeping the answer to yourself or acting more intelligent than what you are. After all, this is what this forum is intended for.

Thank you.|||Sorry you feel that way...

If you can supply us with what you're doing, I'm sure the people here can assist...if you don't like what I say, I'm sure someone will step...need more details though...

This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Any "AUTO-ADMIN" stuff is always risky...(my own opinion) MOO

Why do you have to do this? Are you releasing hundreds of databases?

Also, unless there are performance issues involved, why deviate from standard practices...

AND HOW DARE YOU ACCUSE ME OF BEING INTELLIGENT!

The nerve...|||CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

The return value of the function is declared as NVARCHAR, a single character. You need to declare the return value as a character array.

RETURNS NVARCHAR(1000)|||USE Northwind
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS

BEGIN

SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END
GO

SET NOCOUNT ON
DECLARE @.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATH
SET NOCOUNT OFF
GO

DROP FUNCTION fn_sqlmgr_get_mdf_filepath
GO|||Yeah .. but in spite of all the help from BK ... you need to rethink it anyway ;)|||I think that Brett saw you doing something potentially VERY dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP|||I think that Brett saw you doing something potentially [i[VERY[/i] dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP

You know the funny thing about this?

Trying to build a rocket ship, but can't get past retrun nvarchar|||And i dont remember exactly ... but isnt there a registry key we could read to get the default location where a file would be created ...

Hmmm ... what the hell I am thinking .. the files would be created int the default location anyway ... if you do not specify ... so whats with the function ... i am confused (once again ;))|||Thank you Homer37 for your assistance. Your answer worked just fine.|||Improper setting at the time of server installation leads to situations like this, where there is a need to write extra code. However, it also appears that you ARE trying to auto-create databases (I already picture a scene where something in other parts of your code got missed and you are sitting at a non-responsive server because your code ended up creating databases), unless I am misreading the post. To alter the default location you need to use xp_instance_regwrite rather than for every database to be created to reference the same server, following a call to xp_instance_regread.

Function Return First Date of Week of Year

Hi
I've been using this function to return the date og the 'w of year' - but
until 2004-53 - > it calculates wrong.
W 53-2004 -> 2004/12/27 OK
W 01-2005 -> 2005/01/01 Wrong
W 02-2005 -> 2005/01/02 Wrong
W 03-2005 -> 2005/01/10 OK
btw: I have the Set DateFirst 1 ( Monday )
Can anyone Help?
And here the function
create FUNCTION WEEK_TO_DATE(@.w int, @.year int)
RETURNS datetime
AS
BEGIN
declare @.date datetime
set @.date = convert(datetime, '1/1/' + cast(@.year as char(4)) )
while datepart(wk, @.date) <> @.w
set @.date = dateadd(dd, 1, @.date)
RETURN @.date
END
Kind Regards
J. E. JensenTake a look at the ISOWEEK function under the CREATE FUNCTION topic in
Books Online.
David Portas
SQL Server MVP
--

Function is 10 times slower than SP

Hi all,

In order to return a table for a specific input parameter, I am using Function, but the performance is just awful! After I have tried same code as SP, the whole thing is running under 1 sec (like 0.5 sec), while the function is about 10 times slow (4-6 sec). I know in SQL 2000 function is slower than SP, but that cannot be as bad as 10 times slower.

Now, in order to use that table from SP, I have to create a temp table, then insert result into that temp table, before I can direct use any "select" sentence. Any explanation here? Or how to "select" from a SP directly?

Thanks,

Ning

It is very difficult for us to attempt to help you with being able to see the code.

You may wish to consider posting the procedure code and maybe someone here will help create an efficient TVF (table valued function.)

|||

As you said you have to insert the sp output to temp table to use in select statement ... you can not use sp in select statement... you are rightly mentioned that function used to be more or less slower than sp... also check whether all the parameters used in sp are used in function also...

Madhu

Function for return element list from a query

Hello,

I have do a sql function for return a list of element from a query send in variable.

When I test the function on self I have no problem.

But when I use the function in a sql query I have problem.

example :

Code Snippet

SELECT APPLI_SUPPLIER.N_SUPPLIER_ID, APPLI_SUPPLIER.V_SUPPLIER_LABEL,
dbo.APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')
AS V_PROCESS_ITEMS
FROM APPLI_SUPPLIER INNER JOIN
APPLI_SUPPLIER_SKILL ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID
WHERE (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

This is the error :

Server: Msg 557, Level 16, State 2, Procedure APPLI_RETURN_LIST_ITEM, Line 24
Only functions and extended stored procedures can be executed from within a function.

When I do an exec of the function I have this problem :

Code Snippet

exec APPLI_RETURN_LIST_ITEM('SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))')

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'SELECT DISTINCT APPLI_CONSTRUCTION.V_PROCESS_CODE FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL) AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6))'

This is the function

Code Snippet

CREATE FUNCTION [dbo].[APPLI_RETURN_LIST_ITEM]
(@.QUERY AS VARCHAR(3900)=null)
RETURNS varchar(8000)
AS
BEGIN
-- Insert statements for procedure here
declare @.v_List_ITEM as varchar(8000)
set @.v_List_ITEM=''

if @.QUERY is not null
Begin
declare @.cur_Lect_ITEM CURSOR;
declare @.FUNCTION AS NVARCHAR(4000);
Declare @.ITEM as VARCHAR(255);

SET @.FUNCTION = 'set @.mainCursor=cursor for ' + @.QUERY + ' for read only open @.mainCursor'

EXEC sp_executesql @.FUNCTION,N'@.mainCursor cursor output', @.cur_Lect_ITEM output

fetch next from @.cur_Lect_ITEM into @.ITEM
while @.@.fetch_status=0
begin
set @.v_List_ITEM=@.ITEM + ' ; ' + @.v_List_ITEM
fetch next from @.cur_Lect_ITEM into @.ITEM
end
deallocate @.cur_Lect_ITEM
SET @.v_List_ITEM=REPLACE(REPLACE(@.v_List_ITEM, CHAR(13), ''), CHAR(10), '')
set @.v_List_ITEM=left(@.v_List_ITEM,len(@.v_List_ITEM)-3)
End
RETURN @.v_List_ITEM
END

Can you help me please?

Thank you

You DO NOT execute a FUNCTION.

You use a FUNCTION inline (like an expression), or

you use a FUNCTION like a table.

Perhaps your 'function' should be a Stored Procedure...

|||

1. You can't use dynamic SQL in Functions

2. You needn't this much complex query to concatinate the items

If you use sql server 2005 the following query will work for you..

Code Snippet

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

(SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) + ';' as [text()]

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_CONSTRUCTION.N_SUPPLIER_ID )

for XML path('')) AS V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER APPLI_SUPPLIER_MAIN

INNER JOIN APPLI_SUPPLIER_SKILL APPLI_SUPPLIER_SKILL_MAIN

ON APPLI_SUPPLIER_MAIN.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL_MAIN.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL_MAIN.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

If you use sql server 2000,

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2,3,4,5,6)

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

SELECT

APPLI_SUPPLIER.N_SUPPLIER_ID,

APPLI_SUPPLIER.V_SUPPLIER_LABEL,

DBO.GET_PROCESS_ITEMS(APPLI_CONSTRUCTION.N_SUPPLIER_ID) V_PROCESS_ITEMS

FROM

APPLI_SUPPLIER

INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_SUPPLIER.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE

(APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (2, 3, 4, 5, 6))

|||

Hello Manivannan.D.Sekaran

thank you for your answer.

I use SQL SERVER 2000 SP4

The objective of the function it's to use any query for return a list of element

It's not possible to do a generic function?

|||

In SQL Server 2000, we can't able to achieve this. You have to create a separate function. Since you have to embedded this function in your query you have to use the UDF (for each requirement).

In SQL Server 2005, you can achieve this using .NET CLR integration..

|||

thank you for answer ............. I have not chance|||

Hello,
I would like send a list of ID in the function.
How I can do that?
Do you have a idea?
thank you

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN @.SKILL_ID

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

GO

|||

You can use the SPLIT UDF,

Code Snippet

CREATE FUNCTION SPLITINTOROWS

(

@.LIST AS VARCHAR(8000),

@.DELIMITER AS VARCHAR(10)

)

RETURNS @.LISTOFIDS TABLE (ITEM VARCHAR(8000))

AS

BEGIN

WHILE CHARINDEX(@.DELIMITER, @.LIST) <> 0

BEGIN

INSERT INTO @.LISTOFIDS

VALUES(SUBSTRING(@.LIST,1,CHARINDEX(@.DELIMITER,@.LIST)-1))

SET @.LIST = SUBSTRING(@.LIST, CHARINDEX(@.DELIMITER,@.LIST)+1, LEN(@.LIST))

END

INSERT INTO @.LISTOFIDS VALUES(@.LIST)

RETURN;

END

Code Snippet

CREATE FUNCTION GET_PROCESS_ITEMS(@.SUPPLIER_ID AS INT, @.SKILL_ID AS VARCHAR(1000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.RESULT VARCHAR(8000);

SET @.RESULT = '';

SELECT @.RESULT = @.RESULT + ';' + PROCESS_CODE

FROM

(

SELECT DISTINCT Cast(APPLI_CONSTRUCTION.V_PROCESS_CODE as varchar) PROCESS_CODE

FROM APPLI_CONSTRUCTION INNER JOIN APPLI_SUPPLIER_SKILL

ON APPLI_CONSTRUCTION.N_SUPPLIER_ID = APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID

WHERE (APPLI_CONSTRUCTION.V_PROCESS_CODE IS NOT NULL)

AND (APPLI_SUPPLIER_SKILL.N_SKILL_ID IN (select item from SplitIntoRows(@.SKILL_ID, ','))

AND APPLI_SUPPLIER_SKILL.N_SUPPLIER_ID = @.SUPPLIER_ID

)

) AS DATA;

RETURN @.RESULT;

END

|||

Thank you very much

You are my god

Function for counting distance

Hi,
I want to create function which will return column of maximal distance.
SELECT MaxDistance(X,Y) FROM Table;
Is it possible ?
(Something like MAX function but I will have two arguments in MaxDistance on
which will be calculated distance e.g.: x-y).
Thank you very much.Are you simply attempting to identify max of a difference operation?
If so then calling MAX(x-y) will provide this. The MAX() function
belongs to the Aggregate functions. An Aggregate UDF will not be
available until SQL Server 2005.|||Like this?
SELECT MAX(ABS(x-y)) FROM Table
David Portas
SQL Server MVP
--

Function Date

A have initial date 20/04/2005
and a final date 25/09/2005. id like a function that return how many days
there are for each month until the final date.
Ex: 04/2005 - 10 days
05/2005 - 31 days
_
_
09/2005 - 25 days.
Is that possible, or i have to create some function that do it' Id like
some already done!
ThanksIf you don't care about wends / holidays,
SELECT DATEDIFF(DAY, '20050420', '20050925')
If you need to incorporate/ignore wends and holidays, see
http://www.aspfaq.com/2519
(I also *strongly* suggest avoiding usage and assumptions based on ambiguous
date formats like dd/mm/yyyy.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Daniel Caetano" <dcaetano@.ig.com.br> wrote in message
news:eqFrlesPFHA.244@.TK2MSFTNGP12.phx.gbl...
> A have initial date 20/04/2005
> and a final date 25/09/2005. id like a function that return how many days
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' Id like
> some already done!
> Thanks
>|||Try This:
Create FUNCTION dbo.MonthDays (@.StartDT Datetime, @.EndDT DateTime)
RETURNS @.Dates Table (DT DateTime, DaysInMonth SmallInt)
AS
BEGIN
Declare @.DT DateTime, @.EDT DateTime
Select @.DT = Convert(VarChar(6), @.StartDT, 112) + '01',
@.EDT = DateAdd(month, 1, @.DT)
While @.DT < @.EndDT Begin
Insert @.Dates(DT, DaysInMonth)
Values (@.DT, DateDiff(day,
Case When @.StartDT > @.DT Then @.StartDT Else @.DT END,
Case When @.EDT < @.EndDT Then @.EDT Else @.EndDT END))
Set @.DT = @.EDT
Set @.EDT = DateAdd(month, 1, @.DT)
End
RETURN
END|||create procedure GetDayNumbers(
@.StartDate datetime,@.EndDate datetime
)
as
declare @.StartDiff smallint,@.EndDiff smallint,@.Diff int
declare @.Test table (StartDate datetime,EndDate datetime)
select
@.StartDiff =
datediff(d,@.StartDate,left(convert(varch
ar(6),dateadd(m,1,@.StartDate),112),6
)+'01')-1;
select
@.EndDiff =
datediff(d,left(convert(varchar,@.EndDate
,112),6)+'01',@.EndDate)+1;
select
@.Diff =
datediff(m,@.StartDate,@.EndDate)+1
set rowcount @.Diff
select i=identity(int,1,1)
into Months
from sysobjects,syscolumns
insert @.Test
select @.StartDate StartDate,@.EndDate EndDate
select
Month=month(dateadd(m,i-1,StartDate)),
Year=year(dateadd(m,i-1,StartDate)),
NumOfDays=
case i
when 1 then @.StartDiff
when datediff(m,StartDate,EndDate)+1 then @.EndDiff
else datediff(d,dateadd(m,i-1,StartDate),dateadd(m,i,StartDate))
end
from @.Test join Months
on (datediff(m,StartDate,EndDate)+1)>=Months.i
drop table Months;
set rowcount 0
Regards,
Marko Simic
"Daniel Caetano" wrote:

> A have initial date 20/04/2005
> and a final date 25/09/2005. i′d like a function that return how many day
s
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' I′d like
> some already done!
> Thanks
>
>

Function

Can a UDF return a table query as result? Please kindly provide sample T-SQL. Thanks.Yes. Check out books on line under UDF.|||In Northwind database I try to create and test the sample UDF shown in BOL as below:
CREATE FUNCTION LargeOrderShippers ( @.FreightParm money )
RETURNS @.OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @.OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @.FreightParm
RETURN

SQ analyser displays the following:
Server: Msg 170, Level 15, State 1, Procedure LargeOrderShippers, Line 18
Line 18: Incorrect syntax near 'RETURN'.

Please advise.
Thanks much.
|||Try this link for all the info you need about UDF (user defined functions), the person who runs the site is a UDF expert. Hope this helps.
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm|||check thislink|||

The syntax for it is :
create function <function name ( param 1 <datatype>,...)>
returns table
as
return
select .....
go

Hope this solves your query...
Cheers
Ajay G

|||Sorry, I missed the "END" at the end. After I add it back, the UDF is created and runs OK.
Thanks much.

Full-text Search Assistance

Hi there:

We've been using full-text catalogs and some SQL stored procedures to return a ranked set of search results for a while now in 2000. Now that we've moved to 2005, I wonder if anyone knows of any good resources for returning ranked search results within 2005?

Ahh, but that was but part one of my question.

We have a table that need several fields indexed and searched, for our purposes we'll just say they're two varchar(1024) fields, a date field, and a two smaller varchar(128) fields. Would it make better sense, and return better search results, if we created a new ntext field named "SearchResults" and then wrote all of the data from the fields above to that single field for each record? W're then searching against only one field, and each result can be weighted for proximity.

Perhaps I'm just looking for suggestions on the best search methodology to apply against a scenario like I outlined above, when we need to search several fields against the same search criteria.

Any assistance you can give me would be great.

Thnks!

Brad

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20

Hi,
read this

http://www.sqlteam.com/article/using-sql-server-2005-fulltext-search-from-aspnet-20

Full-Text Search and Output Parameters

Hi,

I'd like to incorporate search functionality (SQL Server 2005 Full-Text Search) into a web application, so I want to be able to return a paged list of results based on the user's search terms. I already have a parameterized stored procedure that returns a list of products when a category ID is supplied. I modified this procedure to use a different input parameter (@.SearchTerms), but I'd still like to return the number of records, as in the original stored procedure.

However, I'm getting this error: Invalid object name 'ProductEntries'.

Here's the original stored procedure:

ALTER PROCEDURE dbo.GetProductsByCategoryID
(
@.CategoryID INT,
@.PageIndex INT,
@.NumRows INT,
@.CategoryName VARCHAR(50) OUTPUT,
@.CategoryProductCount INT OUTPUT
)
AS

BEGIN
SELECT @.CategoryProductCount = (SELECT COUNT(ProductID)
FROM Products
WHERE Products.CategoryID = @.CategoryID)
SELECT @.CategoryName = (SELECT CategoryName
FROM Categories
WHERE Categories.CategoryID = @.CategoryID)

DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;

WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM Products
WHERE CategoryID = @.CategoryID
)

SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1

END

And here's the modified one:

ALTER PROCEDURE dbo.GetSearchResults
(
@.SearchTerms VARCHAR(200),
@.PageIndex INT,
@.NumRows INT,
@.ProductCount INT OUTPUT
)
AS

BEGIN
SELECT @.ProductCount = (SELECT COUNT(ProductID)
FROM ProductEntries)

DECLARE @.startRowIndex INT;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;

WITH ProductEntries AS (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID) AS Row, ProductID, CategoryID, Description, ProductImage, UnitCost
FROM CONTAINSTABLE (Products, *, @.SearchTerms, 25) AS c, Products p
WHERE c.[KEY] = p.ProductID
)

SELECT ProductID, CategoryID, Description, ProductImage, UnitCost
FROM ProductEntries
WHERE Row BETWEEN
@.startRowIndex AND @.startRowIndex + @.NumRows - 1

END

I thought I might be getting this error because SELECT @.ProductCount occurs before the ProductEntries table is created, but when I move that SELECT statement further down, I still get the same error.

How can I get the value of @.ProductCount in this scenario so that I can display it in the UI of the web app?The first to do would be to directly query the ProductEntries table from both a Stored Procedure and then as an individual statement. In 99% of cases, this solves the problem.

A possible and common reason for your error may have to do with ownership properties of the table and their association, or lack thereof, to the account that you are using to create the stored procedure.

I haven't used SQL Server for a few months now, as I've just recently started working on an Oracle project, and I cannot recall exactly the relationship between CREATE and EXECUTE on stored procedures and which of the above explicitly allows access to the underlying objects that will be used by the stored procedure.

Regards,|||Hi Robert,

Thanks for your suggestions. Unfortunately, I could not get the procedure to work. After further research, I decided not to rely on SQL Server Full-Text Search for my site's search engine because it's really not practical in a shared hosting environment. So, I think I'll put this one to rest and check out the MSN Search SDK (http://search.msn.com/developer).

Anyway, thanks again for your reply -- it was much appreciated.