Company-calendars

Dear All,

How to store a separated company-calendar for each client-company (about
500).
Company-calendar: workingdays, non-workingdays, meetings, etc.

Thanks,

FilipsCheck out this link:

http://www.aspfaq.com/show.asp?id=2519

HTH

--
Message posted via http://www.sqlmonster.com

commas in col

How do I display comma separated values in a column using column A of tbl MMM

Say Col B of tbl NNN = Col C of Tbl MMM

I'm displaying values in tbl NNN & col A from MMM.
I have rows like
xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz
but I want
xxx yyy 1,2,3 zzz

What's the SQL (MS SQL Server)?What's the MS SQL Server SQL for making rows

xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz

into a row
xxx yyy 1,2,3 zzz

Comma seperated IDS in SELECT query with IN.

How can I use list of comma separated IDS in SELECT query with IN.
DECLARE @.TaskID varchar(200)
SET @.TaskID = '(30,32)'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE TaskID IN @.TaskIDI do not want to use Dynamic SQL|||Look at Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690585.836794.90030@.g14g2000cwa.googlegroups.com...
>I do not want to use Dynamic SQL
>|||Look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks Uri,
It solved my problem.|||http://www.aspfaq.com/2248
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690353.281661.41550@.v46g2000cwv.googlegroups.com...
> How can I use list of comma separated IDS in SELECT query with IN.
> DECLARE @.TaskID varchar(200)
> SET @.TaskID = '(30,32)'
> SELECT DISTINCT UserID
> FROM tbl_UserTask
> WHERE TaskID IN @.TaskID
>|||or
DECLARE @.TaskID varchar(200)
SET @.TaskID = '30,32'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE ','+@.TaskID+',' like '%,'+cast(TaskID as varchar)+',%'
Madhivanan

comma separated values to stored procedures

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.shark wrote:

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)

This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written

select * from clients where CONVERT(VACHAR(100),iClientid) = @.iclientid

Quote:

Originally Posted by

end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?

If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.

robert|||See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
>
Thanks in Advance.
>

|||Where ','+@.iClientid+',' like '%,+cast(iClientid as varchar(20))+',%'

Madhivanan

Dan Guzman wrote:

Quote:

Originally Posted by

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.

Comma Separated Values

I need deptno and against/beside it comma separated ename's for join dept.deptno=emp.deptno in a single query.

I require deptno=10 against/beside ename=scott,blake,richard,moxon like this.

can u try this ?

select * from dept,emp where dept.deptno = emp.deptno and emp.ename in ('scott','blake','richard','moxon')

comma separated value

Suppose I have a table like this

code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g

Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
SubhasishOriginally posted by subhasishray
Suppose I have a table like this

code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g

Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
Subhasish

there is an article explaining exactly what u are looking for :
http://www.sqlteam.com/item.asp?ItemID=256

Regards,
Harshal.

comma separated resultant column

I would like the resultant column to be comma separated. Here is sample ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'john')
insert z_my_tbl_del values(2,'mary')
insert z_my_tbl_del values(3,'luke')
insert z_my_tbl_del values(4,'trisha')
insert z_my_tbl_del values(5,'sam')
go
select * from z_my_tbl_del
go
drop table z_my_tbl_del
go
The resultant column should be all in one line as as opposed to separate
lines.
n
======
john,mary,luke,trisha,samThe right way is to do display formatting in the front end. But if you
like doing things wrong and destroying 1NF, use a cursor. Someone else
will show you a proprietary way of doing .|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam|||Somehow I get the feeling that comma-separated values are really *in* this
year.
These things do not belog on the data layer!
But, that's fashion - useless but neat...
Look at this:
http://milambda.blogspot.com/2005/0...s-as-array.html
And what's bad about it? One of the things is the mangled informational
value - the actual relationship between the values 'john', 'mary', 'luke',
'trisha' and 'sam' gets distorted. Are these values cummulative or
alternative? What do they have in common? Did they have anything in common
before? Can they have anything in common ever again?
Anyway, use with care...
ML|||i suggest the following :
SELECT TOP 1 z_my_tbl_del_1.n + ',' + z_my_tbl_del_2.n + ',' +
z_my_tbl_del_3.n + ',' + z_my_tbl_del_4.n + ',' + z_my_tbl_del_5.n AS nn
FROM z_my_tbl_del z_my_tbl_del_1 INNER JOIN
z_my_tbl_del z_my_tbl_del_2 ON z_my_tbl_del_1.i <>
z_my_tbl_del_2.i INNER JOIN
z_my_tbl_del z_my_tbl_del_3 ON z_my_tbl_del_2.i <>
z_my_tbl_del_3.i AND z_my_tbl_del_1.i <> z_my_tbl_del_3.i INNER JOIN
z_my_tbl_del z_my_tbl_del_4 ON z_my_tbl_del_3.i <>
z_my_tbl_del_4.i AND z_my_tbl_del_1.i <> z_my_tbl_del_4.i AND
z_my_tbl_del_2.i <> z_my_tbl_del_4.i INNER JOIN
z_my_tbl_del z_my_tbl_del_5 ON z_my_tbl_del_1.i <>
z_my_tbl_del_5.i AND z_my_tbl_del_2.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_3.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_4.i <> z_my_tbl_del_5.i
Try it!!!
mario
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam

Comma Separated Output with .TXT extension

I have a somewhat strange situation where I need the output of a query thrown into a file with a .txt extension but I need the data in comma separate form. Any idea how I would get this accomplished?
Thanks in Advance,
TechRickIf you use bcp (from the command-line) you can specify the full filename

- Andy Abel|||You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.|||Originally posted by achorozy
You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.

Thanks so much for the info. I'm giving it a go but I'm running into an error. Perhaps you or someone else might know how to fix it.

Upon running the package I get this error: "Incomplete File Format Information - File Cannot Be Opened."

I don't know if this has anything to do with it, but I had to jimmy-rig the package to build it. I am using 3 temporary tables and 4 views in my query. DTS would not let me finish the package unless the main temporary table was already created. So, instead of having the query build it I just have the query delete and recreate it at the end so it is blank.

Any additional help would be appreciated.

TechRick|||Can you post the dts package ? Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Can you post the dts package ? Also, which version of sql server are you using - including service pack ?

I am running 8.00.384 SP1 I believe.

Anyway, I was able to work it out by breaking the task up into smaller steps and running some of them through the job scheduler. I just had the last step run under the DTS package so I could get the output I wanted.

I had to break it up because I always seem to be above the 3200 charater limit for my queries to run as a job. Either my queries are too long or the job scheduler doesn't give enough characters...

Thanks for your assistance.

TechRick

Comma Separated List On Single Line

Hello,

I need to create a single row of data with comma separated fields like such:

Value1, Value2, Value3, Value 4

How can I achieve this with data from a dataset? I do have some other options, but want to consider this being done within the report itself.

Thanks.

You could make an class library with a function that takes several parameters and concatenates the parameters of this method comma-separated. You can call your function in a report:

http://geekswithblogs.net/davyknuysen/archive/2007/03/26/109901.aspx

|||

Brian,

This is how i do it.. this is my sproc to acheive that

ALTERProcedure [dbo].[rpt_LoanAttriApplied]

@.PlanIdint

AS

BEGIN

Declare @.List varchar(255)

Set @.List=' '

Select @.List= @.List+', '+ los.Name

From LoansAttriApplied laa

InnerJoin LoanOptions loson laa.LoanId= los.LoanId

Where PlanId= @.PlanId

selectRight(@.List,Len(@.List)- 2)

--set @.List = Right(@.List, Len(@.List) - 1)

End

|||

Ok, thanks for both.

comma separated list into stored procedure and order of records

