|
set nocount on
declare @ResultSql varchar(8000)
declare @anurag varchar(20)
set @anurag='anurag'
Set @ResultSql='SELECT distinct a.RegID,a.companyname, b.LocalityName FROM Registration AS a INNER JOIN Locality AS b ON a.LocalityID = b.LocalityID inner join pincode as p on a.pincodeid=p.pincodeid WHERE(a.CityID =1) and a.companyname='+@anurag
Create Table #Tbl_Myphonedata
(
Id numeric IDENTITY PRIMARY KEY,
regid numeric,
companyname varchar(30),
localityname varchar(30)
)
--Fill the temp table with the reminders
set @ResultSql = 'select regid,companyname,localityname from( ' + @ResultSql + ' ) OO'
Insert Into #Tbl_Myphonedata
(
regid,companyname,localityname
)
exec
(
@ResultSql
)
select * from #Tbl_Myphonedata
drop table #Tbl_Myphonedata
in above procedure parameter @anurag is string type parameter when i execute exec(@ResultSql) @anurag trate as colunm name but it is a simple parameter
please help me 
no knowledge in .net
|
|
|
|
|
You need to enclose it in quotes so that sql server knows it is a value not a column. Better still, used a parameterised query - faster and less prone to sql injections.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
but i don't know how to enclose string parameter in execute procedure
no knowledge in .net
|
|
|
|
|
That's called learning - look into parameterised queries in BOL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well then, now is a good time to learn - google is your friend 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Just because you don't know how to enclose a parameter or willing to try the parameterized method like Ashfield suggested, you should bash people on the review. People maybe less likely to help in the future.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Hello
I have a program which must read a directory of files and update a database.
I can open my connection at the program start and run the process but it may take a couple of minutes or so. Is is worth opening and closing the database connection for each record I update or can I just hold the connection open for minutes at a time and close it at the end of the process?
Thanks
|
|
|
|
|
You should use connection pooling when you create the connection (creating is the slow bit) and close the connection after each write. This will put the connection into the pool and the next write operation will retrieve and use the same connection.
You could also put all the filenames into a table and bulkcopy them into the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Agreed with Mycroft.
By default connection pooling should be enabled, unless specified otherwise.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
The answer Mycroft Holmes gave is a good and correct way to go.
The only reason I see to keep the connection open in the program is if you have transactional needs over separate writes. For example: if you need a logic where every record is written succesfully to the database or no records at all (commit point is after all separate writes).
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yeah, what they said.
I definitely wouldn't open the connection any sooner than necessary, but I also wouldn't close it any sooner either.
I prefer to get all the data collected and ready to go, then open, process all the stuff, and close.
If holding all the data in memory at once isn't an option, then opening and closing for each may be a good idea.
|
|
|
|
|
I have to find the result of an expression in SQL database.Can you help me to solve this problem ?
|
|
|
|
|
Not without further information. As it stands it tells us nothing about your problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have 2 column in sqlserver that their type is datetime. i want formula column b and thats formula be this: column a - 48hours = column b
i dont know how should i do this?
|
|
|
|
|
Ok I assume you have a history of Access development. SQL Server does not support these columns.
There is not requirements to store the column, you have a number of choices, create a view on the table and add the cacled col or calc the data when you store the information if you require to store it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Add a computed column like:
ALTER TABLE WhatEverTableName ADD ColumnB AS (ColumnA-2);
If you want to store the result in the database (not mandatory) add PERSISTED keyword.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I hav a table emp in which 3 records contain same data...means im not using any primary constraint soooo in all the 3 rows the data is same.
My question is how to delete the other 2 rows from the table and will it be applicable if the case is like......
i hav duplicate rows for 2 different different records
|
|
|
|
|
You need to do it in steps.
select * into allrecs from table1 where 1 = 2 -- create any empty table
insert in to allrecs select * from table1 -- copy all your current data
truncate table table1 -- clear your table
insert into table1 select dustinct * from allrecs -- now you just have unqiue records in your table
drop table allrecs
You may wnt to keep the allrecs table until you are satisfied you have not lost anything that you wanted.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
can't we do without creating a new table.....
beacuse this question was asked in an interview soo i guess it is having some one command line answer....
aneways thanks for the answer
|
|
|
|
|
harsha_mec345 wrote: can't we do without creating a new table
Not that I know of - unless you export the data to a file and read it back in.
harsha_mec345 wrote: soo i guess it is having some one command line answer....
Why? I wouldn't expect 1 line answers.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm guessing that you're using Oracle (based on the table name).
If that's true and all the rows are identical, you can use rowid in delete statement. For example, if you want to leave the 'first' row, the statement could be:
DELETE FROM Emp
WHERE ROWID > (SELECT MIN(ROWID)
FROM Emp);
Correction: As Ashfield pointed out, I misunderstood the question. The statement above will delete all the records except one (for some reason I understood that all are duplicates).
If the table has both duplicate and non-duplicate rows, you can use correlated condition. The condition would be something like the following (all the fields from Emp must be included in correlation):
DELETE FROM Emp a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM Emp b
WHERE a.Field1 = b.Field1
AND a.Field2 = b.Field2...);
Sorry for the confusion,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
modified on Tuesday, November 11, 2008 11:42 AM
|
|
|
|
|
Mika Wendelius wrote: DELETE FROM EmpWHERE ROWID > (SELECT MIN(ROWID) FROM Emp);
Mika, its not often I question your expertise, but won't this delete everything except the very first record in the table? Or am I missing a trick with Oracle (it is along time since I used it)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
My 2 pesos.
Generally, you wouldn't want to do this, because of what your stating. There would typically be a mix match of good and duplicated records. (hence your post would be best suited in this scenario).
The original question stated that the table in question has 3 rows which all contain the exact same value without a primary key and to delete the duplicated rows; meaning only to keep 1 row - so Mika's answer is a little more concise and gets the job done.
Splitting hairs though...
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
You're absolutely correct. Everything is deleted except 1 row. It may be that I've misunderstood the question!!!
Thanks very much for the correction, I really appreciate it! I'll modify the original answer (and perhaps learn to read  ).
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
It's not often I doubt your answer, it just didn't seem right - the new one is what I expected. It happens to us all from time to time - hopefully just after a backup
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
