SQL Statement and Variables
-
Hi,
I have to solve a big problem:
my question is:
I would like to store a result of a query into a variable.....
just to be clear:
a variable: MyMax
and a select statement that retrieve the max value in a column:
e.g.
a table (fruits):
Fields: number fruits
Row1: 10 bananas
row2: 5 peras
select max(number) from fruits
the SQL response will be: 10.
now I would like to store 5 into the variable: MyMax.
What is the TSQL code.....?
Thank in advance to everybody
-
well this would do it in this case.. but what are you trying to achieve exactly?
set @MyMin = ISNULL((Select min(Number) from dbo.Fruits), 0)
-
You need to use SELECT not SET!
You can even assign more than one value to a variable at once
select @a=min(Number), @b-2=max(Number) from dbo.Fruits
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nice point... but I'm still not totally sure of what he wants to do.
-
............I forgot to add that my problem is in case of SQL Statement with parameter.....
the name of column change, so I declare a varable named: @Column
eg
SELECT @MyMin = 'Max (' + @Column + ') from dbo.Fruits'
If I print,the result is the string and not the result....
print @MyMin the resul is:
Max (Number) from dbo.Fruits
???????????
I need it because I have to store some variables into a tables and re-used in other statment.....
Thank......for more suggestion
-
You have to use dynamic SQL. Note: If this query is part of stored procedure and if you have exec permission on stored procedure and no select permission on table you have to give select permission for the table.
declare @SQL nvarchar(1000)
SET @SQL = 'SELECT GETDATE()' -- build your query here
EXECUTE sp_executesql @SQL
--OR
EXEC (@SQL)
Regards,
gova -
Unless this is for administration work I would suggest you find another way to solve your problem because the use of dynamic sql is strongly suggested againsts in a production environement.
Your might want to read up on this : The Curse and Blessings of Dynamic SQL
-
Dynamic SQL
USE PUBS
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount bigint
DECLARE @table nvarchar(255)
SET @table = 'authors'
SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table
EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT
IF @rowcount > 0
BEGIN
SELECT @rowcount AS Anzahl
END
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Nice, a triple synchronised post. It's a first for me.
Hey Frank, since when are you approving the use a dynamic sql without questionning the design or the task at hand???
-
Remi I use dynamic sql for sorting something like this
CREATE PROCEDURE GetDetails
(
@sort VARCHAR(25)
)
AS
SET NOCOUNT ON
DECLARE @SQL = 'SELECT * FROM TableName ORDER BY ' + @sort
EXEC (@SQL)
GO
Since my need is to sort as per the users' click on a column heading in ASP.NET application. If this is a bad design is there any other way to it. May be this should be a different thread.
Thanks
Regards,
gova -
Depends on how many columns you have to sort on... if there's only 2-3 possibilities and that the recordset is small you can always do it on the server. But if it's big you're better of sorting the whole thing on the client.
ex :
CREATE PROCEDURE GetDetails
(
@sort VARCHAR(25) = 'id'
)
AS
SET NOCOUNT ON
if @sort not in ('id', 'col1', 'col2')
--exit with error
Select col1, col2, id from dbo.YourTable
order by
case when @sort = 'id' then id else null end
, case when @sort = 'col1' then col1 else null end
, case when @sort = 'col2' then col2 else null end
This solution works very well. However if you have 15 possible sorts, then the server will have to sort by null, null, null..., someCol, null, null.... which is a lot of overhead for nothing.
That's why I think you're better off sending the results without a order by and let the application do a recordset.order = "Selected col"
But as always, test, test, test.
-
Btw I would strongly urge you to read these documents... it's an hour very well spent (especially the first one for this thread) :
Dynamic Search Conditions in T-SQL
-
Thanks Remi.
I have to use SqlDataReader since it is faster. This does not have a sort method as far as I know. So I had to use back end sorting. It is always a single column sort and we have 7 to 18 columns for different pages.
It is not simple select * from it has lot of columns sub stored procedure cols. I keep the data in temp table and sort it at last. Thanks for the link.
I think I am stuck with what I did.
Regards,
gova -
Maybe not, but if you think you are, then I feel I should tell you this : Make sure that the user has absolutely NO WAY of accessing the column names that he wishes to sort on. If he can't access that then he won't be able to start an injection attack on the server (on that page that is).
-
I agree may be not if I use 15 lines like this
SELECT col1, col2, col3
FROM #tbl
ORDER BY CASE @SortCol
WHEN 'col2' THEN CASE @SortYpe WHEN 'DESC' THEN col1 DESC ELSE col1 END
WHEN 'col2' THEN CASE @SortYpe WHEN 'DESC' THEN col2 DESC ELSE col1 END
WHEN 'col3' THEN CASE @SortYpe WHEN 'DESC' THEN col2 DESC ELSE col1 END
END
But fortunately I don't have security issues since I get all data into temptables then manipulate them for the business rules and sort the temp table using dynamic SQL.
My approach would be lot different from now on. Thanks for the good info.
Regards,
gova
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply