Database

Re: Get Comma Separated List from Query

6-Jun-06 1:34

Is there any other way without hard coding the values and individually concatenating them as I have to retrieve data from a table with numerous records based on some conditions.

Stephan Samuel6-Jun-06 3:35

Stephan Samuel

6-Jun-06 3:35

You can do something like this:

<br />
<br />
declare @list varchar(1000)<br />
<br />
select @list = isnull(@list, '') + colors + ',' from ColorTable<br />
<br />
select @list<br />
<br />

It's got some extra "features" like that it always ends in a comma, but for every issue you may have with it there's a reasonable solution. Ask if you need more help with that.

sarah_chandran7-Jun-06 18:55

7-Jun-06 18:55

That was just what i needed.
Thanks a lot.

By using the following i have removed the comma that appears at the end of the line
select substring(@list,1,len(@list)-1)

Now can you please tell me if there is a way in which the comma before the last item can be replaced with 'or'

i.e. I would like the output to be like

red,blue,green,..........pink or yellow

-- modified at 0:55 Thursday 8th June, 2006

Stephan Samuel8-Jun-06 0:44

Stephan Samuel

8-Jun-06 0:44

Sure, try this:

<br />
declare @list varchar(1000)<br />
declare @len int<br />
<br />
select @list = isnull(@list, '') + colors + ',', @len = len(colors) from ColorTable<br />
<br />
select @list = substring(@list, 1, len(@list)-1)<br />
select substring(@list, 1, len(@list) - @len - 1) + " or " + substring(@list, len(@list) - @len, @len)<br />

I'm not in front of a SQL Server right now so I can't try it easily, and I may be off by a character (and thus a -1 or +1 in the substring lengths) here and there. I'm sure you can figure out the tweaks, though.

The concept is to have a variable hold the length of the last element. It does this by constantly replacing its value with the length of the current element; at the end of the query, it will contain the length of the last element. Once you know that, it's a matter of string handling to cut your list up and replace text as necessary.

Two more things:

1. If there's a possibility that ColorTable may contain zero, one or two rows, or if you're using a where clause that may return only zero, one or two rows, you may want either some if/then or case when clauses with select count statements to ensure you don't return errors.

2. While it's possible to do this in SQL, as demonstrated, there are some things that are better left to code. I assume you have a really good reason for doing it on the SQL Server instead of in whatever language you're using for your queries, but in general I'd recommend this be done in a code library instead of on the DB server.

sarah_chandran9-Jun-06 0:11

9-Jun-06 0:11

Thanks a lot for your help.
Thanks a lot for taking the time to reply.

sarah_chandran9-Jun-06 0:12

9-Jun-06 0:12

Thanks.It's just what I needed.

Second Highest

Rahul.RK5-Jun-06 21:06

Rahul.RK

5-Jun-06 21:06

Hi all..

Can any one tell me how i can find second largest value of perticular

attribute. Please give query fetching second largest value.

Rahul Kulkarni

WoutL5-Jun-06 23:16

WoutL

5-Jun-06 23:16

Try:

<br />
select Max(attr)<br />
from table<br />
Where attr < (select Max(attr) from table)<br />

Wout Louwers

Mahesh Kulkarni6-Jun-06 20:05

Mahesh Kulkarni

6-Jun-06 20:05

what is about 7th highest. can you tell me plz

The secret of life is not enjoyment
but education through experience.
- Swami Vivekananda.

Dinuj Nath6-Jun-06 20:46

Dinuj Nath

6-Jun-06 20:46

Select *,sal From Emp X Where n =<br />
(<br />
Select Count(Distinct Sal) From Emp Where sal >=X.sal<br />
)

Where n = 7

Need help with dynamic WHERE clause

Mahesh Kulkarni6-Jun-06 23:20

Mahesh Kulkarni

6-Jun-06 23:20

Thanks It is the correct answer . Thanks Again.

The secret of life is not enjoyment
but education through experience.
- Swami Vivekananda.

Slow Learner5-Jun-06 17:57

Slow Learner

5-Jun-06 17:57

Hello Friends,

May be I am missing some thing here while trying to build a dynamic WHERE clause.
this is the final version of my Stored pror

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[USP_SearchVehicleOnYMM]

@Year char(4)
,@Make int
,@Model int
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@Year)<=0
begin
select @Year = NULL
end
IF LEN(@Make)<=0
begin
select @Make = NULL
end
IF LEN(@Model)<=0
begin
select @Model = NULL
end
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
,VS.GroundedDate
,V.[Year]
,VMkL.VehicleMakeDesc
,VMdL.ModelDesc

FROM Vehicle V
Inner Join ContractInfo contrt ON Contrt.VehicleID = V.VehicleID
Left Join DealerShips D ON v.ManufacturerID = D.ManufacturerID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
Inner Join VehicleStatus VS ON Contrt.VehicleID = VS.VehicleID
Left Join ManufacturerMakeModel mmm ON v.MakeID = mmm.makeID
left Join VehicleMakeLookUp vmkl ON mmm.MakeID =
vmkl.VehicleMakeID
left Join VehicleModelLookup VMdl ON mmm.ModelID =
VMdl.ModelID

