function returning a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

function returning a table

Author

Topic

Fu
Starting Member

23 Posts

Posted - 2005-11-16 : 15:23:22

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar)

RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))
AS

BEGIN

IF @art <>'/'
INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as
'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label,
dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and
track.trackid=cdtrack.trackid and label.labelid=cd.labelid and
shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid
and artist.artist=@art
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate,
dbo.Label.Label, dbo.Shelf.Shelf

if @cd <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @tra <> '/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @gen <>'/'
insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @cdate<>'01/01/1900'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf

if @label<>'/'
insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
FROM artist,cd,label,shelf,cdtrack,artisttrack,track
where artist.artistid=artisttrack.artistid
and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid
and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label
Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
return
end
---------------------------------------------------------------------
upon running executing this function with valid values i am not getting any results.
anything is wrong?
thank you,

Kristen
Test

22859 Posts

Posted - 2005-11-16 : 16:00:58

"anything is wrong?"

None of your "insert into @result SELECT ... " found any rows to process?

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-11-16 : 16:07:07

yes none and that's weird because i have already made sure that the parameter value let's say for cdtitle is valid.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-16 : 17:36:53

I think you need to look at how you declared your VARCHAR input parameters. I doubt that you are getting what you expected. See what happend with the code below:

create FUNCTION [dbo].[search]
	(
	@art varchar,
	@cd varchar,
	@tra varchar,
	@gen varchar,
	@label varchar
)
returns table 
as
return
(
select
	art = @art,
	cd = @cd ,
	tra = @tra ,
	gen = @gen ,
	label = @label 
)

go

select
	*
from
	 [dbo].[search]('12345','23456','34567','45678','56789')



art  cd   tra  gen  label 
---- ---- ---- ---- ----- 
1    2    3    4    5

(1 row(s) affected)

CODO ERGO SUM

Fu
Starting Member

23 Posts

Posted - 2005-11-18 : 03:49:37

I have modified the varchar variables apropriately but still getting the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
still am getting this error

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-18 : 07:59:38

Why didn't you mention that you are getting this error before now?

quote:
Originally posted by Fu

I have modified the varchar variables apropriately but still getting the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
still am getting this error

CODO ERGO SUM

Fu
Starting Member

23 Posts

Posted - 2005-11-18 : 08:10:48

i wasnt getting it until i modified the varchar variables. but what does it have to do with varchar variables?

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 09:38:51

Parameter 5 in your code which calls the function is not a valid date, or is not in an appropriate format.

Use a variable of type date / datetime (such as NOW() in VB) or present a string in one of these formats only: 'yyyyddmm', 'yyyyddmm hh:mm:ss.mmm' or 'yyyy-dd-mmThh:mm:ss.mmm'

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-11-18 : 10:15:12

select *from dbo.search('/','/','/','new age','1900-01-01','/')
----------------------------------------------------------------
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
----------------------------------------------------------------
is that what you mean?
in vb.net i am having an issue. date should be inserted via a TextBox so in case it was empty the oledbparameter should be passed to sql in order be correctly processed.
-----------------------------------------
If ReleaseDate.Text = "" Then
op5.Value = "1900-01-01"
Else : op5.Value = result
End If

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 11:41:43

"is that what you mean?"

Yes. Your string date '1900-01-01' does not confirm to one of the formats I listed above

EDIT: Actually, in the case of Day=01 and Month=01 the date can be interpreted any way round, so its probably not an issue, but you should still avoid this format.

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-11-18 : 11:48:22

and the outlet is?
there wont be any problem using yyyyddmm
i will try :D
btw thank you
sory i just tried it and it didnt work

select * from dbo.search('/','/','/','new age','20051111','/')

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 11:52:25

RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype
varchar(100),CDDate datetime, Label varchar(100))

...

INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf

Well, your columns don't match up on this INSERT, I haven't checked the others

Kristen

Fu
Starting Member

23 Posts

Posted - 2005-11-18 : 11:55:59

this means i should remove the convert [date formaat] no?

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 12:00:26

No, it means you should get your INSERT columns in the same order as the Table's definition !!!!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources