Database

change the predicate to use "CASE"

10-Jun-06 7:58

oic, no wonder it works lah;)

thanks ya.. Smile | :)

angela

angelagke9-Jun-06 1:31

9-Jun-06 1:31

how to change SQL statement below to use CASE instant of "AND" "OR" after condition where :-

ALTER PROCEDURE dbo.authorInfor
@au_lname varchar(40)

AS

select title, price, Name = substring (au_lname, 1,40), type
from authors a, titles t, titleauthor ta

where a.au_id = ta.au_id AND t.title_id = ta.title_id AND(au_lname = @au_lname OR au_lname = '' OR au_lname is Null)

Thanks.

Angela

Frank Kerrigan9-Jun-06 2:33

Frank Kerrigan

9-Jun-06 2:33

If AND OR works use it; its must more efficient than using case.

Look where you want to go not where you don't want to crash.

Bikers Bible

angelagke10-Jun-06 7:42

10-Jun-06 7:42

really can works and get the result that what i want with my previous SQL statement...
the matter is my boss want me to change the predicate to use "Case" D'Oh! | :doh:

anyway thanks for your suggestion;)

angela

Eric Dahlvang9-Jun-06 3:40

Eric Dahlvang

9-Jun-06 3:40

CASE is generally used in instances where you want a different field value returned based on specified conditions. Your select statement is only concerned with returning all fields based on certain conditions. In other words, CASE wouldn't be used in the WHERE clause of an sql statement such as yours.

You could clean this up a little though, like this:

select title, price, Name = substring (au_lname, 1,40), type<br />
from authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id<br />
where  au_lname = @au_lname OR isnull(au_lname,'') = ''

Or your where clause could be like this:
where isnull(au_lname,'') IN (@au_lname,'')

Other than that, nothing comes to my mind.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

angelagke10-Jun-06 7:50

10-Jun-06 7:50

no wonder i can't get what i want if i change the predicate to use "CASE" in my SQL statement Hmmm | :|

can see that your SQL statement is more simplify and better than mine Wink | ;)

but 1 of my condition is when user do not insert any value for author's name, all author's name record will display in my result..

if using your SQL statement above, if i insert null for my author's name, then the result is empty..

angelagke12-Jun-06 17:56

12-Jun-06 17:56

I get a SQL statement that can use CASE under my condition

Declare @au_lname char(30)<br />
<br />
Select @au_lname = ''<br />
<br />
select title, price, <br />
Name = substring (au_lname, 1,40), type<br />
from titleauthor ta JOIN authors a on ta.au_id = a.au_id<br />
                    JOIN titles t on ta.title_id = t.title_id<br />
<br />
Where 1 = <br />
Case<br />
   When RTRIM(@au_lname) = '' Then 1<br />
   When RTRIM(@au_lname) <> '' And <br />
        RTRIM(UPPER(@au_lname)) = RTRIM(UPPER(au_lname)) Then 1<br />
   Else 0<br />
End

it's cool

angela

How to write store procedure ?

Eric Dahlvang13-Jun-06 3:12

Eric Dahlvang

13-Jun-06 3:12

I see. Thank you for posting your solution.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

SaravuthYos8-Jun-06 22:00

SaravuthYos

8-Jun-06 22:00

Dear Sir

I created New Package by wizard in SQL server 2000. I want to write store procedure to execute it but i don't know how to write it.

could you tell me how to write it?

Re: How to write store procedure ?

Colin Angus Mackay9-Jun-06 0:02

Colin Angus Mackay

9-Jun-06 0:02

SaravuthYos wrote:
SQL server 2000. I want to write store procedure

See the documentation: CREATE PROCEDURE[^] and EXECUTE[^]

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Re: How to write store procedure ?

Frank Kerrigan9-Jun-06 0:59

Frank Kerrigan

9-Jun-06 0:59

Stored Procedure
===================
create procedure dbo.myProcAS

selet * from customers

go
=====================

running this

exec myProc

Parameter Stored Procedure
=============================

create procedure dbo.MyProc
@CustNumber int
AS

SELECT * from customer
WHERE CustomerID = @CustNumber

go
=============================

running this

exec myProc 1001

Look where you want to go not where you don't want to crash.

Bikers Bible

Re: How to write store procedure ?

Rakesh.Jha.011010-Jun-06 0:13

Rakesh.Jha.0110

10-Jun-06 0:13

very Simple

--Query without parameter
create proc procedure_name
as
Select * from table_name

--conditional query
create proc procedure_name
as
begin
Select * from table_name
End

Rakesh Jha

Simple Database access

Andrew Stampor8-Jun-06 17:35

Andrew Stampor

8-Jun-06 17:35

I would like to write a simple inventory application in C# to keep track of my dvds, music and books. While I'll use this application, I am more interested in it for the opportunity to familiarize myself with accessing a database in C#. However, my knowledge about databases, particularly accessing them in C#, is limited.

I am looking for suggestions on a good way to set up this application and databases. Tutorials, code snippets or full blown applications that access a database in a way similar to what I would be doing are all helpful.

Thanks for any help / suggestions you can give.

Re: Simple Database access

Colin Angus Mackay8-Jun-06 20:08

Colin Angus Mackay

8-Jun-06 20:08

Andrew Stampor wrote:
I am looking for suggestions on a good way to set up this application and databases.

