select query next line - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select query next line

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-19 : 13:21:40

I have the following userdefined function:
when the result is coming, i see all in one line instead of next line. i guess char 13 should give the next line.

if i get 5 pieces of info, all 5 pieces of info appearing in 1 single row. Is it possible to show in each line.

ALTER FUNCTION [dbo].[getNewRecipientsInfo_TOCCDH](@ModuleID integer, @ModuleName VARCHAR(10), @Locale VARCHAR(20), @DistType VARCHAR(5))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @s VARCHAR(8000)
	DECLARE @iDateFormat varchar(8)


	SET @s=''
	SELECT @s=@s + r.[Name] + ' - '
	 + case when @Locale = 'English'
	 then CONVERT(varchar(10),r.sentdate, 101) 
	else CONVERT(varchar(10),r.sentdate, 103)
	 end + ' - ' + rp.filename + char(13)
	from TAB_Recipients r 
        join TAB_DocRepository rp
        on r.AttachedDocid = rp.docid
        where r.ModuleRecordID = @ModuleID and r.ModuleName = @ModuleName and r.RecipientType = @DistType 
	If @s >''
	   BEGIN
		SELECT @s = left(@s, len(@s)-1)
	   END 
	ELSE
	   BEGIN
		SELECT @s = ''
	   END   
	Return @s
END

Thank you very much for the helpful info.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-19 : 13:39:53

yeah if that's what you wanted, you can append chr(13) at the end of each select.

Or you can make this function a table function and return a table instead.


create FUNCTION [getNewRecipientsInfo_TOCCDH]
(@ModuleID integer, @ModuleName VARCHAR(10), @Locale VARCHAR(20), @DistType VARCHAR(5))
RETURNS @tab1 table(name varchar(500))
AS
BEGIN

	DECLARE @iDateFormat varchar(8)

	INSERT INTO @tab1 select r.[Name] + ' - '
	 + case when @Locale = 'English'
	 then CONVERT(varchar(10),r.sentdate, 101) 
	else CONVERT(varchar(10),r.sentdate, 103)
	 end + ' - ' + rp.filename + char(13)
	from TAB_Recipients r 
        join TAB_DocRepository rp
        on r.AttachedDocid = rp.docid
        where r.ModuleRecordID = @ModuleID and r.ModuleName = @ModuleName and r.RecipientType = @DistType 

	Return
END

[/code]

Thank you very much for the helpful info.
[/quote]

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-19 : 13:53:20

Is there anything wrong with my Userdefinedfunction? There is char(13) but still i see all info in 1 single line.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 01:02:30

are you viewing results in grid or in text? try results in text option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-20 : 14:31:55

Viaskh, yes i am using textoption. but for some reason when i am trying to execute this function getting this error:

Msg 2010, Level 16, State 1, Procedure getNewRecipientsInfo_TOCCDH, Line 19
Cannot perform alter on 'dbo.getNewRecipientsInfo_TOCCDH' because it is an incompatible object type.

I already have a user defined function, i am trying to use alter.

I don't know whats wrong with teh function.

Thanks very much for the helpful info.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-20 : 15:05:46

If you want to make your scalar function now as a table valued function then you can't use ALTER.
Use drop and create.

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-20 : 15:59:46

How to access table valued functions via select query?

i am doing this and geting the below error:
select dbo.getNewRecipientsInfo_TOCCDH(248,'PM','English','LH')

Msg 4121, Level 16, State 1, Line 1
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.getNewRecipientsInfo_TOCCDH", or the name is ambiguous.

Thanks for the helpful information.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-20 : 16:02:14

select * from dbo.getNewRecipientsInfo_TOCCDH(248,'PM','English','LH')

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-20 : 20:19:14

Hanbingl, Can you please tell me i used the table value function but getting error, since i am using it via an sp, it is throwing error saying multiple rows came, when expected a one row.

YOu also suggested i can use mine by puttin chr(13) at end of each select, where to put that thing in order to use mine: here is my UDF:


ALTER FUNCTION [dbo].[getNewRecipientsInfo_TOCCDH](@ModuleID integer, @ModuleName VARCHAR(10), @Locale VARCHAR(20), @DistType VARCHAR(5))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @s VARCHAR(8000)
	DECLARE @iDateFormat varchar(8)


	SET @s=''
	SELECT @s=@s + r.[Name] + ' - '
	 + case when @Locale = 'English'
	 then CONVERT(varchar(10),r.sentdate, 101) 
	else CONVERT(varchar(10),r.sentdate, 103)
	 end + ' - ' + rp.filename + char(13)
	from TAB_Recipients r 
        join TAB_DocRepository rp
        on r.AttachedDocid = rp.docid
        where r.ModuleRecordID = @ModuleID and r.ModuleName = @ModuleName and r.RecipientType = @DistType 
	If @s >''
	   BEGIN
		SELECT @s = left(@s, len(@s)-1)
	   END 
	ELSE
	   BEGIN
		SELECT @s = ''
	   END   
	Return @s
END

Thank you very much for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 01:40:20

can you show statement where you call this function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-21 : 06:12:39

Visakh,
I put another char(13) and now now it is showing with a new line.

end + ' - ' + rp.filename + char(13) + char(13)

Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 08:29:30

Maybe what you really need is carriage return (CR) linefeed (LF).
char(13)+char(10)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources