|
Show the code for the stored procedure, along with the database table structure/schema and someone might be able to shed some light for you.
rcwoods wrote: Was going on 18 minutes and counting when i cancelled it.
How many records do you expect it to return?
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
Thanks for the reply, I think i figured out what was wrong.
It was expected to return alot of records.in the high thousands.
Thanks again for reply
|
|
|
|
|
Out of curiosity, how many thousands of records? Sounds like 18+ minutes is still way too long.
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
i think it was more about my query..i can see now that i need a subquery.
|
|
|
|
|
Glad you got it to work 
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
Could somebody provide an explanation for how to programmatically add a new column to an existing Microsoft Access table, or point me to a resource article?
|
|
|
|
|
You probably want to use the ALTER TABLE .... in a query to do that.
The following, SQL Tutorial[^] might be helpful. Though I've done databases for over 15 years, even I find it helpful for remembering some SQL that I use in rare cases.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
Hi
How can i format numeric data in SQL Server 2005
such as decimal places, use 1000 separator(,)
example: 1234567.23 -> 1,234,567.23
Thanks
|
|
|
|
|
You don't do that in the database, you do that in the interface.
|
|
|
|
|
hi
my table is like
ProductID CagtegoryID
1 1,2,
2 1,
3 4,
4 2,3,
and for SELECT Query if i give the categoryid=1,2 then i want the output like
ProductID CagtegoryID
1 1,2,
2 1,
4 2,3,
Jintal Patel
|
|
|
|
|
I think you need to learn about normalisation. You are storing multiple things in the categoryID column.
By the looks of it you have a many to many join. To create a many-to-many join you need an intermediate table.
So you have a Product table and a Category table and in between you have a ProductCategory table. The intermediate ProductCategory table contains just the primary keys of the source tables.
So,
Product table:
ProductID
1
2
3
4
Category table:
CategoryID
1
2
3
4
ProductCategory table:
ProductID CategoryID
1 1
1 2
2 1
3 4
4 2
4 3
To get the results you want:
SELECT ProductID, CategoryID FROM ProductCategory WHERE CategoryID IN (1,2)
If you need other columns from the product and category tables you'll need a join too. e.g.
SELECT p.ProductID, c.CategoryID, p.ProductName, c.CategoryName
FROM Product AS p
INNER JOIN ProductCategory AS pc ON p.ProductID = pc.ProductID
INNER JOIN Category AS c on c.CategoryID = pc.CategoryID
WHERE c.CategoryID IN (1,2)
|
|
|
|
|
thanks for Solution
i getting my value by Like this
SELECT * FROM [DB_Ecommerce].[dbo].[tb_Ecomm_Product] Where Charindex('8',CategoryId)<>0 or Charindex('2',CategoryId)<>0 ;
Jintal Patel
|
|
|
|
|
Jintal Patel wrote: SELECT * FROM [DB_Ecommerce].[dbo].[tb_Ecomm_Product] Where Charindex('8',CategoryId)<>0 or Charindex('2',CategoryId)<>0 ;
That is hardly a good solution in the face of properly modelling your data.
|
|
|
|
|
creat a temporary table
declare @temp table
(
CagtegoryID varchar(100)
)
insert the CagtegoryIDs into this table one by one
it will be like
CagtegoryID
%,1,%
%,2,%
then try this statement
select * from your table
[your table ] A
where exists (select 1 from @temp B where ','+A.CagtegoryID like B.CagtegoryID
|
|
|
|
|
That has to be the craziest thing I've ever seen and it won't even return the correct answer. It does not handle cases where the category is the first or last on a list. There fore it there is only one or two categories it won't return anything. If there are three or more categories it will only return those in the middle.
|
|
|
|
|
take it carefuly
Patel's table:
ProductID CagtegoryID
1 1,2,
2 1,
3 4,
4 2,3,
search condition :
categoryid=1,2
i transform it to :
CagtegoryID
%,1,%
%,2,%
my statement was
"select * from<br />
[your table ] A <br />
where exists (select 1 from @temp B where ','+A.CagtegoryID like B.CagtegoryID )"
the "','+" can handle the leading first Cagtegory.
and the data in Patel's table "1,2,",the last "," can handle the last one
|
|
|
|
|
Okay - I retract my statement that it won't return the correct data.
However it is still a poor solution. The data should have been properly normalised for this query to work efficiently. This solution can't be optimised by the query optimiser and will always run slowly. That might be fine for a handful of rows but it will take a significant amount of time if the tables grow.
|
|
|
|
|
" it will take a significant amount of time if the tables grow"
yeah ,that's the truth.
i just gived a way to resolve Patel's problem.
in general , yours must be the first and best choice.
|
|
|
|
|
hi
i wrote some line code for one of my friend to get servers list, i simply created an object of SQLDMO.SqlServerClass and called ListAvailableServers or ListInstalledInstances (i cann't remember exactly)  ;P
it worked in my machine but when i transfered it to other machine it threw an exception that what is the "NameList" (return value of the called method) , i have no idea why it's not worked
anybody had any idea,
...and i'm using C# 2005 & SqlServer 2000
thx
I Wish the Life Had CTRL-Z
|
|
|
|
|
try
1.install the sql server client on the other machine
2.register SQLDMO.DLL to the other machine using regsvr32 command line
SQLDMO.DLL locates ~~Program Files\Microsoft SQL Server\80\Tools\Binn
you'll have to copy some other dll files in the same folder (i cann't remember exactly) to the other machine before you can register it.
|
|
|
|
|
newbie's question:
how to convert an int to varchar using sql script,such as 1 ->'01' ?
thanks
|
|
|
|
|
i think cast & convert are easiest,
declare @x int
set @x = 1000;
declare @y nvarchar(10)
set @y = cast(@x as nvarchar)
print @y
set @y = convert(nvarchar,@x)
print @y
I Wish the Life Had CTRL-Z
|
|
|
|
|
ooh. i m sorry
i mean how can i convert that to varchar with leading zero:
10 -> 10
0~9 -> '00' ~ '09'
and it will be used in a " insert ... select ... from ..." statement.
i have a table that containts a datetime column.
now i want to transform the data in this to another
i have to convert datetime to varchar
such as : '2007-09-29 08:05'(datetime) -> '08:05' (varchar)
my poor statement is
"convert( varchar, datepart( hour , [DateTime] ) ) +':' +convert( varchar, datepart( minute , [DateTime]) ) "
but the result is '8:5' what my boss don't want
help me.
thanks a lot.
|
|
|
|
|
How about convert(varchar(5),getdate(),8)
|
|
|
|
|
Great!
thank you very much!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
