Hello,
I want to do a dynamic order but with several criterias, my code look like:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price,stock
WHEN @.order = 'S' THEN stock,price
ELSE name,price
END
But it does not work, MSSQL doesn't like to have more than one value for
the order by, the code below works but that not what i want:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END
How can i do ?
ThanksOne method is with multiple CASE expressions in your ORDER BY clause:
SELECT name,price,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Hi
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
IF @.order="S"
SELECT name,price,stock FROM products ORDER BY stock,price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Untested:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
CASE WHEN @.order = 'P' THEN stock
WHEN @.order = 'S' THEN price
ELSE price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Uri's suggestion might be much better for performance|||Good one Uri and might perform better than the multiple Case.
You just forgot one:
IF @.order not in('S', 'P')
SELECT name,price,stock FROM products ORDER BY name,price
Or he can use Else.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ubd7mXHtFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> IF @.order="S"
> SELECT name,price,stock FROM products ORDER BY stock,price
>
> "Not4u" <Not4u@.chez.com> wrote in message
> news:43203431$0$11421$626a14ce@.news.free.fr...
>|||Thanks it's work great.
Not4u wrote:
> Hello,
> I want to do a dynamic order but with several criterias, my code look like
:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||AK wrote:
> Uri's suggestion might be much better for performance
>
Before discovering the dynamic order by (with case), i used the "IF then"
My select statment is much more complicated than the exemple in this
post and i have multiple order by conditions, the code managing is
easier with the "ORDER BY CASE".
What do you mean by much better performance ?
Thanks|||if at compile time there is an appropriate index, then SQL Server can
satisfy one ORDER BY clause without a sort. If you are specific:
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
the optimizer has a better chance to give you a better plan FOR THIS
PARTICULAR BRANCH of your IF statement.
If you are not specific:
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
the optimizer will utilize "one size fits all" approach, it will always
sort. SQL Server is very good at sorting, but still sorting is not
repeat not free...|||AK wrote:
> if at compile time there is an appropriate index, then SQL Server can
> satisfy one ORDER BY clause without a sort. If you are specific:
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> the optimizer has a better chance to give you a better plan FOR THIS
> PARTICULAR BRANCH of your IF statement.
> If you are not specific:
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END,
> the optimizer will utilize "one size fits all" approach, it will always
> sort. SQL Server is very good at sorting, but still sorting is not
> repeat not free...
>
My request is like this :
SELECT name,
(select min(price) from Price
INNER JOIN Reference ON
Price.id_reference = Reference.id_reference
WHERE Reference.id_product = Products.id_product
) as 'price'
,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
ENDsql
Showing posts with label likeselect. Show all posts
Showing posts with label likeselect. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Dynamic IN for query
I want to create a report which uses a query that has a dynamic "in" list.
something like
something like
select * from employee where employeeID in (@.empid)
when I send @.empid = 1,2,3 , it shows a compiler error ,
"syntax error converting the nvarchar value '1,2,3' to a column of data type int'.
any workarounds?Hi,
you can use a function to convert string type CSV into table of integer values (which work for IN). That is of course if you collect the values into string type CSV.
Using a CSV with an IN sub-select|||check booksonline about using sp_executesql..might give you an idea..
hth|||Solved it using the csv funtion, Thank you all.
Sunday, March 11, 2012
Dynamic generating a SQL statement that includes "INTO #temp"?
Hi there. I started with a simple stored procedure like
select col1, col2 into #temp from table1
select * from #temp
...Do something else here...
For some reason, I'd like to generate the first query dynamically.
declare @.sqlStmt varchar(200)
set @.sqlStmt = 'select col1, col2 into #temp from table1'
exec (@.sqlStmt)
select * from #temp
Unfortunately, it seems like #temp table is not visible anymore in the
"select * from #temp"
- Is it because exec creates a new session, and so #temp created in
the new session is not visible in the original session?
- Using global temp table ##temp may be too dangerous if two
concurrent users are running the same stored procedure. Right?
- Another workaround is to include the "select * from #temp" into the
@.sqlStmt. However, it is not a very elegant solution if the size of
stored procedure is big, but the portion that I'd like to generate
dynamically is small.
- Any other better solution?
ThanksInstead of using the SELECT...INTO statement, create a fixed temporary table
or a table variable and insert data into it. I can't really see any
legitimate reasons to use SELECT...INTO in your case (based on your post).
ML
http://milambda.blogspot.com/|||Thanks! That helps!|||>> Any other better solution? <<
First, avoid dynamic SQL. This says that you have no idea what you
wanted the procedure to do, so you have to "fake it" at the last
minute.
Next, from your narative it looks like a derived table or a VIEW is a
better answer, instead of mimicking the way we wrote scratch tapes in
1950's tape file systems.
CREATE VIEW Foobar (col1, col2)
AS
SELECT col1, col2 FROM Table1;
and then in the stored procedure, which will be compiled in the
database and not built dynamically. The VIEW will always be current.
CREATE PROCEDURE Woowoo (..)
BEGIN ..
SELECT col1, col2 FROM Foobar;
.Do something else here..
END;|||--CELKO-- (jcelko212@.earthlink.net) writes:
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
Rubbish. To see an example of how dynamic SQL can be used to solve a
common business problem, see http://www.sommarskog.se/dyn-search.html.
(And in difference to your books, this is a free resource. :-)
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|||To answer you questions:
When you exec (@.SQL), exec sp_executesql, or exec MyStoredProcedure, any
local temporary tables (#MyTable) will go out of scope and be automatically
deleted when the respective statement ends.
You can get around this by using a global temporary table (##MyTable) but as
you stated, a global temporary table is visable to all processes. Not only
does the same stored procedure executed concurrently have problems but ANY
T-SQL located in other stored procedure, dynamically submiteed by
applications, etc. that happen to create/use a global temporary table of the
same name (##MyTable) will have problems too.
SQL Server protects local temporary tables by concatenating a system
generated numeric suffix to the name. You could do the same. Seeing you're
dynamically creating the global table, you could concatenate the @.@.SPID
(process id) or something else. But then the remaining code would have to b
e
dynamic too and either exec (@.SQL) or exec master.dbo.sp_executesql (which
has more functionality)
Sounds like you need a permanent user table in your database (as suggested
by another forum member) and have a column that contains your @.@.SPID to
idenify the block of rows that belong to your process as opposed to other
concurrent executions.
Col1 - PK, int, identity, clustered (data is always added at the end of the
table data)
Col2 - SPID
Col3-n logical keys of your data
unique constraint on col2, col3, etc.
for rerunability, delete from xxx where spid = @.@.SPID so you have a fresh
workspace
if you need several workspaces, add another column after SPID that creates a
sub-block of data. you're sp can have as many sub-blocks as needed.
Just my two cents,
Joe
"domtam@.hotmail.com" wrote:
> Hi there. I started with a simple stored procedure like
> select col1, col2 into #temp from table1
> select * from #temp
> ....Do something else here...
> For some reason, I'd like to generate the first query dynamically.
> declare @.sqlStmt varchar(200)
> set @.sqlStmt = 'select col1, col2 into #temp from table1'
> exec (@.sqlStmt)
> select * from #temp
> Unfortunately, it seems like #temp table is not visible anymore in the
> "select * from #temp"
> - Is it because exec creates a new session, and so #temp created in
> the new session is not visible in the original session?
> - Using global temp table ##temp may be too dangerous if two
> concurrent users are running the same stored procedure. Right?
> - Another workaround is to include the "select * from #temp" into the
> @.sqlStmt. However, it is not a very elegant solution if the size of
> stored procedure is big, but the portion that I'd like to generate
> dynamically is small.
> - Any other better solution?
> Thanks
>|||> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
First, ignore Celko.
Because he sticks to the ANSI SQL standard he probably hasn't used these
Microsoft SQL Server features so he just blanks them and says they are
kludges etc...
Celko needs to get out and do some real development work and get some real
development experience.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134003996.366060.171490@.z14g2000cwz.googlegroups.com...
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
> Next, from your narative it looks like a derived table or a VIEW is a
> better answer, instead of mimicking the way we wrote scratch tapes in
> 1950's tape file systems.
> CREATE VIEW Foobar (col1, col2)
> AS
> SELECT col1, col2 FROM Table1;
> and then in the stored procedure, which will be compiled in the
> database and not built dynamically. The VIEW will always be current.
> CREATE PROCEDURE Woowoo (..)
> BEGIN ..
> SELECT col1, col2 FROM Foobar;
> ..Do something else here..
> END;
>
select col1, col2 into #temp from table1
select * from #temp
...Do something else here...
For some reason, I'd like to generate the first query dynamically.
declare @.sqlStmt varchar(200)
set @.sqlStmt = 'select col1, col2 into #temp from table1'
exec (@.sqlStmt)
select * from #temp
Unfortunately, it seems like #temp table is not visible anymore in the
"select * from #temp"
- Is it because exec creates a new session, and so #temp created in
the new session is not visible in the original session?
- Using global temp table ##temp may be too dangerous if two
concurrent users are running the same stored procedure. Right?
- Another workaround is to include the "select * from #temp" into the
@.sqlStmt. However, it is not a very elegant solution if the size of
stored procedure is big, but the portion that I'd like to generate
dynamically is small.
- Any other better solution?
ThanksInstead of using the SELECT...INTO statement, create a fixed temporary table
or a table variable and insert data into it. I can't really see any
legitimate reasons to use SELECT...INTO in your case (based on your post).
ML
http://milambda.blogspot.com/|||Thanks! That helps!|||>> Any other better solution? <<
First, avoid dynamic SQL. This says that you have no idea what you
wanted the procedure to do, so you have to "fake it" at the last
minute.
Next, from your narative it looks like a derived table or a VIEW is a
better answer, instead of mimicking the way we wrote scratch tapes in
1950's tape file systems.
CREATE VIEW Foobar (col1, col2)
AS
SELECT col1, col2 FROM Table1;
and then in the stored procedure, which will be compiled in the
database and not built dynamically. The VIEW will always be current.
CREATE PROCEDURE Woowoo (..)
BEGIN ..
SELECT col1, col2 FROM Foobar;
.Do something else here..
END;|||--CELKO-- (jcelko212@.earthlink.net) writes:
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
Rubbish. To see an example of how dynamic SQL can be used to solve a
common business problem, see http://www.sommarskog.se/dyn-search.html.
(And in difference to your books, this is a free resource. :-)
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|||To answer you questions:
When you exec (@.SQL), exec sp_executesql, or exec MyStoredProcedure, any
local temporary tables (#MyTable) will go out of scope and be automatically
deleted when the respective statement ends.
You can get around this by using a global temporary table (##MyTable) but as
you stated, a global temporary table is visable to all processes. Not only
does the same stored procedure executed concurrently have problems but ANY
T-SQL located in other stored procedure, dynamically submiteed by
applications, etc. that happen to create/use a global temporary table of the
same name (##MyTable) will have problems too.
SQL Server protects local temporary tables by concatenating a system
generated numeric suffix to the name. You could do the same. Seeing you're
dynamically creating the global table, you could concatenate the @.@.SPID
(process id) or something else. But then the remaining code would have to b
e
dynamic too and either exec (@.SQL) or exec master.dbo.sp_executesql (which
has more functionality)
Sounds like you need a permanent user table in your database (as suggested
by another forum member) and have a column that contains your @.@.SPID to
idenify the block of rows that belong to your process as opposed to other
concurrent executions.
Col1 - PK, int, identity, clustered (data is always added at the end of the
table data)
Col2 - SPID
Col3-n logical keys of your data
unique constraint on col2, col3, etc.
for rerunability, delete from xxx where spid = @.@.SPID so you have a fresh
workspace
if you need several workspaces, add another column after SPID that creates a
sub-block of data. you're sp can have as many sub-blocks as needed.
Just my two cents,
Joe
"domtam@.hotmail.com" wrote:
> Hi there. I started with a simple stored procedure like
> select col1, col2 into #temp from table1
> select * from #temp
> ....Do something else here...
> For some reason, I'd like to generate the first query dynamically.
> declare @.sqlStmt varchar(200)
> set @.sqlStmt = 'select col1, col2 into #temp from table1'
> exec (@.sqlStmt)
> select * from #temp
> Unfortunately, it seems like #temp table is not visible anymore in the
> "select * from #temp"
> - Is it because exec creates a new session, and so #temp created in
> the new session is not visible in the original session?
> - Using global temp table ##temp may be too dangerous if two
> concurrent users are running the same stored procedure. Right?
> - Another workaround is to include the "select * from #temp" into the
> @.sqlStmt. However, it is not a very elegant solution if the size of
> stored procedure is big, but the portion that I'd like to generate
> dynamically is small.
> - Any other better solution?
> Thanks
>|||> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
First, ignore Celko.
Because he sticks to the ANSI SQL standard he probably hasn't used these
Microsoft SQL Server features so he just blanks them and says they are
kludges etc...
Celko needs to get out and do some real development work and get some real
development experience.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134003996.366060.171490@.z14g2000cwz.googlegroups.com...
> First, avoid dynamic SQL. This says that you have no idea what you
> wanted the procedure to do, so you have to "fake it" at the last
> minute.
> Next, from your narative it looks like a derived table or a VIEW is a
> better answer, instead of mimicking the way we wrote scratch tapes in
> 1950's tape file systems.
> CREATE VIEW Foobar (col1, col2)
> AS
> SELECT col1, col2 FROM Table1;
> and then in the stored procedure, which will be compiled in the
> database and not built dynamically. The VIEW will always be current.
> CREATE PROCEDURE Woowoo (..)
> BEGIN ..
> SELECT col1, col2 FROM Foobar;
> ..Do something else here..
> END;
>
Friday, February 17, 2012
dynamic column name (using where)
Hello All,
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @.Selection like @.SelectionChar+'%'
This dos not work.
does someone have a solution :(
tks stroekDynamic SQL:
http://www.sommarskog.se/dynamic_sql.html|||I wanted a read but that website is blocked :mad:
Your request was denied because of its content categorization: "Personal Web Sites;Society and Lifestyles"|||yikes, george, yikes
then again, you do have a tendency to mess about when you should be working, and if others in your company are the same, then no wonder they've put controls on you...
:)|||then again, you do have a tendency to mess about when you should be working
:shocked: How very dare you!|||Sorry but I didn't find any simple solution for my problem.
Is there no one that can give me a simple solution?
Gr stroek|||DECLARE @.s varchar(150)
SET @.s = 'Select * From SM_Clients where '
SET @.s = @.s + @.Selection
SET @.s = @.s + ' like '''
SET @.s = @.s + @.SelectionChar
SET @.s = @.s + '%'''
EXEC (@.s)
?|||Yeah, my work wouldn't consider time spent here to be "messing about";)|||Mine either - an invaluable learning resource.
You only get out what you put in ;)|||i would've said skiving, but the rest of you lot would've thought i'd gone barmy|||I'm ahead of schedule on my projects and I consider this a constructive use of my time :)
I think I've learnt more from these forums than I have from all the courses I have been on combined.
Furthermore; learning by doing is also a much more effective way of learning imo.|||Unfortunately, I was being sarcastic. Spending a few hours of my work day giving support to others outside of the company (as I've done here in the past) would be severely frowned upon.
I'm trying to us a parameter as column name like:
Select * From SM_Clients
where @.Selection like @.SelectionChar+'%'
This dos not work.
does someone have a solution :(
tks stroekDynamic SQL:
http://www.sommarskog.se/dynamic_sql.html|||I wanted a read but that website is blocked :mad:
Your request was denied because of its content categorization: "Personal Web Sites;Society and Lifestyles"|||yikes, george, yikes
then again, you do have a tendency to mess about when you should be working, and if others in your company are the same, then no wonder they've put controls on you...
:)|||then again, you do have a tendency to mess about when you should be working
:shocked: How very dare you!|||Sorry but I didn't find any simple solution for my problem.
Is there no one that can give me a simple solution?
Gr stroek|||DECLARE @.s varchar(150)
SET @.s = 'Select * From SM_Clients where '
SET @.s = @.s + @.Selection
SET @.s = @.s + ' like '''
SET @.s = @.s + @.SelectionChar
SET @.s = @.s + '%'''
EXEC (@.s)
?|||Yeah, my work wouldn't consider time spent here to be "messing about";)|||Mine either - an invaluable learning resource.
You only get out what you put in ;)|||i would've said skiving, but the rest of you lot would've thought i'd gone barmy|||I'm ahead of schedule on my projects and I consider this a constructive use of my time :)
I think I've learnt more from these forums than I have from all the courses I have been on combined.
Furthermore; learning by doing is also a much more effective way of learning imo.|||Unfortunately, I was being sarcastic. Spending a few hours of my work day giving support to others outside of the company (as I've done here in the past) would be severely frowned upon.
Subscribe to:
Posts (Atom)