I'm passing a comma separated list into a stored procedure
e.g. exec usp_returnProductsFromIdArray '5,4,1,99'
The comma list is being turned into a table (see usp_IntListToTable below)
Here's the proc that returns products that are in the list
CREATE procedure usp_returnProductsFromIdArray
@.prodIdList varchar(1000) = null
as
CREATE TABLE #prodIdTable (productId BIGINT)
if (@.prodIdList is not null)
EXEC usp_IntListToTable @.prodIdList,'#prodIdTable'
select productId, productTitle from products where productId in
(select productId from #prodIdTable)
drop table #prodIdTable
This works well, except I'd like the order of records returned to follow the
order of the IDs in the comma separated list, they aren't, they are in
ascending order (due to the key on the products table)
/*
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
*/
CREATE PROCEDURE dbo.usp_IntListToTable
@.cslist VARCHAR(8000),
@.tablename SYSNAME AS
BEGIN
DECLARE @.spot SMALLINT, @.str VARCHAR(8000), @.sql VARCHAR(8000)
WHILE @.cslist <> ''
BEGIN
SET @.spot = CHARINDEX(',', @.cslist)
IF @.spot>0
BEGIN
SET @.str = CAST(LEFT(@.cslist, @.spot-1) AS INT)
SET @.cslist = RIGHT(@.cslist, LEN(@.cslist)-@.spot)
END
ELSE
BEGIN
SET @.str = CAST(@.cslist AS INT)
SET @.cslist = ''
END
SET @.sql = 'INSERT INTO '+@.tablename+'
VALUES('+CONVERT(VARCHAR(100),@.str)+')'
EXEC(@.sql)
END
END
GOAdd a new column to the temp table to define the sequence. Increment an
integer value in the WHILE loop and insert that into the sequence
number column. Then do:
SELECT P.productid, P.producttitle
FROM products AS P
JOIN #prodIdTable AS T
ON P.productid = T.productid
ORDER BY T.sequence_no ;
David Portas
SQL Server MVP
--|||It works
Thanks very much!!!
www.xwords.co.uk
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129194249.222211.192330@.o13g2000cwo.googlegroups.com...
> Add a new column to the temp table to define the sequence. Increment an
> integer value in the WHILE loop and insert that into the sequence
> number column. Then do:
> SELECT P.productid, P.producttitle
> FROM products AS P
> JOIN #prodIdTable AS T
> ON P.productid = T.productid
> ORDER BY T.sequence_no ;
> --
> David Portas
> SQL Server MVP
> --
>

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:
> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
> > Hi,
> > I want to select a comma delimited text file into a table.
> > Each field separated by a comma should be a field in the table.
> > e.g "faa,fee,fii,foo,fuu" should be insert as
> > field1 field2 field3 field4 field5
> > faa fee fii foo fuu
> >
> > My file is not a fixed length.
> >

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.
Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>
|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent t
o
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
>|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

comma delimited list

Hello,
I have created a small query that will output a list of email addresses that
are separated by commas. I plan to copy and paste the list into a email
invite field (send to: field for an email app). But what is happening is tha
t
only one address appears in the field when I paste because of the added
spaces between the names, example currently outputs like:
blahblah@.blah.com,
blahblah@.blah.com,
blahblah@.blah.com
What I need is it to output like this:
blahblah@.blah.com,blahblah@.blah.com,blahblah@.blah.com
Here is the small query that I am using to extract the emails.
SELECT RTRIM(LTRIM(EMAIL_ADDRESS)) + ',' AS [Name]
FROM evPRUclinicala
WHERE CREATE_DATE BETWEEN '4/1/06' AND '4/30/06'
AND EMAIL_ADDRESS IS NOT NULL
Any help would be appreciated and thanks in advance.
Message posted via http://www.webservertalk.comIn general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the alterantives for doing it at the server, you can check out the
following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||Anith Sen wrote:
>In general, a recommended approach is to extract the resultset outside the
>server and massage the data to appropriate display format using some client
.
>Regarding the alterantives for doing it at the server, you can check out th
e
>following links:
>( For SQL 2005 only )
>http://groups.google.com/group/micr...r />
a9b9b968a
>( For SQL 2000 & 2005 )
>http://groups.google.com/group/micr...r />
66dd9e73e
>
Thanks for the direction, but I am still have some problems. I elected to us
e
the cursor version of your solution: see below
DECLARE @.temptable TABLE ( list VARCHAR( 8000 ) )
SET NOCOUNT ON
DECLARE @.PRUemail VARCHAR( 1000 ), @.emailNext VARCHAR (100)
DECLARE c CURSOR FOR
SELECT EMAIL_ADDRESS
FROM evPRUclinicala
ORDER BY EMAIL_ADDRESS
OPEN c
FETCH NEXT FROM c INTO @.emailNext
SET @.PRUemail = @.emailNext
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF @.emailNext > @.PRUemail BEGIN
INSERT @.temptable SELECT @.PRUemail
SELECT @.PRUemail = @.emailNext
END ELSE
SET @.PRUemail = COALESCE( @.PRUemail + ',', SPACE( 0 ) ) +
@.emailNext
FETCH NEXT FROM c INTO @.emailNext
END
INSERT @.temptable SELECT @.PRUemail
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM @.temptable
But I am still getting a vertical list with no commas. Can you tell me what
I
am doing wrong? Thanks
Message posted via http://www.webservertalk.com|||>> I elected to use the cursor version of your solution:
Why? It is a bad choice to begin with.
I have no idea how the table evPRUclinicala is structured or what the nature
of the data is. Can you post your table DDLs & a few sample data? For
details, refer to: www.aspfaq.com/5006
Anith|||"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:601bd1f4c21b5@.uwe...
>.
> Can you tell me what I am doing wrong?
>.
Yeah, your not using Rac :)
www.rac4sql.net/onlinehelp.asp?topic=236|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
What would have been a better choice? If I should use a different method tha
n
I would appreciate your advise here. If not I will get you the DDLs.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Cursors are generally performance hogs. In most cases, it is wiser to
utilize a set based solution in SQL where a set of rows are manipulated as a
whole rather than doing it one row at a time.
As a long term solution, consider returning the resultset to the client and
manipulate the data using a client application program -- this allows the
stored procedure to be sufficiently generic, eliminates the need to use
server's processing power for string manipulations & loop-based constructs
and leverages the string functionalities of the client language, which in
most cases are more versatile than t-SQL.
If you are convinced to use the server for such stuff, consider the table
valued UDF approach which might be better performing for medium sized
datasets. For SQL 2005, the XML kludge might be faster, though we do not
have much useful benchmarks with this approach.
Alternatively, for large volume transactions of this nature, consider using
3rd party tool's like RAC mentioned in Steve's post.
Anith|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
Ok I will look at the other solutions rather than the one chosen. Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes has multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would return a recordset with each errorCode as its own field. If the newly generated field values could have the same name as the errorCode, that would be good as well.
Thanks,
Stew
I suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew
|||Aaron, I understand your suggestion and gave it some consideration. However, I'm unaware of all the possible error codes which may be encountered. Meaning I'd be entering new errorcodes as they are encountered - which I'm not interested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. However if there is a query that would satisfy my original request, I'd still be interested in that.
Thanks,
Stew
|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net

Comma Delimited Fields

Hello,
I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes ha
s multiple values separated by commas. A typical record would look like:
UID errorCodes
3245 1145 (1), 1051 (7), 0015 (13)
I'd like to query the table based on the UID. Ideally, the query would retur
n a recordset with each errorCode as its own field. If the newly generated f
ield values could have the same name as the errorCode, that would be good as
well.
Thanks,
StewI suggest a change in design, so that there is a row in a related table for
every UID <-> ErrorCode combination. What you're doing here is a very
common non-relational mistake; entities should be single entities, not lists
of entities.
If you keep it the way it is, you're going to have to use ugly brute force
string parsing (LIKE, PATINDEX, etc) to search each column for instances of
an errorcode.
e.g.
WHERE ',' + errorCodes + ',' LIKE '%,1145 (1),%'
Not very efficient at all, and rather cumbersome to program... especially
when you are looking for more than one matching value.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Stewart Partington" <anonymous@.discussions.microsoft.com> wrote in message
news:6D489FF4-E04A-4793-91E5-BCD202805CD8@.microsoft.com...
> Hello,
> I have a table. Among the fields are UID (key) and errorCodes. ErrorCodes
> has multiple values separated by commas. A typical record would look like:
> UID errorCodes
> 3245 1145 (1), 1051 (7), 0015 (13)
> I'd like to query the table based on the UID. Ideally, the query would
> return a recordset with each errorCode as its own field. If the newly
> generated field values could have the same name as the errorCode, that
> would be good as well.
> Thanks,
> Stew|||Aaron, I understand your suggestion and gave it some consideration. However,
I'm unaware of all the possible error codes which may be encountered. Meani
ng I'd be entering new errorcodes as they are encountered - which I'm not in
terested in. Ideally, I'd l
ike the project to be dynamic enough to run itself.
I guess I could automate having the new error codes input into the table. Ho
wever if there is a query that would satisfy my original request, I'd still
be interested in that.
Thanks,
Stew|||> encountered. Meaning I'd be entering new errorcodes as they are
> encountered - which I'm not interested in. Ideally, I'd like the project
> to be dynamic enough to run itself.
How is this impacted by whether you store new errorcodes in a list or as a
separate value in a column?

> I guess I could automate having the new error codes input into the table.
> However if there is a query that would satisfy my original request, I'd
> still be interested in that.
I thought I gave you one (at least the WHERE clause)... looking back on it
you might have to play with spaces, depending on how you're appending new
values to your existing list.
IMHO, you're still approaching this the wrong way and I don't understand
your complaints about my suggested change.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Check out the RAC utility for S2k.
See transposing and then creating a crosstab of
the UID as the row and the errorCodes as the
pivot columns.No sql coding required.
RAC v2.2 and QALite @.
www.rac4sql.net