Thursday, March 29, 2012
Cursor variable not declared issue
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***
CURSOR trouble (:
What i am trying to do is concatenate variable "@.Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for the @.Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @.Where = ''
if IsNull(@.Input1,NULL) <> NULL
Set @.Where = @.Where + " AND studentid='" + @.input1 +"'"
if isnull(@.Input2,NULL) <> NULL
Set @.Where = @.Where + " AND teacherid =' " + @.Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
*
FROM
school
WHERE
school ='ABC' + @.where
=============================================
It gives me all the Records inside the SCHOOL='ABC' ...
Please check my SQL Above and Could somebody tell me , how can I attach the VARIABLE with CURSOR sql statement ?
Please advice me..(:
Thanks !Well, looks like another day is over, so it is time to repeat the (same old) basic thing about SQL again.
You can NOT just put up part of a SQL Statement into a variable and then think the SQL compiler acutlly magically produces dynamic SQL out of it.
That simple.
In your example:
::DECLARE curs1 CURSOR SCROLL
::FOR SELECT
::*
::FROM
::school
::WHERE
::school ='ABC' + @.where
@.where will NOT be appened to the SQL statement. It will be part of the right part of the comparison.
So, if @.where consists of (example)
::AND studentid='22'
it will check whether schoon equals "ABC AND studentid='22'".
That simple.
If you want to execute a dynamic SQL thing, then do so. There are means for this. But otherwise, variables are variables, not some "post compiler magic thing that happens when you dance around the computer at night singing magic chants".
Interesting enough you are not alone. Lots of people think like this - as I said in the beginning, some typical (beginner) error like this pops up every second day. Most, though, stumble opver it before they get into using / abusing cursors.|||
SELECT *>>using / abusing cursors
FROM
school
WHERE
school ='ABC'
AND
( @.Input1 IS NULL OR @.Input1 = StudentID )
AND
( @.Input2 IS NULL OR @.Input2 = teacherID )
So true.... many developers often try to solve T-SQL problems by using cursors, similar to the way they might program in other environments—working with one record at a time in sequential order. But this approach doesn't exploit the power of SQL Servers "engine", which is optimized for set-based processing. Cursors usually require considerably more code than their equivalent set-based solutions and are much less efficient.
If a cursor is the first thing that you think of when you face a T-SQL problem, you have some unlearning to do—you need to start thinking "set-based.
http://www.windowsitpro.com/SQLServer/Article/ArticleID/22431/22431.html|||Christmas is supposed to be about giving so here we go. Some good advice already provided (though not entirely correct). First of all cursor or no cursor and trying to concatenate a where clause or not why are you doing this: if IsNull(@.Input1,NULL) <> NULL ?
For one thing when ever evaluating NULL always use: IF @.Input1 IS NULL or in your specific example IF @.Input1 IS NOT NULL
As for using the ISNULL function I suggest that you use this only when you suspect that the value is null or perhaps in your case an empty string in which case thew correct syntax would be: IF ISNULL(@.Input1, '') <> ''
Also, regardless of if you really need a cursor or not from the example you have provided you can generate the select as follows:
SELECT * FROM school
WHERE school = 'ABC' AND studentid LIKE ISNULL(@.Input1, '') + '%' AND teacherid LIKE ISNULL(@.Input2, '') + '%' ...
Now if you still really feel that you have to concatenate a where clause it can in fact be done but it is ugly! It works like this (but is only viable for returning a result set, in other words you cannot use it for a CURSOR!):
EXEC('SELECT * FROM school WHERE ' + @.Where)
Last but not least, as one of the other respondents suggested CURSOR's should be avoided at all costs and as such there are a number of other ways to apply updates to a group of records with some pretty flexible conditional logic.
Step 1: select the target records into a temporary table i.e.
SELECT [primary_key_field] INTO #target
FROM school WHERE (school = 'ABC')
AND (studentid LIKE ISNULL(@.Input1, '') + '%')
AND (teacherid LIKE ISNULL(@.Input2, '') + '%') ...
Now that you have the keyset available you can do a simple update to all the records in the resultset as follows:
UDPATE school
SET [whatever_field] = [whatever_value]
FROM school, #target
WHERE school.[primary_key_field] = #target.[primary_key_field]
For more sophisitcated updates you can use a flavour of the CASE in sql such as
SET [whatever_field] = CASE WHEN [condition1] = True THEN
value1
WHEN [condition2] = True THEN
value2
...
END
for as many fields as it takes.
Hope this helps.
Cheers
Gary|||Thanks to you all people up there...it solved my problem...Have a wonderful Holidays...:)sql
cursor to insert into a table variable
I would appreciate some help in writing a cursor to populate a table variable based on a result set
All the cursor does is find all rows with same area ID and areaName and insert into the table variable the one with the latest modified date, if all modified dates are equal then insert the one with the latest created date.
This is the result set(id int, areaID int, areaName varchar(30), created datetime, modifieddatetime):
3 10 MA 2005-03-04 2005-03-04
6 10 MA 2005-05-04 2005-03-04
9 10 MA 2005-03-04 2005-03-04
1 52 PUERTO RICO 2005-03-04 2005-03-04
2 52 PUERTO RICO 2005-03-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
8 52 PUERTO RICO 2005-03-04 2005-03-04
5 5 NY 2005-04-04 2005-03-04
7 5 NY 2005-03-04 2005-03-04
table variable @.tempTable(id int, areaID int, areaName varchar(30), created datetime, modified datetime)
After running this cursor my temp table would contain:
6 10 MA 2005-05-04 2005-03-04
4 52 PUERTO RICO 2005-03-04 2005-05-04
7 5 NY 2005-03-04 2005-03-04
Any help would be appreciated
you don't need a cursor for this at all
this should do it, change YourTable to your table name
insert into @.tempTable(id ,areaID ,areaName , created , modified )
select t1.* from
YourTable t1 join(
select areaID ,areaName , max(created)as MaxCreated,max( modified) as MaxModified
from YourTable
group by areaID ,areaName) t2 on
t1.areaID = t2.areaID
and t1.areaName = t2.areaName
and t1.created = t2.MaxCreated
and t1.modified = t2.MaxModified
if it is possible that you can have created and modified that are the same for an area then you have to group on t1 and grab the min or max id
Denis the SQL Menace
http://sqlservercode.blogspot.com/
The following query will do..
Select TwoCol.* From
TwoCol
Join
(
Select
IdWise.ColOne,
IdMaxModdate,
IdMaxCrdDate
From
(Select ColOne,ModifieddateTime,Count(*) ModDateCount from TwoCol
group By ColOne,ModifieddateTime) as ModDateWise
Join (Select ColOne,Count(*) IdCount,max(ModifieddateTime) IdMaxModdate, max(createddatetime) IdMaxCrdDate
From TwoCol Group By ColOne) as IdWise On ModDateWise.ColOne = IdWise.ColOne And IdMaxModdate = ModifieddateTime
) as T On T.ColOne = TwoCol.ColOne And ModifieddateTime = IdMaxModdate And createddatetime = IdMaxCrdDate
cursor select and variables
I have problems to place my variable into the select statement.
DECLARE @.DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @.DB_NAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.DB_NAME; --works fine
Select NAME, FILEDIRECTORY FROM @.DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\\%');
FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @.DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor
GO
How could i use a variable like @.DB_Name in my select ?
the object against which you run a query cannot be a variable. You need use dynamic SQL by first constructing your SQL query as a string and then using the EXEC command or the sp_execute_sql system stored procedure. e.g.
declare @.sql VARCHAR(4000)
set @.sql = 'Select NAME, FILEDIRECTORY FROM ' + @.DB_NAME + '.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE ''%\\%'');'
EXEC (@.sql)
Tuesday, March 27, 2012
Cursor issues
I can get it to create the proc, but it dosen't replace the variable or loop through the select list from ziptrendindex.
Any ideas on what I'm missing?
declare @.countyname varchar (200)
declare @.sql varchar(8000)
declare county_name cursor for
select distinct county from ZipTrendIndex
open county_name
fetch next from county_name
into @.countyname
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[county'+@.countyname+']') AND type in (N'U'))
BEGIN
set @.sql = 'CREATE PROCEDURE dbo.getUpdRecords AS
SET NOCOUNT ON
Declare @.PropertyID bigint
Declare @.PropertyIDBuffer bigint
Declare @.AVMValue float
Declare @.Confidence float
Declare @.a bigint
Declare @.b smallint
Set @.a = 1
Set @.b = 0
While (@.a) > 0
Begin
Select top 1 @.PropertyIDBuffer = sa_property_id from county_name (NOLOCK) Where replyavm is null
If @.PropertyIDBuffer is not null Or @.PropertyIDBuffer > 0
Begin
Set @.b = 0
DECLARE Property_Details CURSOR FOR
Select top 50 sa_property_id
From county_name (NOLOCK)
Where replyavm is null
OPEN Property_Details
FETCH NEXT FROM Property_Details
INTO @.PropertyID
WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.AVMValue = 0
Set @.Confidence = 0
exec usp_ReplyAVMCalculationcounty_name @.PropertyID, @.AVMValue output, @.Confidence output
If @.AVMValue is Null
Begin
Set @.AVMValue = 0
Set @.Confidence = 0
End
Update county_name
Set ReplyAVM = @.AVMValue,
ReplyScore = @.Confidence
Where sa_property_id = @.PropertyID
FETCH NEXT FROM Property_Details
INTO @.PropertyID
END
CLOSE Property_Details
DEALLOCATE Property_Details
End
Else
Set @.a = 0
End
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
'
exec (@.sql)
end
fetch next from county_name into @.countyname
end
CLOSE county_name
DEALLOCATE county_name
One quick look and I found you are not setting @.sql to '' while looking through.
exec (@.sql)
end
SET @.sql = ''
fetch next from county_name into @.countyname
end
|||Thanks!!
That did the trick!!
Sunday, March 25, 2012
Cursor Error...
Server: Msg 137, Level 15, State 2, Line 22
Must declare the variable '@.tbl_name'.
when I run...
declare @.counter int, @.tbl_name varchar(40)
select @.counter = 1
declare loop_cursor cursor for SELECT distinct name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
UPDATE @.tbl_name
SET marcA = substring(marcA,1,2)
WHERE marc = 1
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
END
FETCH loop_cursor INTO @.tbl_name
CLOSE loop_cursor
DEALLOCATE loop_cursor
am i missing something blatently obvious, it's friday so probably so arrrgh
The creates are as follows for anyone wishing to try it
drop table marc, marc1
create table marc (marc int, marcA varchar(5))
create table marc1 (marc int, marcA varchar(5))
insert into marc values(1, 'ABC')
insert into marc values(2, 'ABC')
insert into marc1 values(1, 'ABC')
insert into marc1 values(2, 'ABC')The SQL Server parser doesn't resolve variables as table/column names.
You'll need to use dynamic SQL to do this in Transact-SQL.
See http://www.sommarskog.se/dynamic_sql.html for an article on dynamic SQL
considerations.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:38C354EE-43DC-42D8-B4B9-E503B72D34EA@.microsoft.com...
> ...I can't seem to work it out, maybe you can help. I get a
> Server: Msg 137, Level 15, State 2, Line 22
> Must declare the variable '@.tbl_name'.
> when I run...
> declare @.counter int, @.tbl_name varchar(40)
> select @.counter = 1
> declare loop_cursor cursor for SELECT distinct name FROM
> sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> UPDATE @.tbl_name
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> END
> FETCH loop_cursor INTO @.tbl_name
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> am i missing something blatently obvious, it's friday so probably so
> arrrgh
>
> The creates are as follows for anyone wishing to try it
> drop table marc, marc1
> create table marc (marc int, marcA varchar(5))
> create table marc1 (marc int, marcA varchar(5))
> insert into marc values(1, 'ABC')
> insert into marc values(2, 'ABC')
> insert into marc1 values(1, 'ABC')
> insert into marc1 values(2, 'ABC')|||thanks Dan, I replaced
/*
UPDATE @.tbl_name1
SET marcA = substring(marcA,1,2)
WHERE marc = 1
*/
to dynamic as follows but it only updates the first row of the first
table...Any ideas
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command|||> but it only updates the first row of the first
> table...Any ideas
It appears you need a FETCH within the WHILE loop. Try:
declare @.counter int, @.tbl_name varchar(40)
declare @.command nvarchar(4000)
select @.counter = 1
declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
WHERE NAME in (
'marc', 'marc1') and xtype = 'U'
open loop_cursor
fetch loop_cursor into @.tbl_name
WHILE @.@.fetch_status = 0
BEGIN
Set @.Command = ' UPDATE ' + @.tbl_name + '
SET marcA = substring(marcA,1,2)
WHERE marc = 1 '
Execute sp_executesql @.Command
PRINT @.counter
PRINT '____________________________'
PRINT @.tbl_name + 'Post Codes Updated'
PRINT ' '
SELECT @.counter = @.counter + 1
FETCH loop_cursor INTO @.tbl_name
END
CLOSE loop_cursor
DEALLOCATE loop_cursor
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
> thanks Dan, I replaced
> /*
> UPDATE @.tbl_name1
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1
> */
> to dynamic as follows but it only updates the first row of the first
> table...Any ideas
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
>|||thankyou Dan, appreciate it.
"Dan Guzman" wrote:
> It appears you need a FETCH within the WHILE loop. Try:
>
> declare @.counter int, @.tbl_name varchar(40)
> declare @.command nvarchar(4000)
> select @.counter = 1
> declare loop_cursor cursor for SELECT name FROM sysobjects(nolock)
> WHERE NAME in (
> 'marc', 'marc1') and xtype = 'U'
> open loop_cursor
> fetch loop_cursor into @.tbl_name
> WHILE @.@.fetch_status = 0
> BEGIN
> Set @.Command = ' UPDATE ' + @.tbl_name + '
> SET marcA = substring(marcA,1,2)
> WHERE marc = 1 '
> Execute sp_executesql @.Command
> PRINT @.counter
> PRINT '____________________________'
> PRINT @.tbl_name + 'Post Codes Updated'
> PRINT ' '
> SELECT @.counter = @.counter + 1
> FETCH loop_cursor INTO @.tbl_name
> END
> CLOSE loop_cursor
> DEALLOCATE loop_cursor
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:E06D4572-A6BC-463C-A6E0-17FE3F68E802@.microsoft.com...
>
>sql
cursor error must declare scalar variable
SELECT * FROM LCTRY
WHERE LANGUAGE = 'EN' AND CTRY_CODE = :CTRY
BUT
SELECT * FROM LCTRY
WHERE CTRY_CODE = :CTRY AND LANGUAGE = 'EN'
PRODUCES AN ERROR 000137 MUST DECALRE SCALAR VARIABLE
'@.P1AND'
The application works with MySQL.Hi,
It could be that you are missing single quotes around one of your parameters.
Cheers
C
Cursor count
Hi!
How can I get the size of a cursor variable (the number of rows in it)?
Thank you!
Hi,use @.@.CURSOR_ROWS
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Thursday, March 22, 2012
Current user in SQL
Hi
Is there a variable or a possibility to use the current user in sql?
I would like to create an update trigger and write the user that made the change in the affected table!
Thanks!
Try SUSER_NAME() for the login or USER_NAME() for the database user.
|||Pick your poison ;-)
SELECT SUSER_SNAME(),USER_NAME(),CURRENT_USER
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
Sunday, February 19, 2012
CTE vs. Table Variable (Paging)
I'm trying to find the pros / cons of using CTEs (Common Table Expressions)
vs. Table Variables for 'Paging' through data.
Here is the scanario:
A. Table Variable Example:
Select Query returns 100k posible matching rows, I store the matching key
column along with an identity column in a table varable (i.e. DECLARE @.tmp
TABLE (rowid int identity, userid int) ), then I left join users to userid i
n
@.tmp where @.tmp.rowid between N and N.
B. CTE Example:
WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT * from
CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
What I noticed so far is:
CTE compares ROW_NUMBER() expression to the values of the BETWEEN expression
and returns only matching rows,
While Table Variables are first saving all userids in memory, then going
back for a table scan for a match for the BETWEEN expression.
I would like to know if anyone here has a definitive answer on why and in
what scanario can CTEs be faster then Table Variables and vice versa.
Thank you in advance for all your help.
- Eyal Zinder.>> I would like to know if anyone here has a definitive answer on why and in
In general, unless you have an environment where every factor that affects
performance is controlled, comparisons are moot. Also, there could be
several other ways one could achieve similar results. There could be even
different approaches for paging rows using a CTE or table variable as well.
So simply asking which construct/structure is "faster" doesn't mean much.
Your posted examples are not very clear either. Please post a sample table
structure, a few sample data as insert statements and legible code snippets
that demostrates your paging attempts.
Anith|||There are far better options than table variables.
http://www.aspfaq.com/2120
I have not played with CTEs in this case.
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||".. unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.. "
The database environment I work with currently exceeds 500,000 hits per
second.
I am very much concerned with the smallest difference in performance.
As for my examples, here is a detailed view:
/* TABLE VARIABLE EXAMPLE: */
SET NOCOUNT ON
DECLARE @.tmp TABLE (rowid int identity, userid int)
INSERT INTO @.tmp (userid)
SELECT userid
FROM users (nolock)
WHERE userStatus = @.N1
ORDER BY userLastLoginDate
SELECT u.*
FROM @.tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
/* CTE EXAMPLE */
SET NOCOUNT ON
WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY u.userLastLoginDate) AS rowid
FROM users (nolock)
WHERE userStatus = @.N1
)
SELECT u.*
FROM tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid
WHERE t.rowid BETWEEN @.N2 and @.N3
ORDER BY t.rowid
Again, I am NOT looking for new / better ways to page through data or any
Cursor based paging. I am looking for what pros / cons does CTE offer over
the above solution (variable table example). I am interested to know how CT
E
works and how it differs from the above example.
Thank you again for all your help and prompt reply.
Eyal Zinder.
"Anith Sen" wrote:
> In general, unless you have an environment where every factor that affects
> performance is controlled, comparisons are moot. Also, there could be
> several other ways one could achieve similar results. There could be even
> different approaches for paging rows using a CTE or table variable as well
.
> So simply asking which construct/structure is "faster" doesn't mean much.
> Your posted examples are not very clear either. Please post a sample table
> structure, a few sample data as insert statements and legible code snippet
s
> that demostrates your paging attempts.
> --
> Anith
>
>|||Hi Eyal,
Make sure you keep it server side and only pass back the page the user wants
to the client, that will save significanly on resources especially the
network.
I, personally, don't tend to use CTE for paging because it does the whole
query first and the way I design schema I only need to join for those rows
on my page to get the 'meta' data - basically, I search on surrogate keys
where possible.
Personally I'd be inclided to use the ROWNUMBER() method and pump the output
into a table variable and join that table variable out to the base tables to
get my 'meta' as described above.
The reason is simple, it cuts down on IO.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> Hello Experts.
> I'm trying to find the pros / cons of using CTEs (Common Table
> Expressions)
> vs. Table Variables for 'Paging' through data.
> Here is the scanario:
> A. Table Variable Example:
> Select Query returns 100k posible matching rows, I store the matching key
> column along with an identity column in a table varable (i.e. DECLARE @.tmp
> TABLE (rowid int identity, userid int) ), then I left join users to userid
> in
> @.tmp where @.tmp.rowid between N and N.
> B. CTE Example:
> WITH CTEUsers AS (select row_number() as rowid, userid ... ) SELECT *
> from
> CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N
> What I noticed so far is:
> CTE compares ROW_NUMBER() expression to the values of the BETWEEN
> expression
> and returns only matching rows,
> While Table Variables are first saving all userids in memory, then going
> back for a table scan for a match for the BETWEEN expression.
>
> I would like to know if anyone here has a definitive answer on why and in
> what scanario can CTEs be faster then Table Variables and vice versa.
> Thank you in advance for all your help.
> - Eyal Zinder.
>
>|||>> I am looking for what pros / cons does CTE offer over the above solution
There is no empirical evidence that suggests one approach is always better
than the other. In your specific situation, you should evaluate and compare
the query plans and execution times and decide which one performs better.
You can think of CTE as a temporary resultset/virtual table that lasts only
for the duration of the query. The primary benefits of a CTE include
generation of recursive queries, allowance of multiple references in the
same query and overall simplicity ( many complex queries can be simplified
with a well written CTE )
In your example, CTE offers nothing additional to the overall paging
functionality of the code. In other words, you can avoid the CTE altogether
and use a derived table to achieve similar results. In a small sample I
tested, the plans with a derived table and with a CTE were mostly similar
and provided similar performance.
In general, the "paging" methods are the SQL are an extension of a class of
queries called Quota queries in relational literature. Quota queries sort
the rows based on some explicit sequence of values in a column and then
identify the top/bottom subset (quota). You might want to research on that
if you'd like some background on such formulations.
There are several different approaches to this problem and Aaron's website
offers some of the best SQL 2000 methods that are frequently posted in this
newsgroup.
Anith|||Aaron,
Thank you. But the scale of this site does not allow for middle-tier paging
.
"Aaron Bertrand [SQL Server MVP]" wrote:
> There are far better options than table variables.
> http://www.aspfaq.com/2120
> I have not played with CTEs in this case.
>
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||Tony,
Could you provide an example of using Meta Data for such a scanario?
"Tony Rogerson" wrote:
> Hi Eyal,
> Make sure you keep it server side and only pass back the page the user wan
ts
> to the client, that will save significanly on resources especially the
> network.
> I, personally, don't tend to use CTE for paging because it does the whole
> query first and the way I design schema I only need to join for those rows
> on my page to get the 'meta' data - basically, I search on surrogate keys
> where possible.
> Personally I'd be inclided to use the ROWNUMBER() method and pump the outp
ut
> into a table variable and join that table variable out to the base tables
to
> get my 'meta' as described above.
> The reason is simple, it cuts down on IO.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||The code below runs on my site http://sqlserverfaq.com and performs the
listing and searching of Articles.
You will see I use a temporary table with mostly id's in there and then at
the very end join only for those rows I'm throwing back to the client.
Tony.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ukug3_selGetKBArticles]
@.max_pages int output,
@.required_page int = 1,
@.rows_per_page int = 2,
@.FileType varchar(10) = '',
@.idEvents int = 0,
@.SearchKeywords varchar(200) = '',
@.OpType char(1) = 'F',
@.member_group_id int = NULL,
@.is_member_group_restrict char(1) = 'N'
as
begin
set nocount on
create table #results (
idrow int not null identity,
idKBArticle int not null,
Rank int not null,
Characterization varchar(500) not null default( '' )
)
declare @.from_row int
declare @.to_row int
set @.from_row = ( (@.required_page-1) * @.rows_per_page ) + 1
set @.to_row = @.from_row + ( @.rows_per_page - 1 )
declare @.sql nvarchar(4000)
if @.SearchKeywords > ''
begin
DECLARE @.user_search_text varchar(300)
SET @.user_search_text = @.SearchKeywords
SET @.OpType = 'C' -- Done for performance
SET @.SearchKeywords = dbo.fn_search_cleanse( @.SearchKeywords,
'AND' ) -- Gets rid of noise words and adds 'AND'
IF @.SearchKeywords = '' -- Bad search, give it another chance and
use freetext instead.
BEGIN
SET @.OpType = 'F'
SET @.SearchKeywords = @.user_search_text
END
-- If restricting to a member group then add the additional search
clause for that group
IF @.is_member_group_restrict = 'Y'
BEGIN
SELECT @.SearchKeywords = @.SearchKeywords + ' ' + search_clause
FROM member_group
WHERE id = @.member_group_id
END
SET @.sql = 'SELECT TOP 50 *
FROM (
SELECT DISTINCT
kba.idKBArticle,
[Rank],
Characterization
FROM ( SELECT DISTINCT TOP 50 [FileName],
[Rank],
Characterization
FROM OPENQUERY( lsIndexServer,
''SELECT FileName, Rank, Characterization
FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @.OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
'( '' +
@.SearchKeywords + '' )'' )
WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
) AS qry
INNER JOIN KBArticle kba ON kba.ArticleFileName =
qry.[FileName]'
-- IF @.member_group_id > 0
-- SET @.sql = @.sql + ' WHERE EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=kba.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
SET @.sql = @.sql + '
UNION ALL
SELECT
kba.idKBArticle,
[Rank] = 9999,
''''
FROM KBArticle kba
WHERE kba.ArticleFileName = ''' + @.SearchKeywords +
''' ) AS dt
ORDER BY Rank DESC'
end
else
begin
set @.sql = N'
select idKBArticle, 9999, ''''
from kbarticle k
where 1=1
and is_external_url_link_broken = ''N''
'
-- Search clause
if @.FileType <> ''
set @.sql = @.sql + N' and FileType=@.FileType'
else if @.idEvents = 0
set @.sql = @.sql + N' and FileType<>''wmv''' -- WMV is dealt
with in its own control now so without this we would duplicate content (ok
on the search though!)
if @.idEvents > 0
set @.sql = @.sql + N' and k.idEvents = @.idEvents'
else
set @.sql = @.sql + N' and FileType <> ''ZIP'''
IF @.member_group_id > 0
SET @.sql = @.sql + ' AND EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=k.idKBArticle AND
x.member_group_id=' + CAST( @.member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'
set @.sql = @.sql + N'
order by EntryDate desc'
end
print @.sql
insert #results ( idKBArticle, Rank, Characterization )
exec sp_executesql @.sql,
N'@.FileType varchar(10), @.idEvents int',
@.FileType, @.idEvents
set @.max_pages = ( @.@.rowcount + ( @.rows_per_page - 1 ) ) /
@.rows_per_page
select id,
title,
author_name,
entry_date,
article_summary = dt.article_summary + case when len(
dt.article_summary ) = 100 then '...' else '' end,
FileType,
ArticleFileName,
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from (
select t.idRow,
id = t.idKBArticle,
title = k.KBArticleTitle,
author_name = case when k.external_url_link = '' or
k.external_url_link = 'HTTP://' then isnull( r.fullname, '' ) else '' end,
entry_date = CONVERT( varchar(20), k.ModifiedDate, 106 ),
article_summary= SUBSTRING( CASE WHEN t.Characterization = ''
THEN k.KBArticleAbstract ELSE t.Characterization END, 1, 100 ),
FileType = ISNULL( FileType, '' ),
ArticleFileName= ISNULL( ArticleFileName, '' ),
CompressedSize,
UncompressedSize,
movie_length,
external_url_link
from #results t
inner join kbarticle k on k.idKBArticle = t.idKBArticle
left outer join registrations r on r.idregistrations =
k.idregistrations
where idrow between @.from_row and @.to_row ) as dt
order by idrow
end
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>|||And the ASP.NET (VB.NET) to call the proc...
Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
dbConn.Open()
Dim cmdSQL As SqlClient.SqlCommand
Dim daSQL As New SqlDataAdapter
Dim dsSQL As New DataSet
' Get Articles
cmdSQL = New SqlCommand("ukug3_selGetKBArticles", dbConn)
cmdSQL.CommandType = CommandType.StoredProcedure
cmdSQL.Parameters.Add(New SqlParameter("@.required_page",
Me.ResultsPageNumber))
cmdSQL.Parameters.Add(New SqlParameter("@.rows_per_page",
ConfigurationSettings.AppSettings("KBRowsPerPage")))
If Me.Search_FileType <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.FileType", Me.Search_FileType))
If Me.Search_EventId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.idEvents", Me.Search_EventId))
If Me.Search_Keywords <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@.SearchKeywords", Me.Search_Keywords))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.member_group_id", Me.MemberGroupId))
If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@.is_member_group_restrict", IIf(Me.isRestrictToMemberGroup,
"Y", "N")))
Dim sqlParm As SqlParameter
sqlParm = cmdSQL.Parameters.Add(New SqlParameter("@.max_pages",
CInt(0)))
sqlParm.Direction = ParameterDirection.Output
daSQL.SelectCommand = cmdSQL
Dim iPages As Integer
Try
daSQL.Fill(dsSQL)
datlArt.DataSource = dsSQL
datlArt.DataBind()
iPages = cmdSQL.Parameters("@.max_pages").Value
Catch ex As Exception
iPages = 0
End Try
trNoArticles.Visible = (datlArt.Items.Count = 0)
If iPages = 0 Then
tdPage.Visible = False
Else
lbtnPageNext.Visible = (Me.ResultsPageNumber < iPages)
lbtnPagePrev.Visible = Me.ResultsPageNumber > 1
lblPageCur.Text = Me.ResultsPageNumber.ToString
lblPageLast.Text = iPages.ToString
tdPage.Visible = True
End If
dbConn.Close()
dbConn.Dispose()
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>
CSV String
Hi,
I have a variable like @.states=NY,VA,AL,CA.
Now i want to use the above string in the IN clause.How to separate those string and use.
Select * from xxx where @.State IN('NY','VA','AL','CA')
Thanks in advance
Here are a couple of pretty good references:
http://www.sommarskog.se/arrays-in-sql.html
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1
Tuesday, February 14, 2012
CSV
Hi,
I have a variable like @.states=NY,VA,AL,CA.
Now i want to use the above string in the IN clause.How to separate those string and use.
Select * from xxx where @.State IN('NY','VA','AL','CA')
Thanks in advance
Create Function dbo.ListOfvalues(@.values varchar(8000),@.delimeter char) Returns @.list table (Item varchar(1000))
as
Begin
While CharIndex(@.delimeter,@.values) >0
Begin
Insert Into @.list
Select Ltrim(RTrim(Left(@.values,CharIndex(@.delimeter,@.values)-1)))
Select @.values=RTRIM(Ltrim(Right(@.values,len(@.values)-CharIndex(@.delimeter,@.values))))
End
Insert Into @.list
Select @.values
Return;
End
go
Set @.States = 'NY,VA,AL,CA'
Select * from xxx where State IN (Select item from dbo.ListOfvalues(@.States,','))
|||Thank you very much.