Easy SQL Problem

I have a simple table that stores
name score
-- --
jim 343
bob 322
jane 122
I need a SQL query that will return the name and score of the person who has
the lowest score:
eg. jane 122
SELECT name, min(score) as score FROM tester group by name;
What is the best query for the job?
http://www.plus2net.com/sql_tutorial/sql_min.php
this tutorial seems to be wrong. It doesn't work when i run it.Mark Thomson wrote:
> I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
> I need a SQL query that will return the name and score of the person who h
as
> the lowest score:
> eg. jane 122
I would write something like this:
SELECT name, score FROM tester WHERE score = (SELECT MIN(score) FROM tester)|||Try,
select *
from tester
where score = (select min(score) from tester)
-- or
select top 1 with ties *
from tester
order by score desc
AMB
"Mark Thomson" wrote:

> I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
>
> I need a SQL query that will return the name and score of the person who h
as
> the lowest score:
> eg. jane 122
>
> SELECT name, min(score) as score FROM tester group by name;
> What is the best query for the job?
> http://www.plus2net.com/sql_tutorial/sql_min.php
> this tutorial seems to be wrong. It doesn't work when i run it.
>
>|||Many Thanks,
I've never heard of the ties keyword before.
Cheers.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@.microsoft.com...
> Try,
> select *
> from tester
> where score = (select min(score) from tester)
> -- or
> select top 1 with ties *
> from tester
> order by score desc
>
> AMB
> "Mark Thomson" wrote:
>|||select top 1 name, score from tableName order by score
If course, if 2 people have the same score, this will return only one of
them.
"Mark Thomson" <@.@.@.> wrote in message
news:OacoT6sjFHA.476@.TK2MSFTNGP14.phx.gbl...
>I have a simple table that stores
> name score
> -- --
> jim 343
> bob 322
> jane 122
>
> I need a SQL query that will return the name and score of the person who
> has the lowest score:
> eg. jane 122
>
> SELECT name, min(score) as score FROM tester group by name;
> What is the best query for the job?
> http://www.plus2net.com/sql_tutorial/sql_min.php
> this tutorial seems to be wrong. It doesn't work when i run it.
>|||...and for that you to work as you want, remove 'desc'.
"Mark Thomson" <@.@.@.> wrote in message
news:OUd1QFtjFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Many Thanks,
> I've never heard of the ties keyword before.
> Cheers.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@.microsoft.com...
>|||You are right.
AMB
"Raymond D'Anjou" wrote:

> ...and for that you to work as you want, remove 'desc'.
> "Mark Thomson" <@.@.@.> wrote in message
> news:OUd1QFtjFHA.1204@.TK2MSFTNGP12.phx.gbl...
>
>

Easy query problem

Hello Experts-
I would like a sproc to return a 1 row select with results based on the
results of what it has found. For example say the following table were
created by the sp:
ID Name Dept
23 A 4
38 B 4
117 C 4
if the sproc could tell me which of these columns contained unique values
that would be great:
ID Name Dept
null null 4
In other words if all values in a column are the same, return that value,
otherwise return null.Select (Select Case When Count(*) = 1
Then Min(ID) Else Null End
From Table T
Group By ID) As ID,
(Select Case When Count(*) = 1
Then Min(Name) Else Null End
From Table T
Group By Name) As Name,
(Select Case When Count(*) = 1
Then Min(Dept) Else Null End
From Table T
Group By Dept) As Dept
"Coffee guy" wrote:

> Hello Experts-
> I would like a sproc to return a 1 row select with results based on the
> results of what it has found. For example say the following table were
> created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique values
> that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that value,
> otherwise return null.|||Sorry - messed that u.. Here's the right one...
Select (Select Case When Count(Distinct ID) = 1
Then Min(ID) Else Null End
From Table T) As ID,
(Select Case When Count(Distinct Name) = 1
Then Min(Name) Else Null End
From Table T) As Name,
(Select Case When Count(Distinct Dept) = 1
Then Min(Dept) Else Null End
From Table T) As Dept
"CBretana" wrote:
> Select (Select Case When Count(*) = 1
> Then Min(ID) Else Null End
> From Table T
> Group By ID) As ID,
> (Select Case When Count(*) = 1
> Then Min(Name) Else Null End
> From Table T
> Group By Name) As Name,
> (Select Case When Count(*) = 1
> Then Min(Dept) Else Null End
> From Table T
> Group By Dept) As Dept
>
> "Coffee guy" wrote:
>|||Coffee guy wrote:
> Hello Experts-
> I would like a sproc to return a 1 row select with results based on
> the results of what it has found. For example say the following
> table were created by the sp:
> ID Name Dept
> 23 A 4
> 38 B 4
> 117 C 4
> if the sproc could tell me which of these columns contained unique
> values that would be great:
> ID Name Dept
> null null 4
> In other words if all values in a column are the same, return that
> value, otherwise return null.
<snort>
What makes you think this query is "Easy"?
Try this:
CREATE TABLE #temp (
ID int,
Name varchar(10),
Dept int)
insert into #temp
select 23,'A',4
union all select 38,'B',4
union all select 117,'C',4
SELECT
(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
ID
END FROM #temp) ID
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
Name
END FROM #temp) Name
,(SELECT TOP 1 CASE WHEN
(SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
Dept
END FROM #temp) Dept
drop table #temp
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks to both, harder than I thought ;)
"Bob Barrows [MVP]" wrote:

> Coffee guy wrote:
> <snort>
> What makes you think this query is "Easy"?
> Try this:
> CREATE TABLE #temp (
> ID int,
> Name varchar(10),
> Dept int)
> insert into #temp
> select 23,'A',4
> union all select 38,'B',4
> union all select 117,'C',4
> SELECT
> (SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT ID) FROM #temp)=1 THEN
> ID
> END FROM #temp) ID
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Name) FROM #temp)=1 THEN
> Name
> END FROM #temp) Name
> ,(SELECT TOP 1 CASE WHEN
> (SELECT COUNT(DISTINCT Dept) FROM #temp)=1 THEN
> Dept
> END FROM #temp) Dept
> drop table #temp
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||I think this is a bit simpler than what's been posted so far. Assuming
no NULLs in any of the columns,
select
case when min(ID) = max(ID) then min(ID) else null end as ID,
case when min(Name) = max(Name) then min(Name) else null end as Name,
case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
from #temp
Steve Kass
Drew University
Coffee guy wrote:
>Thanks to both, harder than I thought ;)
>"Bob Barrows [MVP]" wrote:
>
>|||Duh! I definitely did not give this enough thought.
Thanks,
Bob
Steve Kass wrote:
> I think this is a bit simpler than what's been posted so far. Assuming no
> NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Steve,
Yes, Elegant !
"Steve Kass" wrote:

> I think this is a bit simpler than what's been posted so far. Assuming
> no NULLs in any of the columns,
> select
> case when min(ID) = max(ID) then min(ID) else null end as ID,
> case when min(Name) = max(Name) then min(Name) else null end as Name,
> case when min(Dept) = max(Dept) then min(Dept) else null end as Dept
> from #temp
>
> Steve Kass
> Drew University
> Coffee guy wrote:
>
>|||And if your fingers are tired, these are a tiny bit shorter,
but they're basically the same thing:
select
nullif(min(ID),nullif(min(ID), max(ID))) as ID,
nullif(min(Name),nullif(min(Name), max(Name))) as Name,
nullif(min(Dept),nullif(min(Dept), max(Dept))) as Dept
from #temp
select
case min(ID) when max(ID) then min(ID) end as ID,
case min(Name) when max(Name) then min(Name) end as Name,
case min(Dept) when max(Dept) then min(Dept) end as Dept
from #temp
SK
CBretana wrote:
>Steve,
>Yes, Elegant !
>
>"Steve Kass" wrote:
>
>sql

Easy Determine Free Disk Space Another Disk Drive

What is easy way to return a value for the free disk space on another server
?
Please help me with this problem.
Thank You,Why would you want SQL Server to do this? I would use VBScript's
FileSystemObject or WMI and populate SQL Server with the information, maybe
on five minute intervals. When you run the query from SQL Server, the data
is up to five minutes old, but the user doesn't wait and there are no file
share / server access permissions issues.
A
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:4DB5CF71-938E-4E43-ACE2-73392DB4590B@.microsoft.com...
> What is easy way to return a value for the free disk space on another
> server?
> Please help me with this problem.
> Thank You,
>
>
>sql

Dynamics GP Evaluation copy installation

Hi,

I am trying to install an evaluation copy of Dynamics GP on my laptop but it keeps giving me the following error:

msde failed to install. Return code 1603

Any idea what I need to do. I am not a techy so you'll have to be gentle with me.

Thanks

Raju

download

msde and install it before hand

http://www.microsoft.com/sql/prodinfo/previousversions/msde/download.mspx

Dynamics GP Evaluation copy installation

Hi,

I am trying to install an evaluation copy of Dynamics GP on my laptop but it keeps giving me the following error:

msde failed to install. Return code 1603

Any idea what I need to do. I am not a techy so you'll have to be gentle with me.

Thanks

Raju

download

msde and install it before hand

http://www.microsoft.com/sql/prodinfo/previousversions/msde/download.mspx

Dynamically selecting a row from a table

I have a function which i want to return where an id in a table exists somethign like
if exists (select id from @.tablename where id = @.id)
You cannot use dynamic SQL within TSQL UDFs. You should also use dynamic SQL with care. It has security and performance implications if used improperly. Why would you want to write a UDF that does if exists() check on any table? Isn't it easy just to write the query wherever you need it because that will be optimized better. You could consider writing this as a SP instead. And for the dynamic SQL to work you need to grant SELECT permissions for all users on the tables that you would check.

dynamically return rows 1-10, 11-20, 20-30 using select statement

Hi,
i would like to know if it is possible for me to get back the rows i am
interested in rather than all the records matching the query.
Right now my query returns first 20 items, 30 items or the first 500 items.
But i am having a problem with timeout when i query all 5000 items.
Can i get back only 10 items at a time in the order 1-10, 11-20, 20-30, etc.
Thanks
-SreeEssentially you are talking about paging your results into manageable
chunks'
Check out this article:
http://www.fawcette.com/dotnetmag/2...pf
.asp.
It is an implementation in .NET, but pay attention to the SQL portion of it.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"sree" <ss@.aa.com> wrote in message
news:r9b3c.89293$PR3.1286538@.attbi_s03...
> Hi,
> i would like to know if it is possible for me to get back the rows i am
> interested in rather than all the records matching the query.
> Right now my query returns first 20 items, 30 items or the first 500
items.
> But i am having a problem with timeout when i query all 5000 items.
> Can i get back only 10 items at a time in the order 1-10, 11-20, 20-30,
etc.
> Thanks
> -Sree
>

Dynamic WHERE statement

If I pass a variable to @.Cost_Center that is 'SMS', 'SMP' OR 'ALL'
'ALL' will return records that have either 'SMS' or 'SMP' in them.
Now I am using an if statement that has the entire SQL statement in the body
of the
conditional.
I tried this
Where
IF (@.Cost_Center = 'All')
Begin
code...
and i.cost_center in ('SMS','SMP')
End
ELSE
Begin
code...
and i.cost_center = @.Cost_Center
End
Thanks
JimYou might want to check out the following article for some ideas:
http://www.sommarskog.se/dyn-search.html
Anith|||Thank you
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23glqcpZBFHA.4072@.TK2MSFTNGP10.phx.gbl...
> You might want to check out the following article for some ideas:
> http://www.sommarskog.se/dyn-search.html
> --
> Anith
>|||SELECT ...
FROM Foobar
WHERE cost_center = @.my_cost_center
OR cost_center
IN (CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMP' ELSE '' END,
CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMS' ELSE '' END));

Dynamic WHERE clause

Hi
I have a WHERE clause, based on Params, passed in from a report, however, I
cannot get it to work properly.
I have to return two product codes, when a certain Types are passed in,
however, when it's anything else, I am only to return one product type.
Here's a sample of what I am trying to do:
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'CC', 'PO'
ELSE
'CC'
END)
Kind Regards
RickyTry
WHERE [Product Code] = 'CC' OR ( [Product Code] = 'PO' AND @.Type IN ('MD',
'BI') )
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:u$sWO8fjGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi
> I have a WHERE clause, based on Params, passed in from a report, however,
> I
> cannot get it to work properly.
> I have to return two product codes, when a certain Types are passed in,
> however, when it's anything else, I am only to return one product type.
> Here's a sample of what I am trying to do:
>
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'CC', 'PO'
> ELSE
> 'CC'
> END)
>
> Kind Regards
> Ricky
>|||No, you can't do this. You can try something like this though...
WHERE
[Product Code] IN
(CASE
WHEN @.Type IN ('MD', 'BI') THEN
'PO'
ELSE
'CC'
END, 'CC')
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi chaps
Thank you both for the suggestion, couldn't work this out, been stuck for a
few hours. I thought it would be a simple thing to do...anyway, thanks
again.
Kind Regards
Ricky
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:06AFD45A-62AB-49EE-AFED-F6095D3C0C2C@.microsoft.com...
> No, you can't do this. You can try something like this though...
> WHERE
> [Product Code] IN
> (CASE
> WHEN @.Type IN ('MD', 'BI') THEN
> 'PO'
> ELSE
> 'CC'
> END, 'CC')
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>

Dynamic WHERE & Dates

My dynamic sql correctly returns the top 10 dates with records if @.timeID
equals 1. I need the @.timeID equals 2 part to return records between
@.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
keep my example simple).
In the below section where @.timeID = 2, I'm getting a syntax error. Can
anyone help with the part below that tests IF @.timeID = 2 ? I just need some
quote help with that part of the where clause.
This runs on Northwind.
CODE ****************************************
***
declare @.SQL varchar(1000), @.typeID int, @.timeID int
declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
set @.typeID = 1
SET @.timeID = 1
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
set @.dtEndDate = @.dtMaxDate
SET @.SQL = 'SELECT Orders.OrderDate, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
WHERE '
IF @.timeID = 1 /* Return last 10 days */
SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
@.dtMaxDate, 112) + ''''
IF @.timeID = 2 /* should Return records between start and end date */
PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
@.dtStartDate + ' AND '
+ CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
EXEC(@.SQL)Basically, I think your dynamic SQL should look like this when done:
=====
'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
@.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
CONVERT(CHAR(11), @.dtEndDate) + ''''
=====
From your example below, it looks like the CONVERT portion needs to be
within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
to the query with their values. Also, what date format are you expecting by
converting into CHAR(8)?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott
I don't understand why would want to use dynamic sql but this is a fixed
code
-- PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' +
convert(char(8),@.dtStartDate ,112)+ ''' AND '
+ 'CONVERT (char(8), Orders.OrderDate, 112) <='''
+convert(char(8),@.dtEndDate,112)+''''
BTW if IF @.timeID = 1, I got this error
Line 28: Incorrect syntax near '>'.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID
> WHERE '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott (sbailey@.mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
Weren't you the guy with time constraints? Why then are you wasting the
time with building complete SQL strings, when using sp_executesql is so
much easier?
And why do you insist on not printing out the generated SQL? You could
at least include the generated SQL in your posts, so that it's easier for
people to spot the syntax errors.

> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
You have string concatanated with string, and then there is a comparison
operator in the middle of all this. That will obviously give a syntax
error.
And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
an WHERE clause. When you put a column in an expression, this prevents
any index from that column from being used. Thus, this can have a serious
impact on performance.
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|||1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates
from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that
date format to avoid a performance hit?
2. As far as sp_executesql, I wanted to use that method, but the syntax is
more confusing that my method. If I good get some good simple examples, I'd
go that way.
3. My reason for going the way I am is if I hard coded every sql statement
with the matrix of variables I'm sending this SPROC, it would be 1,000 lines
or more. Normally they don't get this out of hand, but this one is for a
graph with lots of options.
4. I'll do better at printing the SQL from now on.
Thanks for any pointers on my date performance issue #1 above.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718610C29F6DYazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Weren't you the guy with time constraints? Why then are you wasting the
> time with building complete SQL strings, when using sp_executesql is so
> much easier?
> And why do you insist on not printing out the generated SQL? You could
> at least include the generated SQL in your posts, so that it's easier for
> people to spot the syntax errors.
>
> You have string concatanated with string, and then there is a comparison
> operator in the middle of all this. That will obviously give a syntax
> error.
> And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
> an WHERE clause. When you put a column in an expression, this prevents
> any index from that column from being used. Thus, this can have a serious
> impact on performance.
> --
> 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|||Thanks for the efforts.
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:enDhqcL8FHA.1140@.tk2msftngp13.phx.gbl...
> Basically, I think your dynamic SQL should look like this when done:
> =====
> 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
> @.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
> CONVERT(CHAR(11), @.dtEndDate) + ''''
> =====
> From your example below, it looks like the CONVERT portion needs to be
> within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
> to the query with their values. Also, what date format are you expecting
> by converting into CHAR(8)?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>|||Scott (sbailey@.mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing
> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to
> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can
> I use on that date format to avoid a performance hit?
No, you don't have that pass dates that way from ASP. Even more you
shouldn't. That is not even a date as far as I am concerned.
The way to pass data from a client is to use parameters, and let the
the client interpret the date according to the regional setting. The is
then passed to SQL Server as a binary value, as dates are binary values
in SQL Server. (As they are in Windows, by the way.)
I don't do ASP, but in plain Visual Basic it looks something like:
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "dbo.some_sp"
cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
And obviously you are doing something like that already, as the date
variables in your example were declared as datetime, not as character.

> 2. As far as sp_executesql, I wanted to use that method, but the syntax
> is more confusing that my method. If I good get some good simple
> examples, I'd go that way.
SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
@.params = N'@.par1 int, @.par2 datetime'
EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
How is this more confusing that trying to get quotes and date formats
correct, something you appear to have big problems with.
As for writing dynamic searches, have you looked at my web site where
I have a longer article on the topic, with both static and dynamic methods?
http://www.sommarskog.se/dyn-search.html. There are also tips for people
that use EXEC().
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|||The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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|||on your site in search_orders_1, for example, how does SQL know what
@.xorderid is when you only declare @.orderid at the beginning of the SPROC? I
see @.xorderid listed in the @.paramlist after you use it as a WHERE criteria,
but how does SQL know what @.xorderid is?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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|||> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
It mean it is Unicode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:ut24UCY8FHA.3952@.TK2MSFTNGP12.phx.g
bl...
> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9718E396131E5Yazorman@.127.0.0.1...
>

Dynamic Stored Procedures uses vars only

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

Writing dynamic T-SQL doesn't strike me as being relevant to SSIS so I'm a little confused. Perhaps you could elaborate.

By the way, best practice stipulates that you shouldn't name your sprocs "sp_*".

-Jamie