Output 10 rows

Hi! I am a newbie, need to make a report with only 10 rows, and not the whole result. How do I actually specify the output as 10 rows?
Thank you so much in advance!

Depends on your exact circumstances.

If you want show 10 per page then you need to

put a list control in the body|||Thank you som much!

SELECT TOP 10 SUM(cd.price) as Sales, ... gave me a list of the most sold CD's + sales figures. How if I want a list including numbers (1 Elton John, 2 Madonna 3, 4, 5, 6, 7,8,9,10etc)
How do I make a list and a sequence of numbers on a report?

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?
|||

curiousss wrote:

Moreover I wonder how to define sales per age-groups? I have a file "age" of customers, need to create groups(<16, 16-20, 21-30 etc) and select sales per group. How do I do this?

You can use a nested IIF within the group criteria to do this.

=IIF(Age < 16, "<16",IIF(Age <21,"16-20),IIF(Age<31,"21-30",">30")))

Note matching the parens can be a pain, but I am sure you will figure it out.

|||

Further to this, to create a sequence of numbers just use the RowNumber function.

=CStr(RowNumber(Nothing)) + " " + Fields!Album.Value

Output !

I have a colunms which contain all the numeric data I want to change the
format in the runtime like
145263 that should be like 145,263.
Is there any way to do through sql query?
Thanks
Formatting is typically better off done on the client side.
Otherwise, you need to convert or cast the value to money to
get what you are after. Something like:
DECLARE @.YourValue integer
SET @.YourValue = 145263
SELECT reverse(substring(reverse(convert(varchar, cast
(@.YourValue as money), 1)),
4, 30))
-Sue
On Mon, 23 Jan 2006 14:00:54 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I have a colunms which contain all the numeric data I want to change the
>format in the runtime like
>145263 that should be like 145,263.
>Is there any way to do through sql query?
>Thanks
>
|||Thanks dear !
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nagat119buf4sdbmbfnq3o035ag2am0g19@.4ax.com...
> Formatting is typically better off done on the client side.
> Otherwise, you need to convert or cast the value to money to
> get what you are after. Something like:
> DECLARE @.YourValue integer
> SET @.YourValue = 145263
> SELECT reverse(substring(reverse(convert(varchar, cast
> (@.YourValue as money), 1)),
> 4, 30))
> -Sue
> On Mon, 23 Jan 2006 14:00:54 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>
sql

OUTPUT - help with please

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @.Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @.SQL @.Params

@.SQL is the Proc Name (varchar) and @.Params is the parameter string (nvarchar).

If I include the @.Fatal_Error variable in the dynamic creation of the @.Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @.Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @.SQL @.Param_List = @.Fatal_Error

AND I also tried:

EXEC @.SQL @.Param_List + ' '+@.Fatal_Error+' '

You have to use the sp_executesql for parameterized dynamic sql,

Code Snippet

Declare @.idParm as int;

Declare @.nameParm as varchar(100);

Declare @.dynamicSql as nvarchar(1000);

Declare @.dynamicParamDef as nvarchar(1000);

Set @.idParm = 2;

Set @.nameParm = 'sysobjects'

Set @.dynamicSql = N'Select * from sysobjects where id=@.id or name=@.name'

Set @.dynamicParamDef = N'@.id as int, @.name as varchar(100)'

Exec sp_executesql @.dynamicSql, @.dynamicParamDef, @.idParm, @.nameParm

|||

I quote from BOL(look for sp_executesql, building statement at runtime)

"

Transact-SQL supports the following methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string."|||...and Manivannan prove it|||

Thank you all for your assistance. I will give it a whirl.

I was able to finally execute with EXEC @.SQL @.Params, @.Fatal_Error = @.Fatal_Error

However, I'm sure this will come back to bite me in the long run..

Thank you ALL for your quick replies!

|||

@.SQL is my stored procedure name

@.Param_List is the list of enumerated parameters

SET @.SQL = @.SQL + IsNull(@.Param_List,'');

EXECUTE sp_executesql @.SQL;

Worked like a charm.

Thanks!

Sandy

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/d...
25v.asp
or
http://msdn.microsoft.com/library/d... />
ql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:

> How to get a field from a specific column as output parameter?
> Hrcko
>
>

output

How to get a field from a specific column as output parameter?
HrckoHi
You don't saying how you are accessing this, so assuming ADO check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp
or
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01b1.asp
You may also want to check out the ADO sample that come with SQL Server.
John
"Hrvoje Voda" wrote:
> How to get a field from a specific column as output parameter?
> Hrcko
>
>

out-of-range smalldatatime value

I am trying to insert a rown into a table with this line in my program :

INSERT INTO MYTABLE (Usuario, Datahora, COD_PROG, Tabela, TipoMov, Registro, Campo, ValAnt, ValAtu, Motivo, Sequencial) VALUES (23, '06/28/2007 19:53:45', '002101', 'Contribuinte', 'A', '1006626', ' ', 'Isentou Tx 2a Via 062007', ' ', ' ', ' ')

and I am receiving this message :

The conversion of char data type to smalldatatime data type resulted in an out-of-range smalldatatime value.

Thanks

Hello

I suppose that the smalldatetime has the value 06/28/2007 19:53:45

what is the language used for the server ?

the format of the date is corresponding to month/day/year ( american format ).Maybe it's the origin of the problem.

For the french format ( dd/mm/yyyy ), you have an error ( 28 does not correspond to a month )

Have a good day

|||

The smalldatetime datatype does NOT include seconds. But it truncates the seconds. -That isn't the problem.

I suspect that your server is expecting a date in the form of 'dd/mm/yyyy', and you are providing 'mm/dd/yyyy'.

If you changed the date format on the INSERT data to the ISO standard of 'yyyy/mm/dd', you wouldn't have any problem.

|||Thank you, that's ok now.

out-of-range datetime value

Hi,

I have a page working with not problem since 3 years, and now that we've moved our database from a SQL Server 2000 to SQL Server 7 and i receive this error everywhere a date is used.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The error append on this line "While myReader.Read()" .. what can i do to solve this problem ?

Thank you

This may be caused by date format that differs from the SQL2000 instance to SQL7.0 instance. Try to change the DATEFORMAT option like this (can be set to mdy,ydm,and ymd) and test again:

SET DATEFORMAT mdy
GO

You can take a look at this link about converting to datetime type:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_27c5.asp?frame=true

sql