Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

return a TABLE

hello all

I need a function with a return value TABLE...

this is not the problem..

but i need the returned table dynamic..

example:

i call the function getTable(schema_name, TableName)

the 1st value is the schema. the 2nd is the table i need...

now i must have a return value like this

select * from schema_name.TableName

is this possible? can i build by return value dynamically?

i tried a lot but nothing worked..

thx

greg

Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:

EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresql

Retrieving the First N Records from a SQL Query in VS 2005

Hi,

first off, I'm a TOTAL novice at this stuff, I'm just currently blundering my way through a complex site to learn stuff.

I'm trying to call the newest addition to a SQL database into a webpage, in this case, it'll be 'newest user', one result only. I've done several other data retrival sections using a datatable, but the guy who was helping me though it is unavailable at the moment and I get the feeling I've jumped into the deepend slightly.

Could anyone give me an example of how retrieving the First N Records from SQL should look in VS? Does it need to be in a data table or can it go in a label?

Sorry if this is somewhat vague, but as I said, I've really only been using VS for a week!

SELECT TOP 1 FROM [TableName] ORDER BY timestamp

Hope this helps

Al

|||

Hi,

Using the Top keyword is the solution. But the use of TOP has changed between SQL2K and SQL2K5.

In SQL2k you were not able to use TOP @.N which means you can not dynamically set the record count for "TOP"

Check the article for TOP @.N usage athttp://kodyaz.com/articles/article.aspx?articleid=2

Also have a look at the new t-sql functions named "Window Functions". Also you can find some samples athttp://kodyaz.com/articles/article.aspx?articleid=19

Eralper

http://www.eralper.com

|||

Cheers Al,

trouble is, I'm unsure as to where it needs to go! As I said, I'm pretty rubbish at this VS lark so far.

Should I be using a datatable to display the information, or because its only a single result I want pulling back, can it be done in a label?

Currently, I have this working to bring back a compleate list of users, but on another page, I need to bring back only the newest. Once again, sorry if I appear dense, learning curve and all that;

CODE:

Dim SiteuserAsNew mySite.Siteuser

dlDataList.DataSource = Siteuser.List

dlDataList.DataBind()

HTML:

<asp:DataListID="dlDataList"runat="server">

<ItemTemplate>

<asp:LabelID="HyperLink1"runat="server"NavigateUrl='<%#Eval("SiteuserID", "viewuser.aspx?SiteuserID={0}" ) %>'Text='<%#Eval("title") %>'Font-Italic="true"></asp:Label><asp:HyperLinkID="hlkUser"runat="server"NavigateUrl='<%#Eval("SiteuserID", "viewuser.aspx?SiteuserID={0}" ) %>'Text='<%#Eval("fullname") %>'></asp:HyperLink></tr>

</ItemTemplate>

</asp:DataList>

sql

Retrieving Output paramater after insert

Can some one offer me some assistance?

I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#):

SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;

SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue =string.Empty;

SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue ="SYSTEM";

SqlDataSource1.InsertParameters["usr_auth_id"].Direction =ParameterDirection.ReturnValue;

SqlDataSource1.Insert();

int id =int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue);

below is the error I'm getting:

System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@.usr_auth_id', which was not supplied.

Has anyone done this before and if so how did you do it?

How did you define the stored procedure 'csi_USR_AUTH' ? Since you add the Direction of usr_auth_id toParameterDirection.ReturnValue, you don't need to declare a @.usr_auth_id parameter in the sp; instead, it will be filled by using a RETURN command. Check this article to see how to use ReturnValue parameter:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

Retrieving data types is slow

Hello,

i'm using SMO to retrieve information from various databases. It works
well except for one thing. When I call the Column.DataType property to
get the SQL type of a column it takes a very long tine. I have a few
databases, each with some tables. There are about 75 columns I think.
If I just browse all columns with SMO and write each name if it not a
system one, it takes about 15 seconds (pretty much anyway). If I also retrieve the data type
it takes about one minute. Any ideas why? The server is a local one.

Thanksstarted reading Michiel's articles. The issue will be fixed shortly I think. Thanks anyway.

retrieve result set generated by a stored procedure

I want to call other stored procedures in my stored procedure.
However, when I tried to call a stored procedure which return a result set rather than a single record, I don't know how to catch it in my stored procedures?

Are there some methods to catch up a result set returned by a stored procedure in another stored procedure?

Thanks!

Thomasinsert the resultset of the stored procedure into a table

create table
#tbl(spid int
, ecid int
, status varchar(15)
, loginame varchar(20)
, hostname varchar(15)
, blk int
, dbname varchar(15)
, cmd varchar(25))

insert into #tbl exec sp_who

select * From #tbl