WHERE
((@Year IS NULL ) OR (V.Year=@Year))
and
(( @make = 0 ) OR (VMkL.VehicleMakeDesc=@make))
and
(( @Model = 0 ) OR (VMdL.ModelDesc=@Model))
END

And when I exectue (right click on the stored proc in management studio
and pick the exectue option) this with out providing any values to the
Year, Make and Model I am getting this error
**********************************Error*****************************************************
Msg 201, Level 16, State 4, Procedure USP_SearchVehicleOnYMM, Line 0
Procedure or Function 'USP_SearchVehicleOnYMM' expects parameter
'@Year', which was not supplied.

(1 row(s) affected)
***************************************************************************************************

Could some one please fix this error and have me understand where I am doing wrong?

Thanks
-L

Re: Need help with dynamic WHERE clause

nguyenvhn5-Jun-06 20:30

nguyenvhn

5-Jun-06 20:30

Specifying a default value for parameters

ALTER PROCEDURE [dbo].[USP_SearchVehicleOnYMM]
@Year char(4)=NULL,
@Make int=NULL,
@Model int=NULL
AS
....

Re: Need help with dynamic WHERE clause

Slow Learner6-Jun-06 2:59

Slow Learner

6-Jun-06 2:59

Hey it helped me Smile | :)

Thank you.

Run Time Error While Execute Sql Server 2005 Integration Services package from C#

Deepu M.I5-Jun-06 15:35

Deepu M.I

5-Jun-06 15:35

Hi

I am trying to Execute an SSIS Package from C# Dot Net.

Below the code i am using.

try
{
Application app = new Application();
Package package = app.LoadPackage("DTSPackage/WinServeMonitoring.dtsx", null);
DTSExecResult result = package.Execute();
label1.Text = "Package Execution " + result.ToString();

}
catch (Exception exp)
{
label1.Text = exp.Message.ToString();
}

I have added reffrence for DTS Run time (using Microsoft.SqlServer.Dts.Runtime;)

But I am getting an Error like this

Error 1 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:\Documents and Settings\vahqmd10\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\Form1.cs 23 17 WindowsApplication1

Error 2 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:\Documents and Settings\vahqmd10\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\Form1.cs 23 39 WindowsApplication1

Error 3 The type 'System.Windows.Forms.Application' has no constructors defined C:\Documents and Settings\vahqmd10\My Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\Form1.cs 23 35 WindowsApplication1

Can any one Help me !

Regards

Deepu M.I

time in sql server

MohamadJaber5-Jun-06 9:28

5-Jun-06 9:28

I create a triggers but I need to add the time
how can I get the time NOW from sql server
thanks

dadax

Re: time in sql server

Michael P Butler5-Jun-06 9:53

Michael P Butler

5-Jun-06 9:53

GetDate() will return the current date and time. It is easy enough to just get the time portion from the returned DateTime

Michael

CP Blog [^] Development Blog [^]

Password !

MohamadJaber5-Jun-06 8:13

5-Jun-06 8:13

I have a table contain ( username,password )
and I'll save that password encrypted in the database

how can I do this ?
thanks

dadax

Colin Angus Mackay5-Jun-06 8:37

Colin Angus Mackay

5-Jun-06 8:37

Encrypt the password first and then save it in the database like anything else.

"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

MohamadJaber5-Jun-06 8:48

5-Jun-06 8:48

How can I encrypt it ?
( from SQL )

dadax

Colin Angus Mackay5-Jun-06 8:52

Colin Angus Mackay

5-Jun-06 8:52

MohamadJaber wrote:
How can I encrypt it ?
( from SQL )

As I already said "Encrypt the password first" then put it in SQL. You cannot encrypt it in SQL without using extended stored procedures in SQL Server 2000 or using CLR stored procedures in SQL Server 2005. Either which way the encryption process does not happen in SQL code.

MohamadJaber5-Jun-06 8:56

5-Jun-06 8:56

sorry annoyance you but plz can you tell how can I enrypted and decrypted it using CLR stored procedures in SQL Server 2005
thanks anyway for you help

dadax

Stephan Samuel6-Jun-06 3:53

Stephan Samuel

6-Jun-06 3:53

You don't want to do that anyway. From a security standpoint, there's practically no value of encrypting passwords on the server if you transmit them over plaintext SQL. Anyone with a packet logger (easy) can get as many passwords as they could by finding an admin password and reading them out of your DB (hard).

albCode5-Jun-06 21:39

albCode

5-Jun-06 21:39

Here u got good example how to hash password
http://www.c-sharpcorner.com/Code/2003/Feb/HashPassword.asp