Using the TOP 1 with a variable, how?
-
If I declare a variable datetime I can set it with something like:
SELECT@TestDate = startdate from table1
Now I wish to set it with something like:
SELECT@TestDate = TOP 1 startdate from table1
This does not work. I've been experimenting with MIN/MAX, but no solution so far. Any hints?
Greetz,
Hans Brouwer -
SELECT TOP 1 @TestDate = startdate from table1Note that you would normally have an ORDER BY clause in this scenario
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Right....never would've thought of that!
Tnx.
Greetz,
Hans Brouwer -
You say you've been experimenting with Min/Max as well?
Here are two equivalent expressions
declare @MinDate datetime
Select @MinDate = Min([date])
from dimtime
Select top 1 @MinDate = [date]
from dimtime
order by [date]
-
Hello I have a question with that TOP scenerio. If i have a store procedure and i want to pass the Top 3 as a paramter so when i execute the sp i could use any value. In my sp i have the top 3 hard code in there but not sure how to pass it as paramter. In SQL server 05
USE [ASIDB]
GO
/****** Object: StoredProcedure [dbo].[sp_moveBackupTables] Script Date: 12/27/2007 16:53:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[sp_moveBackupTables]
@serverName varchar(50) --Parameter
AS
BEGIN
declare @tableName varchar(50) --Variable
--Declare Cursor
DECLARE backup_Cursor CURSOR FOR
select name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U'
and name not in(select top 3 name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)
OPEN backup_Cursor
--Move to initial record in the cursor and set variable(s) to result values
FETCH NEXT FROM backup_Cursor
INTO @tableName
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--dynamically build create table
Declare @sql varchar(2000)
Set @sql = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(
[Id] [numeric](18, 0) NOT NULL,
[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Field2] [numeric](18, 0) NOT NULL,
[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
EXEC(@SQL)
--Insert values into new dynamically created table
Declare @backupTableRecords varchar(1000)
Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName
exec(@backupTableRecords)
--Drop old table
Declare @dropTable varchar(200)
set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName
exec(@dropTable)
--Move to the next record in the Cursor and set variable(s) value(s)
FETCH NEXT FROM backup_Cursor INTO @tableName
END
CLOSE backup_Cursor
DEALLOCATE backup_Cursor
END
-
In 2005 only
SELECT TOP (@Variable) Columns FROM Table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
The REAL question would be, WHY are you using a cursor for this? You should be able to do this in a setbased fashion...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just started using sql server 05 and that the only way i know. I not familar with a setbased... what is that
-
I'm going to reply to your PM here. A private message won't help anyone else, and also means that I'm the only one who can reply. There are lots of other people around here willing to help.
Leaving the set-based issue to Jeff (as it's almost midnight here), you asked about the exact syntax for using top with a variable
You need to nave the variable declared, or have it as a parameter passed to the stored proc.
As a variable...
DECLARE @NumberOfRowsIWant INT
SET @NumberOfRowsIWant = 42
SELECT TOP (@NumberOfRowsIWant) FROM sys.objects ORDER BY create_date
As a parameter
CREATE PROCEDURE TestingVariableTop
@NumberOfRowsIWant INT
AS
SELECT TOP (@NumberOfRowsIWant) FROM sys.objects
GO
EXEC TestingVariableTop 42
Make sense now?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You could always fall back to the old
SET ROWCOUNT @Variable
SELECT ....etc
SET ROWCOUNT 0
In terms of set based processing it simply means that you look at the data as a whole rather than on a row-by-row basis.
for example, if you wanted to update all your prices by 10% you would run a single update statement in preference to looping through records one by one in a cursor.
There is usually a set based way of performing most tasks.
-
Okay I really appreicate the help.
Thanks and have a good new year
-
Heh... Happy New Year to you to... you should make a good resolution... repeat after me, please... "I will NOT use cursors for simple set based problems." 😛
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
USE AdventureWorks
DECLARE @Fir INT
SELECT Top 1 @Fir = EmployeeID FROM HumanResources.Employee ORDER BY EmployeeID Desc
SELECT @Fir
-
h2sut (12/28/2007)
I just started using sql server 05 and that the only way i know. I not familar with a setbased... what is that"Set-based" is the whole idea behind databases... if you've never used one before, you're probably used to the old RBAR method of read a row, process a row, write a row method. When you work one row at a time in a database, you just slow it down... databases are best at processing whole columns... not rows. When you process whole columns, you're processing a whole "set of rows"... and that's set-based.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply