Forms Authentication, CheckAccess

I'm implementing Forms authentication, but I have users stored in two different databases. The "LogonUser" method in my AuthenticationExtension gets an "authority". In that way I can authenticate the user in the correct database.

But... the problem is the CheckAccess method of my AuthorizationExtension. Is it in any way possible to reach the "authority" information from inside it? I need to grant some access to users of the first database and some other access to the users of the second database.

Could you unify the users list into one database using a view with a UNION ALL statement? This may solve your problem.

cheers,

Andrew

Formatting/Returning stored proc results as XML

I have a stored proc that contains a simple select statement. The select statement does not use the 'FOR XML' clause, nor can the select statement be modified to use the 'FOR XML' clause (for compatibility reasons)

Is there a way to execute the stored proc such that it returns its output in XML? I'm looking for something similar to this:

Code Snippet

exec spGetUserDetails @.ID = 1234 for xml raw

Thanks.

I found an approach that would preserve backwards compatibility while allowing me to manipulate results retrieved by a stored proc. It has to do with Table-valued user-defined functions:

Move the select statement from the stored proc into a user--defined function that returns TABLE:

Code Snippet

-- Example from Books Online

CREATE FUNCTION fn_SalesByStore (@.storeid int)
RETURNS TABLE
AS
RETURN ( SELECT * from Products where StoedID = @.storeid );

Modify the original stored proc to call this user-defined function, i.e., the stored proc wraps the UDF hence preserving backwards compatibility:

Code Snippet

-- Inside the stored proc comment the select statement and call the UDF

-- SELECT * from Products where StoedID = @.storeid -- No longer needed

select * from fn_SalesByStore (@.storeid);

Now I can call the UDF to retreive the same results that would have been retrieved by the original stroed proc, and more importantly, manipulate them as required.

Code Snippet

select * from fn_SalesByStore (@.storeid) for xml raw

Yazan

Formatting Issues

Hi all,
Im using the custom code to format the string returned by the
stored procedure in the reports.
Public Function ResourceCategory(ByVal stat As String) As String
Dim str As String
Dim arr As String()
arr = stat.Split(",")
Dim i As Integer
For i = 0 To arr.Length -1
If arr(i) = "'PART'" Then
str = str & "Partner/"
End If
If arr(i) = "'ADM'" Then
str = str & "Administration/"
End If
If arr(i) = "'DIR'" Then
str = str & "Director/"
End If
If arr(i) = "'ENG'" Then
str = str & "Engineer/"
End If
if arr(i) = "'PM'" Then
str = str & "Project Manager/"
End If
if arr(i) = "'SE'" Then
str = str & "Senior Engineer/"
End If
if arr(i) = "'VALGRE'" Then
str = str & "Validation Engineer Greenville/"
End If
if arr(i) = "'VALSEN'" Then
str = str & "Senior Validation Engineer SFO/"
End If
if arr(i) = "'VALSFO'" Then
str = str & "Validation Engineer/"
End If
if arr(i) = "All" Then
str = str & "All/"
End If
Next
str = str.Remove(str.LastIndexOf("/"), 1)
Return str
End Function
But if the condition fails the report prints "#Error"
Can I in any way avoid this'
hanks In Advance,Provide an Else condition for anything that does NOT satisfy the other
conditions like "NA"... But if the function fails, you can not override the
error...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> Hi all,
> Im using the custom code to format the string returned by the
> stored procedure in the reports.
> Public Function ResourceCategory(ByVal stat As String) As String
> Dim str As String
> Dim arr As String()
> arr = stat.Split(",")
> Dim i As Integer
> For i = 0 To arr.Length -1
> If arr(i) = "'PART'" Then
> str = str & "Partner/"
> End If
> If arr(i) = "'ADM'" Then
> str = str & "Administration/"
> End If
> If arr(i) = "'DIR'" Then
> str = str & "Director/"
> End If
> If arr(i) = "'ENG'" Then
> str = str & "Engineer/"
> End If
> if arr(i) = "'PM'" Then
> str = str & "Project Manager/"
> End If
> if arr(i) = "'SE'" Then
> str = str & "Senior Engineer/"
> End If
> if arr(i) = "'VALGRE'" Then
> str = str & "Validation Engineer Greenville/"
> End If
> if arr(i) = "'VALSEN'" Then
> str = str & "Senior Validation Engineer SFO/"
> End If
> if arr(i) = "'VALSFO'" Then
> str = str & "Validation Engineer/"
> End If
> if arr(i) = "All" Then
> str = str & "All/"
> End If
> Next
> str = str.Remove(str.LastIndexOf("/"), 1)
> Return str
> End Function
> But if the condition fails the report prints "#Error"
> Can I in any way avoid this'
> hanks In Advance,|||Thanks Wayne ,
this is wat im trying to accomplish.
Hope u will help me out in this'
Im displaying the parameters selected by the user in a textbox.
The selection mode is multiple.
Im passing the code selected to the stored procedure,so it displays the
code.
if i give,
Parameters!Category.Value
So i wrote the code which i posted previously,
IS there a way i can load the description from the database '
Can i connect to the Database from the Code to achieve this'
"Wayne Snyder" wrote:
> Provide an Else condition for anything that does NOT satisfy the other
> conditions like "NA"... But if the function fails, you can not override the
> error...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8871344E-22EB-45C9-BF69-6A2D68E38093@.microsoft.com...
> > Hi all,
> > Im using the custom code to format the string returned by the
> > stored procedure in the reports.
> >
> > Public Function ResourceCategory(ByVal stat As String) As String
> > Dim str As String
> > Dim arr As String()
> > arr = stat.Split(",")
> > Dim i As Integer
> > For i = 0 To arr.Length -1
> > If arr(i) = "'PART'" Then
> > str = str & "Partner/"
> > End If
> > If arr(i) = "'ADM'" Then
> > str = str & "Administration/"
> > End If
> > If arr(i) = "'DIR'" Then
> > str = str & "Director/"
> > End If
> > If arr(i) = "'ENG'" Then
> > str = str & "Engineer/"
> > End If
> > if arr(i) = "'PM'" Then
> > str = str & "Project Manager/"
> > End If
> > if arr(i) = "'SE'" Then
> > str = str & "Senior Engineer/"
> > End If
> > if arr(i) = "'VALGRE'" Then
> > str = str & "Validation Engineer Greenville/"
> > End If
> > if arr(i) = "'VALSEN'" Then
> > str = str & "Senior Validation Engineer SFO/"
> > End If
> > if arr(i) = "'VALSFO'" Then
> > str = str & "Validation Engineer/"
> > End If
> > if arr(i) = "All" Then
> > str = str & "All/"
> > End If
> > Next
> > str = str.Remove(str.LastIndexOf("/"), 1)
> > Return str
> > End Function
> >
> > But if the condition fails the report prints "#Error"
> > Can I in any way avoid this'
> > hanks In Advance,
>

Formatting a SSN in Reporting Services

I've got a report of peoples' SSN's and Names. The SSN is stored as a string
(nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
IOW, with hyphens.
In my report, the expression to display the data looks like:
=Fields!SSN.Value
When I try to format it like this...
=Format(Fields!SSN.Value,"###-##-####")
...I see only puond signs and hyphens, with no numbers.
So I convert the string to a decimal like this...
=Format(cdec(Fields!SSN.Value),"###-##-####")
...and it displays correctly in my report. But to my mind, this isn't an
elegant solution. Isn't there some way I could format the string, itself,
without having to convert it to a decimal?
TIA,
__BirmAn alternative is to use string manipulation functions. E.g.
=Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
Right(Fields!SSN.Value, 4)
More information about these functions is available on MSDN:
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
* http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||You could use the substring function in your query if it is a shared
dataset, that way you only have to do it once.
"Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> I've got a report of peoples' SSN's and Names. The SSN is stored as a
> string
> (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> IOW, with hyphens.
> In my report, the expression to display the data looks like:
> =Fields!SSN.Value
> When I try to format it like this...
> =Format(Fields!SSN.Value,"###-##-####")
> ...I see only puond signs and hyphens, with no numbers.
>
> So I convert the string to a decimal like this...
> =Format(cdec(Fields!SSN.Value),"###-##-####")
> ...and it displays correctly in my report. But to my mind, this isn't an
> elegant solution. Isn't there some way I could format the string, itself,
> without having to convert it to a decimal?
> TIA,
> __Birm
>|||Robert,
That worked like a champ. (That slapping sound you hear BTW, is my palm
hitting my forehead.) I tried a similar method, using Substring in stead of
the Mid() function, and -- here's my biggest mistake -- using a plus sign as
the concatenator instead of using the ampersand. To much C# in my immediate
past, I guess. <g>
Thanks a lot for the pointer. I really appreciate your taking the time.
__Birm
"Robert Bruckner [MSFT]" wrote:
> An alternative is to use string manipulation functions. E.g.
> =Left(Fields!SSN.Value, 3) & "-" & Mid(Fields!SSN.Value, 4, 2) & " - "
> Right(Fields!SSN.Value, 4)
> More information about these functions is available on MSDN:
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctLeft.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctMid.asp
> * http://msdn.microsoft.com/library/en-us/vblr7/html/vafctRight.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>|||MPF,
Yup...and that would be the elegant solution, wouldn't it? Hmmm...it's
not a shared dataset, but still...mebbe I'm going to do exactly that. Just
for yucks and education if nothing else.
Thanks!
__Birm
"MPF" wrote:
> You could use the substring function in your query if it is a shared
> dataset, that way you only have to do it once.
>
> "Birmbear" <Birmbear@.discussions.microsoft.com> wrote in message
> news:9A6DD1E3-ABDB-488B-8B56-055C8CFCC6F1@.microsoft.com...
> > I've got a report of peoples' SSN's and Names. The SSN is stored as a
> > string
> > (nvarchar(9)). I need to display the SSN in the common way: ###-##-####;
> > IOW, with hyphens.
> >
> > In my report, the expression to display the data looks like:
> >
> > =Fields!SSN.Value
> >
> > When I try to format it like this...
> >
> > =Format(Fields!SSN.Value,"###-##-####")
> >
> > ...I see only puond signs and hyphens, with no numbers.
> >
> >
> > So I convert the string to a decimal like this...
> >
> > =Format(cdec(Fields!SSN.Value),"###-##-####")
> >
> > ...and it displays correctly in my report. But to my mind, this isn't an
> > elegant solution. Isn't there some way I could format the string, itself,
> > without having to convert it to a decimal?
> >
> > TIA,
> >
> > __Birm
> >
>
>

Formatted Values in Stored Procedures

Hello,
I have to put some data from an SQL Server 2000 DB to a
Word document containing some numeric values (money).
I use a stored procedure like this
select field1 + ' ' + field2 + ' ' + field3 ...
from table1 where ...
So I get back one long string. How can I get all numeric
values in this string formatted? There is no format
function like in VB/A.
Second: I'd like to have a tab (Ascii 9) instead of ' ':
How can I get this?
Klaus
www.trappdata.deFormatted like what?
Take a look at this
DECLARE @.v MONEY
SELECT @.v = 1322323.6666
SELECT CONVERT(VARCHAR,@.v,0) --1322323.67 Rounded but not formatted
SELECT CONVERT(VARCHAR,@.v,1) --1,322,323.67 Formatted with commas
SELECT CONVERT(VARCHAR,@.v,2) --1322323.6666 No formatting
If you have a decimal field it doesn't work with the convert function.
The work around is to cast it to money
DECLARE @.v2 DECIMAL (36,10)
SELECT @.v2 = 13243543.56565656
SELECT CONVERT(VARCHAR,CONVERT(MONEY,@.v2),1) --13,243,543.57 Formatted
with commas
http://sqlservercode.blogspot.com/

formatted outputs

Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........
Regards
dba_sybaseYou can Use Enterprise manager to script them in formatted way .. unless you really not able to use Em .. or try using

exec sp_helptext 'sp_name'|||Thanks AAshu. I already knew that but it seems that the third pary application created the SP as a single line. So I couldnot find a way to formatt them other than formatting them by hand.

Thanks

Jaideep|||Try this trick for a select statement (not updates or deletes!):

Copy your line to the clipboard.
Open select a table (any table) in your database in Enterprise Manager, right click, and select show all rows (or show data, or whatever).
When the window opens up and displays the data, look for an icon near the top left that will display the SQL code applied (likely just a select from statement),
Replace the SQL Statement with what you have on the clipboard and requery by clicking the red "!" icon.
EM should reformat your code before displaying the results. If it doesn't, try adding a dummy table to the display and then removing it.

Sorry if this isn't step-by-step clear, but I'm going from memory.

Personally, I don't like the way EM formats statements, so I usually do some touch-up afterward, but its better than one long line of code.

blindman|||Thanks

formatted output

Hi,
Is there a way to get stored procedure texts out in a formatted way
like defncopy in Sybase??? The way they print it out really
sucks.........

Regards

SubhasSubhas (dba_sybase2003@.yahoo.com) writes:
> Is there a way to get stored procedure texts out in a formatted way
> like defncopy in Sybase??? The way they print it out really
> sucks.........

There is no command-line utility, but there are scripting facilities
both in Query Analyzer and Enterprise Manager.

In QA click F8 to get an Object Brower. Right-click a procedure and you
find the scripting option. In EM, right-click a database and pick
All Tasks and then Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

formatted file using stored procedure

how do i create a formatted file using a stored procedure?

for eg. if i have the following...

date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
Place: London : should occupy 20 spaces (text).
Country: United Kingdom should occupy 20 spaces (text)
EOL Terminator: (1 character only)
:
:
:

the sample final output should be like:

050403London United Kingdom
050403Washington DC USA
050403Delhi India

any quick help is appreciated,earlier post discarded the spaces between the sample records... it should reas as

050403London^^^^^^^^^^^^^^United Kingdom^^^^^^
:
:

where ^ denotes a blank space.

regards|||In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed

C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
(char(10),crdate,103) from sysobjects where type='S'" -h-1

-2147483645 sysobjects 13/11/1998
-2147483643 sysindexes 13/11/1998
-2147483645 syscolumns 13/11/1998
-2147483645 systypes 13/11/1998
-2147483647 syscomments 13/11/1998
-2147483648 sysfiles1 13/11/1998
-2147483647 syspermissions 13/11/1998

Substitue the SELECT with your stored procedure. Or even better use BCP.

formating dates

I have a date being input from a stored procedure. It come in as 11/22/2005.
I tried to format this date using:
=Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
00/22/05. What am I doing wrong?Hi Donna,
You need to capital M's for month as follows:
Format(Fields!DATE_RECEIVED.Value,"MM/dd/yy")
"DONNA" wrote:
> I have a date being input from a stored procedure. It come in as 11/22/2005.
> I tried to format this date using:
> =Format(Fields!DATE_RECEIVED.Value,"mm/dd/yy"). The date returned to me was:
> 00/22/05. What am I doing wrong?|||What I did to fix this, under the properties of the text box or field
display change the format code to "d".

Format Telepone Number

I am using a stored procedure to query data in a SQL backend. I have the telephone number stored as 9999999999. I am wondering how I can get that number formatted and displayed in a list box (Access 2000). I want standard formatting, (999) 999-9999. How can I do this? Thanks.create function dbo.fn_FormatUSPhone (
@.PhoneString varchar(10) ) returns char(14)
as begin
declare @.RetVal char(14), @.Area char(3), @.Prefix char(3), @.Nbr char(4)
if isnumeric(@.PhoneString) = 0
set @.PhoneString = replicate('0', 10)
if datalength(@.PhoneString) < 10
set @.PhoneString = replicate('0', 10-datalength(@.PhoneString))+@.PhoneString
set @.Area = cast(@.PhoneString as char(3))
set @.Prefix = substring(@.PhoneString, 4, 3)
set @.Nbr = reverse(cast(reverse(@.PhoneString) as char(4)))
set @.RetVal = '(' + @.Area + ') ' + @.Prefix + '-' + @.Nbr
return @.RetVal
end
go
select field1, field2, dbo.fn_FormatUSPhone(phone_number_field) from your_table|||If you are sure your table inserts are 10 digits all the time

select '('+left(phone,3)+') '+substring(phone,4,3)+'-'+right(phone,4) from PhoneBook

This will return exactly the same result as the last reply|||Thank you very much. That's got it.|||Okay, I thought that had it. I would like to store my telephone numbers as datatype bigint, but now the code no longer works. Apparently you cannot use the substring function on a bigint datatype. Is it possible to use a bigint datatype and still have the format I am looking for. Please help one more time.|||Do NOT store your phone number as bigint. Are you going to add phone numbers? Are you going to divide phone numbers? Are you going to average phone numbers?

Phone numbers are character data that happen to be numerals.

blindman|||yeah, bigint is kinda odd|||I assume then that Social Security Numbers should also be stored as varchar?|||you betchya, just set up check constraints with [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] and you'll be all set.|||Actually, there are more consideration to choose the one or the other datatype.

Most importantly, your data type is part of your data model, especially your integrity rules. If in your model your telephone number are ten digits, you may use bigint, char(10) or three smallints; none of them are fitting your requirement completely, but you will have to add some check constraints like the SQL_DBA showed for char(10) or < 10000000000 and >= 1000000000 for bigint.

When you ensured that your data is correct, there is also the aspect of usage. Than numeric operators are not applicable, isn't really an argument not to choose a numeric data type. However, the possibility of proper formatting may be an reason to choose the char(10) option. Another aspect is storage, in some case it may be important that bigint needs 2 bytes less than char(10). An index will also be smaller, and maybe the engine will also faster compare two numbers than two strings.

However, I was wondering whether you model is fine. Is the part (999) your net number? In this case, you model would be correcter if you could ensure that you only have valid net numbers.|||Use bigint, and you will eventually wish you hadn't.

You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

blindman|||Originally posted by blindman
Use bigint, and you will eventually wish you hadn't.

You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

blindman

As I tried to explain, this is just one aspect. Optimized for presentation, you would even split the telefone number into three alphanumeric portions. Sematically, however, I would propose to model a separate net number, don't you agree?|||I can see a reason for splitting off the net number (I think you are referring to "area code" in U.S.) into a separate field in some applications. To handle all possible international phone formats, I'm not sure if it could be broken apart in a consistent and reliable manner.

blindman|||The advantage of using varchar outweighs the space savings in using bigint? That was my only question. I know all telephone numbers will be stored as 9999999999, so I thought I should use bigint and enjoy the space savings. Regardless, noone has posted indicating I can format the bigint number as (999) 999-9999, so I am stuck with varchar either way. Is that correct?|||You can format it that way after you cast your bigint value as char.

'(' + Left(cast(YourValue as varchar(10)), 3) + ') ' + Mid(cast(YourValue as varchar(10), 4, 3) + '-' + right(cast(YourValue as varchar(10), 4)

It is not a matter of can, but a matter of should. Any performance boost or space-savings you get from using bigint will be offset by added processing and administration time.

blindman|||I just saw that you are working with an Access 2000 front-end. Why don't you format your number at the front-end, using the format function:

format(<Your BigInt Number>, "(###)####-###")|||Apparently because I am using a stored procedure to call the data, and then displaying it in a list box, I have to format it at the stored procedure level. I assume this to be correct.|||No, I don't think so. Let your stored proc return the numeric value with a recordset, and make sure that you fill your listbox within a procedure, looping your recordset and formatting your number during adding to the list.|||Before I waste any more of your time, I think I will keep the phone number stored as varchar. The posts I have seen seem opposed to using bigint as the datatype. The space savings would be nice, but I've already got the code working for varchar and knowing that a lot of people are opposed to bigint makes me think I should stick with varchar. Thanks for your help and everyone else's in the group. You all have been extremely helpful.

Format numeric to display dollar value $1,000.00

Hi,
Currently, I have a numeric field stored a value of 1000, how can I covert
it to varchar value and display it as $1,000.00
Thanks>> Currently, I have a numeric field stored a value of 1000, how can I cover
t it to varchar value and display it as $1,000.00 <<
Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just SQL
and RDBMS.
You are the kid in the class that the other students make fun of
because he just does not understand ...|||I need to store a string of combine text that should should appear ....
$1,000.00
I'm wondering, if there that's a way to do that.
"--CELKO--" wrote:

> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||The following example illustrates how this can be achieved:
CREATE TABLE dbo.currency
(
col1 INT
)
INSERT dbo.currency SELECT 1000
INSERT dbo.currency SELECT 10
INSERT dbo.currency SELECT 999999
SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
FROM dbo.currency
HTH
- Peter Ward
WARDY IT Solutions
"slimla" wrote:
> I need to store a string of combine text that should should appear ....
> $1,000.00
> I'm wondering, if there that's a way to do that.
>
> "--CELKO--" wrote:
>|||Extract from my blog:
http://sqlblogcasts.com/blogs/tonyr...1/429.aspx....
Introduction
Application programmers and Business Intelligent professionals are faced
with having to format data - taylored into what the users want. We have two
choices as to where we do this processing, keep it in the database using the
facilities of the database engine, for instance T-SQL, standard SQL dialect,
CLR, XML or whatever the product has to offer or we can bring the data out
of the database and down into the front end application or middle tier and
format the data there (keep the database for store and retreive only).
My Opinion
The IT industry is full of rules and best practices unfortunetly some of
these rules and best practices aren't based on current technology or
business problems, in fact some of the rules and best practices are based on
techniques adopted in the 70's and 80's on mainframes or early client server
architecture.
No product is just a database anymore, sure SQL Server stores and retrieves
data but it also offers us a lot more, in fact its moving more towards being
the middle and data tiers in the three tier architecture now that SQL Server
can be a web service and the inclusion of CLR.
Data formatting, be it paging, value concatenation should always been done
where it is most efficient to do it. Consider (and benchmark) where its most
efficient to do this, would you really drag 1 million rows into the middle
tier or client browser only to get page 2 of 20 rows? It doesn't make sense.
Relating this to a well known expert, --CELKO--, he states that you should
NEVER do formatting in the database and it should always be done in the
front end. Think this through, take value concatenation for instance, say
you need to create a list of values for a given product category, for
instance for a given person show the mailing lists they belong to. In the
database this will be held in rows, so if a person belongs to 5 mailing
lists there will be 5 rows, now, say the user requires the values to be
normalised so they are displayed on just one line entry. We have two
choices, drag the 5 rows down to the front end or middle tier and use a 4GL
to process the data or we can use some of the extensions available in SQL
Server to do this.
Example
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select ', ' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 3, 100 )
from mailing_list m1
Gives this result :-
alex r List A
joe r List A, List B
tony r List A, List B, List C
Now, just how easy was that! It only takes a few lines of SQL and you have
also saved a lot of network traffic back out to the middle tier or front
end.
So, my point is this: whatever you do - always think through what you are
doing, don't just follow 'rules' blindly!
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150411335.797938.9320@.f6g2000cwb.googlegroups.com...
> Why are you formatting data in the back end? The basic principle of a
> tiered architecture is that display is done in the front end and never
> in the back end. This a more basic programming principle than just SQL
> and RDBMS.
> You are the kid in the class that the other students make fun of
> because he just does not understand ...
>|||slimla wrote:
> Hi,
> Currently, I have a numeric field stored a value of 1000, how can I covert
> it to varchar value and display it as $1,000.00
> Thanks
you can also set up a compute column to have string instead of integer.
select '$' + cast(<int value> as varchar(20))|||Tony,
Very well said!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam|||Thanks All
"P. Ward" wrote:
> The following example illustrates how this can be achieved:
> CREATE TABLE dbo.currency
> (
> col1 INT
> )
> INSERT dbo.currency SELECT 1000
> INSERT dbo.currency SELECT 10
> INSERT dbo.currency SELECT 999999
> SELECT '$' + CONVERT(VARCHAR(20), CAST(col1 AS MONEY), 1)
> FROM dbo.currency
> HTH
> - Peter Ward
> WARDY IT Solutions
>
> "slimla" wrote:
>|||I like that method, sort of gives you a formatting tier within the data
tier - nice and central and it doesn't effect storage, gives you a standard
view for people to use and code against... A lot more easier and
maintainable than using views as well...
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1150461726.914638.202330@.i40g2000cwc.googlegroups.com...
> slimla wrote:
>
> you can also set up a compute column to have string instead of integer.
> select '$' + cast(<int value> as varchar(20))
>

Format number in stored procedure

I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.
com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>

Format number in stored procedure

I have one question about stored procedure. I will write expression
which I used and result what I want:
Cast(InvNumber as varchar)+'/'+cast(year(InvDate) as varchar) results
as 75/2006. I want it to result as 00075/2006. How can I do it?Here is an example, but this should be done at the front-end
declare @.InvNumber int
select @.InvNumber = 75
SELECT @.InvNumber, RIGHT('0000000000' + Cast(@.InvNumber as
varchar)+'/'+cast(year(getdate()) as varchar),10)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for your response,
I can not use code which you propose because I have many InvNumber not
only one. This number 75 was only sample number.
Regards,|||Sure you can, that was just an example
SELECT RIGHT('0000000000' + Cast(InvNumber as
varchar)+'/'+cast(year(InvDate) as varchar),10)
FROM ...
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I got the error message: Subquery returned more than 1 value. It is not
allowed etc.|||Can you show use the query you executed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<lemes_m@.yahoo.com> wrote in message news:1147965460.658957.270150@.y43g2000cwc.googlegroups.com...
>I got the error message: Subquery returned more than 1 value. It is not
> allowed etc.
>

Format Decimal number conditionally

My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
place.
(ie: 2.0 hours, 3.5 hours)
I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
the ".0".
So far I have this solution:
=IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
Abs(Fields!Hours.Value) )
Is there a better solution? thanks!I found an even simpler solution:
I changed the Custom Format of my table cell to:
=Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> place.
> (ie: 2.0 hours, 3.5 hours)
> I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> the ".0".
> So far I have this solution:
> =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> Abs(Fields!Hours.Value) )
> Is there a better solution? thanks!
>|||Apology.
Do not use the Custom Format.
Change the Text Value Property to, ie: =Format(Cdbl(Fields!Hours.Value))
"SQT" wrote:
> I found an even simpler solution:
> I changed the Custom Format of my table cell to:
> =Format(Cdbl(Fields!Hours.Value))
>
> "SQT" wrote:
> >
> > My 'Hour' Fields are retrieved from stored procedure always with 1 decimal
> > place.
> > (ie: 2.0 hours, 3.5 hours)
> >
> > I wish to display, (ie: 2, 3.5 respectively), the first value (2.0) without
> > the ".0".
> > So far I have this solution:
> > =IIF(Abs(Fields!Hours.Value) < Fields!Hours.Value, Fields!Hours.Value ,
> > Abs(Fields!Hours.Value) )
> >
> > Is there a better solution? thanks!
> >

Format date in a Stored Procedure

Dear friends,

I have a stored procedure that returns some fiels. One of the fields is a datetime type.

The field return in the follow format : 2006-11-13 0:00:00

How can I return only 2006-11-13? How can I use the format function?

regards!!!

declare @.someDate datetime
set @.someDate = getdate()
select cast(datepart(yyyy, @.someDate) as varchar) + '-' + cast(datepart(mm, @.someDate) as varchar) + '-' + cast(datepart(dd, @.someDate) as varchar)

result: 2006-11-13

|||

Or...

Select Convert(varchar(10), GetDate(), 120)

Lookup the convert function in Books On Line for more formats.

|||

Here is another method:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

This keeps the datatype as a datetime while removing the date from the string. It is best practice to format the data in the UI.

|||

Dear friens,

First, let me thank for all your support.

And the last question about this problem, How can get the system current time in format hh:mm? (ex: 12:30)

Thanks

|||You should not care about formatting the date on the server this is a thing for the presentation layer.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||ok, but how I return the time value of the system?|||Do you mean at the presentation layer ? That depends on your used coding language, with .NET you will date various options on the Date type.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

Understood?

I want to save in my database th system time...

Thanks!!

|||Depending on which datatype you use in the column you can′t separate the date and the time. Datetime is a combined type storing date as well as time. Is it against any rules storing the date additionally ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I save the time field in teh database is a nchar(5) as for example: 12:30, 22:30, 08h00

Thanks!!

Format Date

I have a stored procedure that selects several fields from a table,
including a date field. I'd like to format the date field in the stored
proc so that the result reads mmddyy or mmddyyyy - without any slashes
or dashes, and without the time (06/26/2006 06:53:06.373 would read
06262006). Any ideas?4 digit year
Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
2 digit year
Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
Tom
<birdbyte@.gmail.com> wrote in message
news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...
>I have a stored procedure that selects several fields from a table,
> including a date field. I'd like to format the date field in the stored
> proc so that the result reads mmddyy or mmddyyyy - without any slashes
> or dashes, and without the time (06/26/2006 06:53:06.373 would read
> 06262006). Any ideas?
>|||How handy! Worked like a charm! Thanks!
Tom Cooper wrote:
> 4 digit year
> Select Replace(Convert(varchar(10), GetDate(), 101), '/', '')
> 2 digit year
> Select Replace(Convert(varchar(10), GetDate(), 1), '/', '')
> Tom
> <birdbyte@.gmail.com> wrote in message
> news:1151335825.434174.81350@.i40g2000cwc.googlegroups.com...