Database

Re: How Do i use Distinct keyword to extract the whole table

29-Apr-07 21:42

SELECT DISTINCT Column1, Column2, etc FROM MyTable

Upcoming events:
* Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Vimalsoft(Pty) Ltd29-Apr-07 21:47

Vimalsoft(Pty) Ltd

29-Apr-07 21:47

Thanks For you reply

cant i say something like * that will slect all the table distinctively?

or i have to SELECT DISTINCT Column1, Column2, etc FROM MyTable

and write down all the columns i have in my table, in my case there are more than 15 columns

?

Vuyiswa

Re: How Do i use Distinct keyword to extract the whole table

Colin Angus Mackay29-Apr-07 23:53

Re: How Do i use Distinct keyword to extract the whole table

29-Apr-07 23:53

Vuyiswamb wrote:
cant i say something like * that will slect all the table distinctively?

SELECT * is inefficient and the result set will change if you change your database. You are better to specify the columns that you want.

Vuyiswamb wrote:
and write down all the columns i have in my table, in my case there are more than 15 columns

Just 15... Then you don't have much to do. A project I worked on a couple of years ago produced result sets with over 200 columns, all individually named.

Vimalsoft(Pty) Ltd30-Apr-07 0:41

Vimalsoft(Pty) Ltd

30-Apr-07 0:41

Thank you Very much it Helped

Vuyiswa

Re: How Do i use Distinct keyword to extract the whole table

Brady Kelly30-Apr-07 0:17

Brady Kelly

30-Apr-07 0:17

A good opportunity to find a way of getting a list of column names for that table from the DB.

SqlHelper Class in Enterprise Library Release April 2007

ctrlnick29-Apr-07 12:55

ctrlnick

29-Apr-07 12:55

Hi Folks,

I am unable to locate the SqlHelper Class in the Enterprise Library Release April 2007. Can Somebody tell me where exactly I can find the source code , I actually wanted to understand the source code more in order to use it efficiently.

Thanks,

Happy Programming!

Regards,
Nikhil Pagidala

Re: SqlHelper Class in Enterprise Library Release April 2007

Colin Angus Mackay29-Apr-07 14:16

Re: SqlHelper Class in Enterprise Library Release April 2007

29-Apr-07 14:16

I've not looked at this version of the Enterprise Library, but didn't they take that out in version 2?

ctrlnick29-Apr-07 15:46

ctrlnick

29-Apr-07 15:46

I am not sure about the Jan 2007 release coz so far I was using the June 2005 release.
Can you please let me know if you get any info.

Thank You!

Happy Programming!

Regards,
Nikhil Pagidala

Re: SqlHelper Class in Enterprise Library Release April 2007

Colin Angus Mackay29-Apr-07 21:36

29-Apr-07 21:36

Nikhil Pagidala wrote:
Can you please let me know if you get any info.

Why don't you just read up on how the Enterprise Library now works rather than how it used to work? There should be plenty of documentation and tutorials on the Microsoft site.

Booking

zeroonea29-Apr-07 9:10

zeroonea

29-Apr-07 9:10

i have a "Booking" table with 3 Column:

BookingID (numeric - primary key)
CheckInDate (datetime)
CheckOutDate (datetime)

- With input are indate and outdate, i want select out free date range in Booking table, like find free room for hotel booking

Thanks.

Christian Graus29-Apr-07 11:15

Christian Graus

29-Apr-07 11:15

I don't see how you can do it with this, you don't have a room number in the table.

Otherwise, you need to use the date functions in SQL to find which rooms are booked on a given date, or a given range, and return the other room numbers.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

zeroonea29-Apr-07 20:10

zeroonea

29-Apr-07 20:10

Can you show me some db design and algorithm about hotel booking? i tried search a hours but...
Thanks.

Christian Graus29-Apr-07 22:23

Christian Graus

29-Apr-07 22:23

I'm not really sure what to say. You should work through a book on database design, and think your problem through. The SQL Server help has all the info you need on how to use date functions.

stored procedure conditional ASC/DESC

dayanandip3-Nov-09 2:25

dayanandip

3-Nov-09 2:25

<pre><code> :-O  :zzz:  :-\  :((  :-O  :omg: :mad::confused::~ :doh: </code>

Meysam Mahfouzi29-Apr-07 4:14

Meysam Mahfouzi

29-Apr-07 4:14

Hi,
Is it possible to make a stored procedure sort columns either ascendingly or descendingly base on a parameter? If not, should we ignore taking advantage of stored procedures and build dynamic SQL commands instead? (building a dynamic sql string in our stored procedure and then calling EXEC())

We don't want to use dynamic SQL commands due to two reasons:

1- preventing SQL injection vulnerability
2- stored procedures containing dynamic SQL are compiled everytime we execute them, which I think should be avoided as far as possible

thanks for any help

Arun.Immanuel29-Apr-07 5:01

29-Apr-07 5:01

U can use if condition to test the argument
and write 2 select commands like:

if(@order=1)
select .... order by asc
else
select .... order by desc

Do U really need this or I am wrong somewhere?

Regards,
Arun Kumar.A

Meysam Mahfouzi29-Apr-07 5:21

Meysam Mahfouzi

29-Apr-07 5:21

Yes this works, the problem is that for two large blocks of select statements, I should copy the whole select block and paste it into 'else' section of if statement just because I wanna sort it descendingly, it's a little annoying, isn't it?
It would be nice if there was a way to enclose the ASC/DESC section of our sql, something like this:

select * from books order by title
if(@order=1)
asc
else
desc

Arun.Immanuel29-Apr-07 5:58

29-Apr-07 5:58

select * from books order by
case when(@order=1) then title end, asc
case when(@order=2) then title end, desc

Hope, this helps U.

Regards,
Arun Kumar.A

Arun.Immanuel29-Apr-07 6:04

29-Apr-07 6:04

I have tried this with the folloing example by changing @a to 1 or 0 alternatively:

It works fine.

declare @a int
set @a=1
select * from tableName
order by case
when @a=0 then columnName
end ,

case
when @a=1 then columnName
end desc

But the problem is we have to design the conditons in such a way that only one of the conditions must evaluate to true.

Change the value "0" to "1" in the above code.
It will throw U an error.

So, please make sure to check the above problem.

Regards,
Arun Kumar.A

Meysam Mahfouzi29-Apr-07 13:43

Meysam Mahfouzi

29-Apr-07 13:43

Thank you, this seems to help solve the problem we have,
I just didn't understand what the need of ',' is after the first when statement here:

when @a=0 then columnName
end ,

why sould we use ',' when we have shown the end of WHEN block with an 'end' keyword? I also tried this and it worked:

when @a=0 then columnName
end asc,

Maybe I'm just not familiar enough with CASE WHEN statement syntax in sql! shame on me!

Just one more question, Is there also a way to select different columns conditionaly too? (I mean something like the second solution you offered, just by enclosing a specific part of our select statement)

Arun.Immanuel29-Apr-07 16:04