I typed "ADO.NET Tutorial" into a search engine and got this: http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx[^] There are more tutorials out there if you care to look. There are also excellent books on the subject.

Re: Simple Database access

Andrew Stampor11-Jun-06 8:47

Andrew Stampor

11-Jun-06 8:47

Thanks for your help!

I've been looking through the example at the site you suggested and I am having a little trouble getting it to run. I've installed "Microsoft SQL Sever 2005" and am trying to run the code from lesson 2 (http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson02.aspx). When it gets to the "conn.Open()" call, I get an exception, which says:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I have attempted to resolve this but am not sure what needs to be done. I cannot find a setting that specifically allows or disallows remote connections.

Re: Simple Database access [modified]

Colin Angus Mackay11-Jun-06 11:16

Colin Angus Mackay

11-Jun-06 11:16

It means you are trying to access the SQL Server over a network connection. By default, for security, the SQL Server 2005 only permits local connections (Previously TCP/IP was also turned on).

If your SQL Server is installed on the same machine as your application is running then change the Data Source value in the connection string to (local), with the brackets, instead of the machine name, an IP address or localhost.

If your SQL Server is running on a different machine to your application then allow it to accept incoming network connections (Open the SQL Server Configuration Manager). Find "Client Protocols", and in the right side Enable "TCP/IP". If you take this option you may wish to check the properties of the TCP/IP connection and ensure the port is locked down on your internet connection's firewall.

Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog

-- modified at 17:16 Sunday 11th June, 2006

Help with Navigation in Reporting Services

spirit_kleptus8-Jun-06 12:50

spirit_kleptus

8-Jun-06 12:50

Hi all,

Here's where I am stuck:

I am planning to have a sitemapath or a treeview of available reports for users in SQL reporting services 2005. I am pretty sure it is not an easy job. I am thinking of using frames here. Let's say the left frame shows the treeview and the right frame contains the reports page. Assume, there are three folders (Folder A, B, & C) containing few reports each. I want these folders and the corresponding reports(preferable) to be present in the left frame as treeview. And when I click on the folder from the left frame, it should bring up the corresponding Report Manager page showing the folder contents in the right frame.

any help or ideas would be greatly appreciated! Confused | :confused:

Dynamic SQL - Not Returning Unicode

Tristan Rhodes8-Jun-06 6:56

Tristan Rhodes

8-Jun-06 6:56

I've got a dynamic SQL query that is generated inside a SP, and is run using the

EXEC (@SQL)

or

EXEC sp_executesql @strSQL

This has worked fine until now, where we are now using a database for unicode charachters.

All fixed code stored procedures return data correctly in the unicode format, however, all queries run using EXEC / sp_executesql, return '?' instead of unicode charachters.

This is a cause of some serious issues, and i'm really hoping someone knows if there is a fix for it!

Cheers

Tris

Syntax question: Multiple Inner Joins

leckey8-Jun-06 6:04

leckey

8-Jun-06 6:04

I am working with a datagrid in c#/asp.net. Within the datagrid I have to display fields from 10 different tables. I am wondering if my thinking of the syntax is correct.

First, the user enters the part number (Parts.PartNumber)which is actually a varchar. Then it finds the Part.ID. This Part.ID is what most things are going to inner join. Right now I have it just displaying the cost:
Select cost from costs c INNER JOIN Parts p ON c.PartID = p.Id where p.PartNumber = @PartID

But I want it to also display SubParts.Quantity where SubParts.PartID = Parts.ID. So would I set it up like this?

Select cost from costs c, quantity from SubParts sp (INNER JOIN Parts p ON c.PartID = p.ID AND INNER JOIN sp.PartID = p.ID) where p.PartNumber = @PartID

I've got a bunch of these so if someone can point me in the right direction much thanks!

Kschuler8-Jun-06 6:29

Kschuler

8-Jun-06 6:29

Personally, I don't use the "JOIN" keywords very often. You can do a typical join like this:

SELECT cost FROM costs as c, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID

Then your second statement would look like this:

SELECT cost, quantity FROM costs as c, SubPars as sp, parts as p WHERE c.PartID=p.ID AND p.PartNumber=@PartID AND sp.PartID=p.ID

Basically you just need to make sure that whatever you would put in the "ON" portion, you include in the "WHERE" portion.

If you want to keep the "JOIN" keywords, I believe it would have to look like this:

SELECT cost, quantity FROM costs as c INNER JOIN parts as p ON c.PartID=p.ID INNER JOIN SubParts as sp ON sp.PartID=p.ID WHERE p.PartNumber=@PartID

leckey8-Jun-06 6:38

leckey

8-Jun-06 6:38

Okay, elaborating on the second statement...I have two tables that I have to pull the field 'description' (I had nothing to do with the table structures !). So would you say

SELECT cost, quantity, description, description from costs as c, SubParts as sp, WeightType as wt, CostType as ct, parts as p WHERE c.PartID = p.ID AND p.PartNumber = @PartID AND sp.PartID=p.ID...(

and the other joins for the descriptions).

Since description is in there twice, is that a legal statement since the number of select fields matches the number of tables listed? Just curious.

Kschuler8-Jun-06 6:42

Kschuler

8-Jun-06 6:42

I think you will have to use the table aliases and then give aliases to the field names. So if description is from the part table and the sub part table it would look like this:

SELECT cost, quantity, p.description as PartDesc, sp.description as SubPartDesc, ... (etc.)