Database

Re: Intellisense not showing up in sqlserver 2008

10-Jul-09 3:59

Hi Guys,

I have slq server 2008 installed in my pc, I enabled Intellisense too, but I cant see that anytime I type a query .

Please help

Thanks
Ramm

Manas Bhardwaj17-Jul-09 4:51

Manas Bhardwaj

17-Jul-09 4:51

Which OS are you using?

Manas Bhardwaj
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Re: Intellisense not showing up in sqlserver 2008

Krishna Aditya19-Jul-09 20:38

How to Filter variables for SP, to return only one value from many in SQL View

19-Jul-09 20:38

Hi,

I am using Windows XP version 2002, service pack 3.

Thanks
Ramm

Krishna Aditya9-Jul-09 2:00

Re: How to Filter variables for SP, to return only one value from many in SQL View

9-Jul-09 2:00

Hi ,

In a SQL View, I have 5 boolean variables and one int variable.
out of 5 boolean variables only one variable will be true for a single data row,

Task                              Type      boolVerySmall Datestart                           TagName
----------------------------------------------------------------------------------
Architecture Setup -- Doc Code True               1900-01-01 00:00:00.000      Design_09

idProject   boolsmall   boolMedium   boolLarge      boolVeryLarge      intHours
----------------------------------------------------------------------------
4               False            False         False            False                  0

The above data is for one row... in this row, when I download the data from database to excel sheet, I have to display only the value which is true (boolVerySmall ,boolsmall,   boolMedium,boolLarge, boolVeryLarge, intHours) should be displayed in a s ingle column,

I have written a Stored procedure for this. I am finding it difficult to get a particular row which has TRUE of intHours>0.

I am adding the sql query below. Please help me

> SELECT      dbo.tblResourceTaskList.txtTask, dbo.tblIndividualRelativeData.txtProductType, dbo.tblResourceTaskList.boolVerySmall,
dbo.tblResourceTaskList.dtActualCompletionDate, dbo.tblEffortCodes.txtTagName, dbo.tblResourceTaskList.txtTaskNotes, dbo.tblResourceTaskList.idSubProject, dbo.tblResourceTaskList.idLaunch, dbo.tblResourceTaskList.idResource, dbo.tblResourceTaskList.boolSmall, dbo.tblResourceTaskList.boolMedium, dbo.tblResourceTaskList.boolLarge, dbo.tblResourceTaskList.boolVeryLarge,
dbo.tblResourceTaskList.intDirectHours

FROM            dbo.tblResourceTaskList INNER JOIN dbo.tblProjectUsers ON dbo.tblResourceTaskList.idSubProject = dbo.tblProjectUsers.idSubProject AND
dbo.tblResourceTaskList.idResource = dbo.tblProjectUsers.idUser INNER JOIN
dbo.tblLaunchInfo ON dbo.tblResourceTaskList.idLaunch = dbo.tblLaunchInfo.idLaunch INNER JOIN dbo.tblIndividualRelativeData ON dbo.tblResourceTaskList.idIndividualRelativeEffort = dbo.tblIndividualRelativeData.idIndividualRelativeEffort LEFT OUTER JOINdbo.tblEffortCodes ON dbo.tblResourceTaskList.idEffortCode = dbo.tblEffortCodes.idEffortCode

My stored procedure is
SELECT txtTask, txtProductType, **boolVerySmall**, txtTagName, txtTaskNotes,

dtActualCompletionDate
   FROM tblResourceTaskList_View
WHERE (idSubProject = @idSubProjectIndex )

as the first row from db has TRUE for boolVerySmall, then SP should return this value and if its other than that it should give that value.

Instead of filling *True* in excel sheet, I , have to assign VS for boolVerySmall, S - Small, M - Medium, L - Large VL- VeryLarge in the excelsheet.

So please help me how to work on this.
1. Assigning shortnames (like VS... for the bool var's).
2. Returning only one value (boolverysmall or ... ) and assigning VS to that and fill it in excel.
3. if all the bools are false, then intHours should be assinged...

Kindly help me.

Thanks
Ramm

Mycroft Holmes9-Jul-09 11:36

Mycroft Holmes

9-Jul-09 11:36

Firstly your data structure *sucks*, you should redesign your table to have 1 column for size and 5 possible values. If possible do this NOW, if you leave it you will continually be plagued by this issue.

In the select part of you query use a nested CASE statement,

Case When size1 is null then 'S' else
   Case when size2 is null then 's1' else
      Case when size3 is null then 's2'
end end end as Size

You get the picture. Fix you structure if at all possible, if you own it then suck it up and make the changes, pay the cost for lousy design NOW, it only gets more expensive the longer you leave it.

Never underestimate the power of human stupidity
RAH

Re: How to Filter variables for SP, to return only one value from many in SQL View

Krishna Aditya10-Jul-09 2:16

Re: How to Filter variables for SP, to return only one value from many in SQL View

10-Jul-09 2:16

Thanks a lot for your reply.

I sorted out some other way to display this.

Thank you,
Aditya

Blue_Boy10-Jul-09 3:33

Blue_Boy

10-Jul-09 3:33

Well,post here that solution because it can be helpfull for someone else.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Stored Proc Optimisation

Paul Unsworth8-Jul-09 23:19

Paul Unsworth

8-Jul-09 23:19

Hi All.

I have a stored procedure which returns a set of records. I have an optional parameter in there, so the idea is the proc will return all records if the parameter is null, otherwise filter the results.
Code:

CREATE PROC sp_MyProc (@ItemID AS INT)
AS
BEGIN
   SELECT *
   FROM   myTable
   WHERE  ItemID LIKE CASE WHERE @ItemID IS NULL THEN '%' ELSE '%' + @ItemID END
END

Is there a more effient way of doing this without repeating the query with an IF statement? (My Actual query is quite lengthy, so repeating the code will be messy)

Thanks in advance Big Grin | :-D

oooo, the Jedi's will feel this one....

www.Developerof.NET9-Jul-09 0:17

www.Developerof.NET

9-Jul-09 0:17

CREATE PROC sp_MyProc (@ItemID AS INT)
AS
BEGIN
   SELECT *
   FROM   myTable
   WHERE  ItemID=isNull(@ItemID,ItemID)
END

Check out IsNull[^].

It checks whether the parameter is null and in case it finds it to be null, returns back the column queried which indicates an (1=1) condition.

hope the explanation is clear enough.

When you fail to plan, you are planning to fail.

Paul Unsworth9-Jul-09 0:21

Paul Unsworth

9-Jul-09 0:21

Thats great. Thank you.

Just one question though: If this were to be run against a large table, would the fact that a where clause is included impact the performance? or would the 1=1 condition make it run just as fast as if there were no where clause? Smile | :)

oooo, the Jedi's will feel this one....

J4amieC9-Jul-09 0:51

J4amieC

9-Jul-09 0:51

I like the ISNULL example above, but I have a feeling it will affect performance - I have reservation as to whether SQL will correctly use indexes when resolving the function call.

Ive always used this method, which I know works:

SELECT *
FROM   myTable
WHERE  @ItemID IS NULL OR ItemId = @ItemId

Also, depending on which version of SQL Server you're using, prefixing your stored proc with sp_ affects performance in that it looks in the master db first. I think you'd have to be using a very old version for this to be true, but its good practice not to use an sp_ prefix - if you really like prefixes use usp_ to distinguish from in-build sp's.

Paul Unsworth9-Jul-09 22:07

Paul Unsworth

9-Jul-09 22:07

Thats just what I'm after. Thanks. Big Grin | :-D

oooo, the Jedi's will feel this one....

Jeremy Likness9-Jul-09 8:31

Jeremy Likness

9-Jul-09 8:31

If it's a simple query then it's OK to do this:

IF @ItemID IS NULL 
BEGIN
   SELECT * FROM myTable; 
END
ELSE
BEGIN
   DECLARE @like NVARCHAR(6) 
   SET @like = '%' + CAST(@ItemID AS NVARCHAR(5))
   SELECT * FROM MyTable WHERE ItemID LIKE %like; 
END

Or similiar depending on your needs.

Jeremy Likness
http://csharperimage.jeremylikness.com/

Quering Nested Data in a table

J4amieC9-Jul-09 23:45

J4amieC

9-Jul-09 23:45

Ive also used this method, but i think the OP suggested that the query was long and complex - and therefore duplicating the code inside IF logic was going to get tough.

Good answer though. The OP now has all 3 possible methods!

24891288-Jul-09 22:36

2489128

8-Jul-09 22:36

Hi. I've a table Project(ProjectId, ParentProjectId, ProjectName)

the sample data are --------------------------

1,0, X
2,1,Y
3,2,Z
4,3,W

now for a given project id i want to get projectid, projectname for a given projectid.

e.g. i want 1,X for projectid = 4

i want to SQL query for this. plz help me.

Mycroft Holmes9-Jul-09 1:45

Mycroft Holmes

9-Jul-09 1:45

So what have you tried? Anyone who has read more than 1 chapter of a book on SQL or has spent more than 10 minutes learning what to do can answer this. It is so basic that I suspect you tobe a troll.

Never underestimate the power of human stupidity
RAH

David Skelly9-Jul-09 2:16

David Skelly

9-Jul-09 2:16

To be fair, it's not quite that straightforward if you look at the data.

ProjectId  ParentProject  ProjectName
1          0              X
2          1              Y
3          2              Z
4          3              W

What he wants (I think) is to start from project 4, and go all the way back to the root parent. So, 4 is a child of 3 which is a child of 2 which is a child of 1 which has no parent. So, he wants to know that project 4 (name W) is part of the hierarchy ultimately descending from project 1 (name X).

The SQL to pick out the ultimate root parent when you don't know how deep the hierarchy might be is actually quite challenging. I'm not sure a beginner could write this. I'm not sure I could, to be honest.

Mycroft Holmes9-Jul-09 11:26

Mycroft Holmes

9-Jul-09 11:26

I'm impressed, I did not get that from the question at all. And yes building the hierarchy stuff is quite challenging. I did an article on using it in C# but the SQL stuff I left out for this reason. It is also not well covered.

Never underestimate the power of human stupidity
RAH

Niladri_Biswas9-Jul-09 5:42

Niladri_Biswas

9-Jul-09 5:42

Hi,

First of all the question is unclear.

What I made out is that, you want to get the parent record of 4 i.e. 1

What if I pass the Projectid = 3 or 2?

You should give the relevant scenarios so that others can also make out.

Any way, based on what I understand, here is the solution

SELECT PROJECTID,PROJECTNAME

FROM PROJECT 

WHERE PROJECTID = (	SELECT P1.PARENTPROJECTID + 1 
		FROM PROJECT P1
		INNER JOIN PROJECT P2
		ON P2.PROJECTID - <code>4</code>  = P1.PARENTPROJECTID )

Hope this helps.

But as what I found that if for 4 the parent is 1, the same applies to 2 or 3 also.

In that case why not you are making a top count?

Let me know in case of any concern

Smile | :)

Niladri Biswas