Hi,
I am writing a stored procedure which needs to select different tables
based on different parameters. I used to use 'CASE' to select
different columns, so I tried to use following statement like "select
* from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
What i need to achieve is dynamically select tables based on
parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
'ORDER' table.
Could anyone help me with this issue?
ThanksYOu have to use dynamic SQL for this. You can stuck you sql coe
together and execute it then with EXEC or sp_executesql. Dynamic sql
has some limitations and may be the nail to your coffin, the best would
be to read Erlands article first before implementing this:
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.|||Ron (rzhou@.mettle.biz) writes:
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
Sounds ugly. Maybe there is reason for a table redesign? Then again,
it could make sense.
Anyway, dynamic SQL is what you need to do this. I have a general
article on dynamic SQL on my web site, and then there is another which
discusses dynamic search conditions in particular.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Guys,
Thanks for your help! great articles
Ron|||>> I am writing a stored procedure which needs to select different tables ba
sed on different parameters. <<
Have you thought about what that means in terms of your design?
Assuming that you have a relational schema, each table is a TOTALLY
DIFFERENT KIND OF ENTITY , what will meaningful name will you give this
nightmare? I propose that you use
" Get_squids_or_automobiles_or_Britney_Spe
ars" as the name. It sounds
pretty vague and stupid when you think about it.
Gee, sure sounds like it violates coupling and cohesion -- remember
those fundamentals of programming from your freshman year in Comp Sci?
That is FAR more fundamental than SQL.
You have never read a book on SQL. Not even half a book! The CASE
expression returns a value of a known data type, just like any other
expression. SQL is compiled; you are not writing BASIC.
The stinking, dirty, unmaintainable kludge that you will get on a
Newsgroup is dynamic SQL. That way you can avoid RDBMS and fake 1960's
BASIC code on the fly.
Why won't anyone else tell you this? If we give you that quick answer
or a few links, you will go away. But if someone yells at you for
your lack of fundamentals, then your feeling might be hurt (we assume
you are child, not an adult) or that you will ask questions that will
require serious study and we don't want to post a few quarters of
college level work on a newsgroup.
If you want a REAL answer, we need DDL, a good spec, sample data, etc.
And you might have a horrible schema that needs to be re-done, the
queries might be really hard, etc. Welcome to the real world!!|||Don't be intimidated,... Dynamic sql will do what you wish...That is the
answer to your question.
However, you might wish to ensure you have a good design, and that you are
not making a problem for yourself later... Dynamic SQL does help us solve
problems, and we use it when we need to -
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Ron" wrote:
> Hi,
> I am writing a stored procedure which needs to select different tables
> based on different parameters. I used to use 'CASE' to select
> different columns, so I tried to use following statement like "select
> * from CASE @.id WHEN 0 then 'EMPLOYEE' END". It doesn't work.
> What i need to achieve is dynamically select tables based on
> parameters, such like :@.id = 1 then from 'EMPLOYEE', @.id = 0 then from
> 'ORDER' table.
> Could anyone help me with this issue?
> Thanks
>|||IMO Dynamic sql is possible.
BOL states that you can not use can not us parameters with openRowset and fr
om a
pure technical sense, I guess it's a valid statement. But where there is wi
ll
there is a way.
You can see I build a variable @.SQL based in part on parameters passed to th
e
procedure. Is this not dynamic SQL?
CREATE Procedure usp_GetPeriodLabor @.bp as char(5),@.ep as nvarchar(5) as
Declare @.sql nvarchar(500)
SET @.SQL = 'Select * into tPeriodLabor_tmp from OPENROWSET(''MSDAORA'',
''oralcleinstance'';''user'';''password'
',
''select detail_Date,employee_sys_id,pay_period,L
D_CODE1 as
CostCenter,ld_code2,ld_Code3 as account,
stop_time,start_time, (stop_time-start_time)/60
from easp.timecard_detail
where (pay_Period >= ' +@.bp+' and
detail_date <= ' +@.ep+') and (timecode_sys_id = 128 or timecode_sys_id = 136
or timecode_sys_id = 142 or timecode_sys_id = 163 or timecode_sys_id = 166)'
')'
Exec (@.sql)
GO
-- Posted with NewsLeecher v3.0 Beta 6
-- http://www.newsleecher.com/?usenet
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Dynamically Changing the length of a varchar(n) field
Hi Everyone,
There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.
>
There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.
>
Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.
>
Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.
>
Kind regards
>
robert
I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?
I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?
Here is my code (I'm working in an ASP environment):
<%
var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"
sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"
On Error Resume Next
conn.Execute sql
If err = -2147217833 Then
Response.Write "Error Recognized Successfully!<br /><br />"
sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &
Len(var_txt) &
") NOT NULL"
On Error Resume Next
conn.Execute sql
If err<>0 Then
Response.Write "Error while trying to alter Column:<br
/>" & err & "
= " & err.description & "<br />"
Else
Response.Write "Column altered successfully to: " &
Len(var_txt) &
"<br />"
sql = "Insert Into Table1 (text) Values ('" & var_txt &
"')"
On Error Resume Next
conn.Execute sql
If err<>0 Then
Response.Write "<br />Error number 2:<br />" &
err.description &
"<br />"
Else
Response.Write "Now it was added successfully!
HaHa!<br />"
End If
End If
Else
Response.Write "Success."
End If
%>
Thanks in advance!On 26.12.2006 12:46, John wrote:
Quote:
Originally Posted by
I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?
>
I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?
There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.
Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.
Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.
Kind regards
robert|||Hey Robert,
Thank you for your reply! That's pretty good advice and I think I'll do
just that, since it's far less complicated than what I was trying to do
(obviously).
Robert Klemme wrote:
Quote:
Originally Posted by
On 26.12.2006 12:46, John wrote:
Quote:
Originally Posted by
I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the error is given! i.e. Is there some
kind of a way to "test" the length of the field while Inserting the
value into it, and to have it automatically increase its length to the
length of the value being inserted, in case the value is too big?
I've been able to do this in a "primitive" way, simply by identifying
the specific error number in case the value is being truncated, and
then increasing the length of the varchar(n) field by using the ALTER
command, and then duplicating the insert statement, but is there a
standard (shorter) way of doing this?
>
There are several things to say to this. First, the length of a VARCHAR
column should generally be dependent on business requirements - i.e. the
length comes before the inserted values. You can view it as a
requirement (for example a zip code is just 5 characters here in
Germany) and data not satisfying that requirement basically should not
go into that column. In this case the table will only change if the
business requirement changes (they changed length of zip code from 4 to
5 after the wall broke down over here) and not according to data inserted.
>
Having said that the easiest solution in your case (i.e. if you want to
maintain that you have to insert strings with arbitrary length) is to
just set the length to the max length allowed for that column (in SQL
Server 2k it's 8000 IIRC). Additionally you will have to take
application level measures to limit the length of inserted values to the
columns width.
>
Alternatively you could use a TEXT column but changing an existing
VARCHAR to a TEXT column is not as easy as executing an ALTER TABLE.
>
Kind regards
>
robert
Wednesday, March 7, 2012
Dynamic where clause with if..else or case
Hello all...
I am trying rewrite an sp that I have that is considered dynamic cause it builds a where clause based on a bunch of if statements then adds it to the the end of select
i.e
if...@.where = @.where + ' llll '
if...@.where = @.where + ' llll '
select @.statement = @.statement + @.where
exec(@.statement)
I have rewritten most of it to but I have several conditions that use ' contains' for the condition and I can't get SQL server to recognize an if statement or a case statement.
Is it possible to use either statement inside a where clause?
i.e
where if a = 1 then d=e
else contains(.....)
thanks
No. You cannot use control of flow statements in the WHERE clause. CASE is an expression so you can use it in the WHERE clause. Since CONTAINS is a predicate in itself, you need to do something like:
case ... when .. then (select 1 where contains()) else ... end = 1
But it is not clear why you need to use dynamic SQL or build WHERE clause like this. Using dynamic SQL can cause performance problems and has lot of security implications. So you need to be aware of the risks while using it. CONTAINS predicate in SQL Server 2000 (from SP3 I think) and SQL Server 2005 can take a variable for the search expression so you probably don't dynamic SQL for this. If you have different checks to perform then you may actually be better off using multiple IF statements that call specific SPs with SELECT statements. This will provide the best performance advantage.
|||Thanks. I need the dynamic where based on search criteria because it was decided to make one SP compared to about 50 or more because there are possible combinations of the criteria...
The purpose of the contains is to search a text field for a value that is close to what what asked for. This is one of our slowest SPs so I thought I would give it a shot to make it quicker but I guess I will have to just be happy with what it is.
Thanks for the help.
|||Check out the article at the link below for various techniques on how to do dynamic searches using SPs.
http://www.sommarskog.se/dyn-search.html
Sunday, February 26, 2012
Dynamic views
Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:
> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>
Friday, February 17, 2012
Dynamic SQL replace with CASE statement.
Hello All,
I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
The code that I replaced it with
seems to be worst than the original code. Would any one know why.
The first batch of SQL statements (Dynamic Sql) is the original code and I
tried to replace this code with the
code in the second batch. If you notice, I have used a CASE statement to
mimic the IF part of the code. But
in the Execution Plan I see a Clustered Index scan in the replaced code but
in the execution plan of the original
code I see a Index s
. I had to put the OPTION statement to avoid
parellelism
Would any one know why it is doing an Index S in the Original case and
Clustered Index Scan in the replaced
code ?
Gopi
USE Northwind
GO
Declare @.str varchar(200)
SET @.str = 'select * from Orders '
Declare @.i int
set @.i = 10252
IF @.i <> 0
SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
Print @.str
Exec(@.str)
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN OrderID
ELSE @.i
END
OPTION (MAXDOP 1)
GO
select * from Orders where OrderID = 10252
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = @.i
GOHello rgn,
I have a feeling that this may help.
If not then get back to me.
http://support.microsoft.com/defaul...kb;en-us;841627
Peter
"Status quo, you know, that is Latin for "the mess we're in."
Ronald Reagan
"rgn" wrote:
> Hello All,
> I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
> The code that I replaced it with
> seems to be worst than the original code. Would any one know why.
> The first batch of SQL statements (Dynamic Sql) is the original code and I
> tried to replace this code with the
> code in the second batch. If you notice, I have used a CASE statement to
> mimic the IF part of the code. But
> in the Execution Plan I see a Clustered Index scan in the replaced code bu
t
> in the execution plan of the original
> code I see a Index s. I had to put the OPTION statement to avoid
> parellelism
> Would any one know why it is doing an Index S in the Original case and
> Clustered Index Scan in the replaced
> code ?
> Gopi
> USE Northwind
> GO
> Declare @.str varchar(200)
> SET @.str = 'select * from Orders '
> Declare @.i int
> set @.i = 10252
> IF @.i <> 0
> SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
> Print @.str
> Exec(@.str)
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = CASE
> WHEN @.i = 0 THEN OrderID
> ELSE @.i
> END
> OPTION (MAXDOP 1)
> GO
> select * from Orders where OrderID = 10252
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = @.i
> GO
>
>
>|||Peter,
The query is not based on a View if you noticed.
<<. The max degree of parallelism configuration option for your instance of
SQL Server is set to 0 or to a number that is greater than 1.
. The query is based on a view.>>
I would understand if the query is Slow had I used operators such as <> ...
like '%xyz%'
as it makes sense to scan the table. But I dont understand why it has to do
a Clustered
Index Scan because I introduced a CASE statement to avoid dynamic SQL.
Gopi
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
> Hello rgn,
> I have a feeling that this may help.
> If not then get back to me.
> http://support.microsoft.com/defaul...kb;en-us;841627
> Peter
> "Status quo, you know, that is Latin for "the mess we're in."
> Ronald Reagan
>
>
> "rgn" wrote:
>|||My apologies for both the rubbish earlier post and what I'm going to say now
.
I know what cause the execution path difference, but not why.
I did a test where the OrderID was a clustered index with 10000 rows
populated by identity.
If you change the where clause so instead of it returning OrderID, it
returns say 0, then you get a clustered index s, similar to having the
statement without the CASE Statement, so there is something about 'ID' that
causes a difference.
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN 1
ELSE @.i
END
Anyway sorry I can't be more of a help, probably one of the really big
brains know the answer.
Peter
"It's true hard work never killed anybody, but I figure, why take the chance
?"
Ronald Reagan
"rgn" wrote:
> Peter,
> The query is not based on a View if you noticed.
> <<. The max degree of parallelism configuration option for your instance o
f
> SQL Server is set to 0 or to a number that is greater than 1.
> .. The query is based on a view.>>
> I would understand if the query is Slow had I used operators such as <> ..
.
> like '%xyz%'
> as it makes sense to scan the table. But I dont understand why it has to d
o
> a Clustered
> Index Scan because I introduced a CASE statement to avoid dynamic SQL.
> Gopi
>
> "Peter 'Not Peter The Spate' Nolan"
> <PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
> news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
>
>
I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
The code that I replaced it with
seems to be worst than the original code. Would any one know why.
The first batch of SQL statements (Dynamic Sql) is the original code and I
tried to replace this code with the
code in the second batch. If you notice, I have used a CASE statement to
mimic the IF part of the code. But
in the Execution Plan I see a Clustered Index scan in the replaced code but
in the execution plan of the original
code I see a Index s
. I had to put the OPTION statement to avoidparellelism
Would any one know why it is doing an Index S
in the Original case andClustered Index Scan in the replaced
code ?
Gopi
USE Northwind
GO
Declare @.str varchar(200)
SET @.str = 'select * from Orders '
Declare @.i int
set @.i = 10252
IF @.i <> 0
SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
Print @.str
Exec(@.str)
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN OrderID
ELSE @.i
END
OPTION (MAXDOP 1)
GO
select * from Orders where OrderID = 10252
GO
Declare @.i int
set @.i = 10252
select * from Orders where OrderID = @.i
GOHello rgn,
I have a feeling that this may help.
If not then get back to me.
http://support.microsoft.com/defaul...kb;en-us;841627
Peter
"Status quo, you know, that is Latin for "the mess we're in."
Ronald Reagan
"rgn" wrote:
> Hello All,
> I was trying to optimize a SP by replacing a Dynamic SQL with Ad-Hoc Sql.
> The code that I replaced it with
> seems to be worst than the original code. Would any one know why.
> The first batch of SQL statements (Dynamic Sql) is the original code and I
> tried to replace this code with the
> code in the second batch. If you notice, I have used a CASE statement to
> mimic the IF part of the code. But
> in the Execution Plan I see a Clustered Index scan in the replaced code bu
t
> in the execution plan of the original
> code I see a Index s
. I had to put the OPTION statement to avoid> parellelism
> Would any one know why it is doing an Index S
in the Original case and> Clustered Index Scan in the replaced
> code ?
> Gopi
> USE Northwind
> GO
> Declare @.str varchar(200)
> SET @.str = 'select * from Orders '
> Declare @.i int
> set @.i = 10252
> IF @.i <> 0
> SET @.str = @.str + 'where OrderID = ' + convert(varchar,@.i)
> Print @.str
> Exec(@.str)
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = CASE
> WHEN @.i = 0 THEN OrderID
> ELSE @.i
> END
> OPTION (MAXDOP 1)
> GO
> select * from Orders where OrderID = 10252
> GO
> Declare @.i int
> set @.i = 10252
> select * from Orders where OrderID = @.i
> GO
>
>
>|||Peter,
The query is not based on a View if you noticed.
<<. The max degree of parallelism configuration option for your instance of
SQL Server is set to 0 or to a number that is greater than 1.
. The query is based on a view.>>
I would understand if the query is Slow had I used operators such as <> ...
like '%xyz%'
as it makes sense to scan the table. But I dont understand why it has to do
a Clustered
Index Scan because I introduced a CASE statement to avoid dynamic SQL.
Gopi
"Peter 'Not Peter The Spate' Nolan"
<PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
> Hello rgn,
> I have a feeling that this may help.
> If not then get back to me.
> http://support.microsoft.com/defaul...kb;en-us;841627
> Peter
> "Status quo, you know, that is Latin for "the mess we're in."
> Ronald Reagan
>
>
> "rgn" wrote:
>|||My apologies for both the rubbish earlier post and what I'm going to say now
.
I know what cause the execution path difference, but not why.
I did a test where the OrderID was a clustered index with 10000 rows
populated by identity.
If you change the where clause so instead of it returning OrderID, it
returns say 0, then you get a clustered index s
, similar to having thestatement without the CASE Statement, so there is something about 'ID' that
causes a difference.
select * from Orders where OrderID = CASE
WHEN @.i = 0 THEN 1
ELSE @.i
END
Anyway sorry I can't be more of a help, probably one of the really big
brains know the answer.
Peter
"It's true hard work never killed anybody, but I figure, why take the chance
?"
Ronald Reagan
"rgn" wrote:
> Peter,
> The query is not based on a View if you noticed.
> <<. The max degree of parallelism configuration option for your instance o
f
> SQL Server is set to 0 or to a number that is greater than 1.
> .. The query is based on a view.>>
> I would understand if the query is Slow had I used operators such as <> ..
.
> like '%xyz%'
> as it makes sense to scan the table. But I dont understand why it has to d
o
> a Clustered
> Index Scan because I introduced a CASE statement to avoid dynamic SQL.
> Gopi
>
> "Peter 'Not Peter The Spate' Nolan"
> <PeterNotPeterTheSpateNolan@.discussions.microsoft.com> wrote in message
> news:40923177-761A-465B-AFCD-BA4B53A24927@.microsoft.com...
>
>
Dynamic SQL or CASE statement?
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf issue
s
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general? Currently
,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
para
aWhy sort it at all? Can't ASP handle that? It would be more scalable than
making SQL Server do all the sorting.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf
issues
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general?
Currently,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
paraa|||tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:
> Why sort it at all? Can't ASP handle that? It would be more scalable tha
n
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort optio
n
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||How "lean" are we talking? I find it hard to believe that an IIS server
can't handle this.
That said, you can do dynamic sorting without resorting to dynamic SQL:
declare @.sort varchar (100)
set @.sort = 'Country'
select
*
from
dbo.Customers
order by
case when @.sort = 'Country' then Country end
, case when @.sort = 'CustomerID' then CustomerID end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:C3B84B96-3839-4A66-8FAE-0F737FBE21C5@.microsoft.com...
tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:
> Why sort it at all? Can't ASP handle that? It would be more scalable
> than
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa
>|||If you simply need to sort by a single column, then the ASP.NET DataGrid
could handle this. However, if your sort requirements are more complex
involving multiple columns or complex conditions, then it would probably
need to be done within the stored procedure. A conditional [order by] clause
using a case expression would be much preferable than dynamic SQL. Just as a
side note, you can also include case expressions in the [where] clause as
well for conditional row filtering, or even in the [group by] clause for
conditional grouping.
In the example below, there are 3 different sort options enabled using a
parameter called @.sort :
. . .
order by
case @.sort
when 0 then NULL
when 1 then company
when 2 then region
end,
case @.sort
when 0 then accountno
when 1 then lastname
when 2 then lastname
end
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> paraa|||thx a bunch JT and tom..thatz what i was arriving at when i mentioned CASE
stmt v/s dyn sql...just wanted to make sure with the xperts
"JT" wrote:
> If you simply need to sort by a single column, then the ASP.NET DataGrid
> could handle this. However, if your sort requirements are more complex
> involving multiple columns or complex conditions, then it would probably
> need to be done within the stored procedure. A conditional [order by] clau
se
> using a case expression would be much preferable than dynamic SQL. Just as
a
> side note, you can also include case expressions in the [where] clause as
> well for conditional row filtering, or even in the [group by] clause for
> conditional grouping.
> In the example below, there are 3 different sort options enabled using a
> parameter called @.sort :
> .. . .
> order by
> case @.sort
> when 0 then NULL
> when 1 then company
> when 2 then region
> end,
> case @.sort
> when 0 then accountno
> when 1 then lastname
> when 2 then lastname
> end
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
>
>
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf issue
s
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general? Currently
,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
para
aWhy sort it at all? Can't ASP handle that? It would be more scalable thanmaking SQL Server do all the sorting.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
hi all,
we are having an asp app that is fetching a big resultset to display in a
list screen. The screen has 4 or 5 columns which have the click sort option
(ie., on clicking the column hdr, the list needs to be sorted out).
Currently, we are using a procedure which would dynamically construct the
sort order and use exec(@.stmt) to send back the resultset. Coz of perf
issues
of late, we are thinking of removing dyn sql in the proc and recreating it
using CASE/IF statements. Which option would be better in general?
Currently,
the dyn sql has high counts of CPU.
would appreciate any help in this regards
thx
para
a|||tom,we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:
> Why sort it at all? Can't ASP handle that? It would be more scalable tha
n
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "para
a" <paraa@.discussions.microsoft.com> wrote in message> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort optio
n
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> para
a>|||How "lean" are we talking? I find it hard to believe that an IIS server
can't handle this.
That said, you can do dynamic sorting without resorting to dynamic SQL:
declare @.sort varchar (100)
set @.sort = 'Country'
select
*
from
dbo.Customers
order by
case when @.sort = 'Country' then Country end
, case when @.sort = 'CustomerID' then CustomerID end
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:C3B84B96-3839-4A66-8FAE-0F737FBE21C5@.microsoft.com...
tom,
we have lean clients and so after lot of brainstorming have put the sorting
on the server side..sorting on client is not an option over here..as per the
scalability factor - may not be that much of an issue..thatz why we are
thinking abt using CASE on the proc side...wanted to know if that would buy
us something vis a vis dyn sql
thx
"Tom Moreau" wrote:
> Why sort it at all? Can't ASP handle that? It would be more scalable
> than
> making SQL Server do all the sorting.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "para
a" <paraa@.discussions.microsoft.com> wrote in message> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> para
a>|||If you simply need to sort by a single column, then the ASP.NET DataGrid
could handle this. However, if your sort requirements are more complex
involving multiple columns or complex conditions, then it would probably
need to be done within the stored procedure. A conditional [order by] clause
using a case expression would be much preferable than dynamic SQL. Just as a
side note, you can also include case expressions in the [where] clause as
well for conditional row filtering, or even in the [group by] clause for
conditional grouping.
In the example below, there are 3 different sort options enabled using a
parameter called @.sort :
. . .
order by
case @.sort
when 0 then NULL
when 1 then company
when 2 then region
end,
case @.sort
when 0 then accountno
when 1 then lastname
when 2 then lastname
end
"para
a" <paraa@.discussions.microsoft.com> wrote in messagenews:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
> hi all,
> we are having an asp app that is fetching a big resultset to display in a
> list screen. The screen has 4 or 5 columns which have the click sort
> option
> (ie., on clicking the column hdr, the list needs to be sorted out).
> Currently, we are using a procedure which would dynamically construct the
> sort order and use exec(@.stmt) to send back the resultset. Coz of perf
> issues
> of late, we are thinking of removing dyn sql in the proc and recreating it
> using CASE/IF statements. Which option would be better in general?
> Currently,
> the dyn sql has high counts of CPU.
> would appreciate any help in this regards
> thx
> para
a|||thx a bunch JT and tom..thatz what i was arriving at when i mentioned CASEstmt v/s dyn sql...just wanted to make sure with the xperts
"JT" wrote:
> If you simply need to sort by a single column, then the ASP.NET DataGrid
> could handle this. However, if your sort requirements are more complex
> involving multiple columns or complex conditions, then it would probably
> need to be done within the stored procedure. A conditional [order by] clau
se
> using a case expression would be much preferable than dynamic SQL. Just as
a
> side note, you can also include case expressions in the [where] clause as
> well for conditional row filtering, or even in the [group by] clause for
> conditional grouping.
> In the example below, there are 3 different sort options enabled using a
> parameter called @.sort :
> .. . .
> order by
> case @.sort
> when 0 then NULL
> when 1 then company
> when 2 then region
> end,
> case @.sort
> when 0 then accountno
> when 1 then lastname
> when 2 then lastname
> end
> "para
a" <paraa@.discussions.microsoft.com> wrote in message> news:6D3D38EC-AAB8-4BF0-A74F-AAE898D73BBD@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)