|
Don't know if this is an option for you but one possibility (which could be easy) is to create a stored procedure in order to return correct result set. Stored procedure could return a table type or a cursor (depending on the needs). This way you would have the full power of T-SQL to use and it would be easier to break the logic in to pieces.
Another way (haven't tested it though) could be using pivoting and correlated subqueries.
Mika
|
|
|
|
|
look into Pivot queries (presuming SQL 2005)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
actually i know it is always better for good performance to specify the column name in sql query rather than issuing select * from table name.
please tell me in detail all the reason that why we should not issue select * from table name.
Thanks
tbhattacharjee
|
|
|
|
|
Common reason is for execution time.If our data base is large and number of records are more than it is better to specify the column name rather than * .
|
|
|
|
|
aside from the other response, if you have a database that gets updated or changed fairly often(like mine, always adding on new features), and their data is added to existing tables, depending on where the column is added, removed (old, unused, found a better way, etc..), or renamed(doesnt happen much) it could break your code, if you're expecting an int in col 3 and i add a column to the begining of the table it could change the datatype in column 3 and you're code then wouldnt read it correctly. however if you list out every column you want, in selects and inserts you wont have this problem unless a column is renamed(rare) or removed, and its easy enough to do a search through the code and a sql script file to find where its used if you're going to remove it.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Example from the work force:
We have a vendor supplied system that has too much data in it; we only want to keep 2 years worth of data online and archive anything over 2 years old.
The archiving system used a statement similiar to:
Insert into Archive_Table select * from Source_Table
An in-house system was created to copy the data from production to archive, however, over time, the columns in the vendor supplied system chnaged: more were added, order changed etc.
The archiving system failed because the destination table was not updated to match the source table and the insert statement was expected the SAME number of columns, order and type of columns in the destination table.
Hope that helps.
Tim
|
|
|
|
|
Because all that data has to come across the network to you, increasing network load needlessly.
On the other hand, if you really want all the columns, then using * is OK.
|
|
|
|
|
If you explicitly specify the columns in a SELECT statement, they are guaranteed to be returned in the order you specify. Consequently, you may safely identify the columns as theReader(0), theReader(1), etc. rather than having to have the system look up the column names on every theReader("customerName") access. If, for whatever reason, the expected columns don't exist, it's probably better to have the problem caught in the SELECT statement than to throw an exception when the non-existent data is accessed.
If your code is genuinely interested in getting all the columns that are or ever will be in the database, including any that may be added in future, then SELECT * is appropriate. This may be the case, for example, in a database viewer (though unless a naming convention is used to indicate fields that should be regarded as confidential, such an approach could be dangerous even there). If you're only interested in a few fields, even if the fields of interest are at present the only ones in the database, you should specify explicitly the fields of interest.
|
|
|
|
|
iam having a string like this 'hi this is "john" iam a 'good' boy' how to split this string so that the words should come as
hi
this
john
iam
a
good
boy
plz help in this regard.
|
|
|
|
|
Cross posting is considered rude...
In any case, what has this got to do with Oracle?
|
|
|
|
|
just hit the return key on the typewriter instead of space key.
|
|
|
|
|
Another option is to insert enough spaces if word wrapping is on... 
|
|
|
|
|
Hi all,
I wrote a StoredProcedure which returns a dataset.
But when i drag that SP into .dbml file, the return type of SP is becomes int, not ISingleResultType<>. I dont know reason for this.
When i call that SP i got Error / Return vale 0
Plz Help me.
Here my SP:
ALTER PROCEDURE [dbo].[USP_GetAdNames](
@user varchar(50),
@specialization smallint = null,
@country smallint = null,
@state tinyint = null,
@city varchar(50)= null,
@from datetime = null,
@to datetime = null
)
AS
CREATE TABLE #TEMPADS
(
int_ID int,
str_Name varchar(50)
)
DECLARE @query nvarchar(1000)
BEGIN
SET @query = N'INSERT INTO #TEMPADS SELECT transAds.int_AdID,transAds.str_Name
FROM TRANS_ADS AS transAds
WHERE transAds.str_UserID = '''+@user+''''
if (@specialization != null OR @specialization != 0)
SET @query = @query + ' AND tansAds.smallint_Specialization = '+cast(@specialization as varchar(10))
if (@country != null OR @country != 0)
SET @query = @query + ' AND transAds.smallint_CountryCode = '+cast(@country as varchar(10))
if(@state != null OR @state != 0)
SET @query = @query + ' AND transAds.tinyint_FocusState = '+cast(@state as varchar(10))
if(Ltrim(Rtrim(@city)) != '' OR Ltrim(Rtrim(@city)) != null)
SET @query = @query + ' AND transAds.str_FocusCity = '''+@city +''''
if(@from IS NOT NULL)
SET @query = @query + ' AND transAds.dt_Modifiedon >='''+convert(varchar(25),@from,101)+''''
if(@to IS NOT NULL)
SET @query = @query + ' AND transAds.dt_Modifiedon <='''+convert(varchar(25),@to,101)+''''
print @query
EXECUTE sp_sqlexec @query
SELECT int_ID,str_Name FROM #TEMPADS
END
///// When i execute in SQl Management Studio iam getting records as dataset. But not in my program.
I am calling that sp like this:
var query = from temp in DataContext1.USP_GetAdNames("kvs",null,null,null,null,null,null) select temp;
.....................................................
I also tried creating a partial class in DataContextMapping.Designer.cs file but no use.
Please suggest me, what went wrong in my SP.
|
|
|
|
|
Try using table variables instead of temporary tables since the result set of a stored procedure with temporary tables can not be inferred.
Regards,
Syed Mehroz Alam
|
|
|
|
|
The database engine for SQL Server 2008 Express is downloadable from: http://www.microsoft.com/express/sql/download/[^]
Management Studio Basic and Advanced Services (Full-Text Search and Reporting Services) are not yet released, but based on blogs the target is at the end of August.
In order to install SQL Server 2008 Express, you need to install first:
- Microsoft .Net Framework 3.5 SP1
- Windows Installer 4.5
Standalone version of Books Online can be downloaded from Microsoft SQL Server 2008 Books Online[^]
[Edit]
Also tools and advanced services are now released for download
[/Edit]
modified on Saturday, August 23, 2008 1:51 AM
|
|
|
|
|
|
No, just thought that this could be useful info. That's why I selected News for message type.
|
|
|
|
|
hi Guys
i have a Stored procedure
Create Procedure [prcLoginv]
(
@User_name varchar(20),
@U_Password varchar(23),
@Results int Output
)
IF EXISTS (Select * from Login
where Username = @User_name and U_Password = @U_Password)
return 1
else
return 0
i get the following Error
Msg 156, Level 15, State 1, Procedure prcLoginv, Line 8
Incorrect syntax near the keyword 'IF'.
what is wrong
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Take the SELECT statement to a separate statement and take the return values (for example username or COUNT) to a local variable and then test the variable value in IF statement.
Mika
|
|
|
|
|
Given that he has a syntax error (code won't even compile) how will checking the logic of the stored procedure help?
|
|
|
|
|
That's true, didn't notice the missing AS keyword  .
|
|
|
|
|
You are missing the keyword AS
CREATE PROCEDURE Name
@parameters parameter_types
AS
BEGIN
-- Code goes here.
END
|
|
|
|
|
Thank you guys,
But how can i forget such small thing.
Thanks a lot 
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
It's pretty obvious that password hashes for SQL Server 8.0 and 9.0 are different. So when migrating a server from SQL server 2000 to SQL Server 2005, everyone's password pretty much gets jacked.
Is there a way to login to a SQL Server 2005 instance using SQL Server 200 credentials?
I've tried specifying the provider and driver in my connection string for my ADO objects but get an error thrown back at me saying that the "provider" keyword is not recognized.
|
|
|
|
|
So here's the story. I was wrong about the hashes.
But, 2005 uses case sensitive passwords by default. So when our vendor's application converts all passwords to upper before connecting, of course it'll do the same when it creates the users.
So I have the misfortune of being an idiot and writing supplementary applications for badly designed software. Yay for me, and all I have to do now is edit a few libraries.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
