November 6, 2010 at 5:21 am
Hi Dudes,
I have a problem and if any body know what to do, please help me.
I have to write a stored procedure with one string parameter and it should select some values in DB.
CREATE PROCEDURE [SP_SelectEmployees]
@projectIDs nvarchar(4000)
AS
SELECT *
FROM VEmployee
WHERE ProjID in @projectIDs
But it raise error on debuger....
I have read some articles about this, some of them suggested to select @projectIDs into a table, then select the vEmployee join #ProjectIDs.
But As you know it will be to really slow to join 2 tables VS select one table where parameter in 'some values'
So I Create my method to an string and Then I had execute it.
It is faster , but There is no compilation in base time.
Know the code is like this:
declare @Query nvarchar(4000)
set @Query = 'select *
FROM VEmployee
WHERE ProjID in '+ @projectIDs
exec(@Query)
------------
So If you have any better Idea about what to do, please help me;)
Best Regards,
Ashkan
November 6, 2010 at 12:00 pm
ashkan siroos (11/6/2010)
But As you know it will be to really slow to join 2 tables VS select one table where parameter in 'some values'
It is? Got hard numbers to prove that?
It is faster , but There is no compilation in base time.
Don't understand what you're saying here...
declare @Query nvarchar(4000)
set @Query = 'select *
FROM VEmployee
WHERE ProjID in '+ @projectIDs
exec(@Query)
Ooh, a major SQL Injection vulnerability. I do hope that variable/parameter is checked somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2010 at 3:26 am
Dear Gail,
Thanks for reply,
GilaMonster (11/6/2010)
ashkan siroos (11/6/2010)
But As you know it will be to really slow to join 2 tables VS select one table where parameter in 'some values'It is? Got hard numbers to prove that?
Well I've test it in some of my queries, and also if I'm wrong please tell me.
Cause I think when you join two tables one with a million row and other with about 1k rows , it will take more calculation than just a select with a where closure.
...I'm not Expert, so please correct me.
It is faster , but There is no compilation in base time.
Don't understand what you're saying here...
Well, in dynamic sql , your code is just one dummy string and sqlserver couldn't compile it like when you write a simple SP.
So there is no Code optimization, error checking or etc on your code.
And also as you mentioned :
Ooh, a major SQL Injection vulnerability. I do hope that variable/parameter is checked somewhere.
And This is a big problem with Dynamic sql!
So,Do you have any Idea about what should I do?
Best Regards,
Ashkan
November 7, 2010 at 3:55 am
ashkan siroos (11/7/2010)
Cause I think when you join two tables one with a million row and other with about 1k rows , it will take more calculation than just a select with a where closure.
Don't think, test and see. Is the join slower? Is it significantly slower?
Well, in dynamic sql , your code is just one dummy string and sqlserver couldn't compile it like when you write a simple SP.
So there is no Code optimization, error checking or etc on your code.
Sorry, completely wrong.
Ad-hoc SQL goes though the same parse, bind, optimise procedure as a stored proc does when executed. Error checkin's at run-time, not creation time, but that's the only significant difference
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply