syntax of while loops

Question

Post reply

syntax of while loops

craig 84462

Say Hey Kid

Points: 669
More actions
March 22, 2010 at 2:32 pm

#231107

hi there,
if I want to do a condition on a boolean operator within a while loop is it like this:
thanks heaps for the reply, do you mean this?
**************************************
While @@FETCH_STATUS = 0
begin
if @Statisticvalue <=100
Begin
do something if @statistic value <=100
end
end
this is using a cursor and a fetch

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What exactly is your question?

Please read and follow the first link in my signature on how to post sample data.

In your specific case: It is more than likely that you don't need to use a loop or cursor approach at all.

If you'll give us enough information about what you try to do we might be able to help you finding a non-RBAR solution.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

craig 84462 Say Hey Kid Points: 669 More actions · Answer 2

ok, I am just learning SQL so would like to use a cursor if possible then i'll look for a solution without using them.

I have created a table called "demo" that has 5 records of data. In this table there are the variables database,schename,tablename, columnname and statistic value. Based on the value of statistic value , if it is <=100 then I want to "insert" counts into this table. Demo2 is a copy of the demo table. If I run the below code, it just 'hangs ' and does nothing, the syntax is correct but the query does nothing. This is the problem I think, this part:While@@FETCH_STATUS=0begin if @Statisticvalue <= 100.

Something to do with this part. Any help would be most appreciated

Here's my code:

*******************************************************

use Craig

go

Declare@DatabaseSysname

Declare@SchemaNameSysname

Declare@TableNameSysname

Declare@ColumnNameSysname

Declare@strExecnvarChar(Max)

Declare@StatisticvalueBigInt

-- Loop over all databases/tables/columns that are available.

DeclarecsrDatabaseTableColumnstat Cursor For

SELECT TOP 5 -- debug

DatabaseName

,SchemaName

,TableName

,ColumnName

,Statisticvalue

FROMdemo

OpencsrDatabaseTableColumnstat

FetchNext

FromcsrDatabaseTableColumnstat

Into @Database

,@SchemaName

,@TableName

,@ColumnName

,@Statisticvalue

While@@FETCH_STATUS=0

begin

if @Statisticvalue <= 100

Begin

Set@strExec=N'Insert'+' '+'demo2'+' '+'(Databasename,SchemaName,Tablename,

Columnname,Statistic,Statisticsubpart,Statisticvalue,Snapshotdate)'

Set@strExec=@strExec+'Select'+' '+''''+@Database+''''+','+

+''''+@SchemaName+''''+','

+''''+@TableName+''''+','

+''''+@ColumnName+''''+','

+'''CountDistinct'''+','

+'Convert(varChar(100), '+@ColumnName+')'+','

+'Count(0)'+','

+'Getdate()'

Set@strExec=@strExec+' '+'From'+' '+@Database+'.'+@SchemaName+'.'+@TableName

+'group by' +' '+ 'Convert(varChar(100), '+@ColumnName+')'

Exec(@strExec)

FetchNext

FromcsrDatabaseTableColumnstat

Into @Database

,@SchemaName

,@TableName

,@ColumnName

,@Statisticvalue

End

end

ClosecsrDatabaseTableColumnstat

DeallocatecsrDatabaseTableColumnstat

*************************************************8

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

I'm lost. What are you trying to do and why do you feel that you need to use a cursor?

WayneS SSC Guru Points: 95405 More actions · Answer 4

Lynn Pettis (3/22/2010)
I'm lost. What are you trying to do and why do you feel that you need to use a cursor?

From what I can gather, there is a table of database/schema/table/column names. For each of these, they are running a count(0) grouping by that column, and putting all of that into data into a second table.

Is this correct Craig?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

craig 84462 Say Hey Kid Points: 669 More actions · Answer 5

craig 84462

Say Hey Kid

Points: 669

March 22, 2010 at 6:30 pm

#1137944

Yes that is correct Wayne thanks very much

Ben Teraberry SSChampion Points: 11815 More actions · Answer 6

Looks like the placement of the 'End' on your 'If' is preventing the cursor from looping, try ...

<snip>

While@@FETCH_STATUS=0

BEGIN

if @Statisticvalue <= 100

BEGIN

Set @strExec=N'Insert'+' '+'demo2'+' '+'(Databasename,SchemaName,Tablename,

Columnname,Statistic,Statisticsubpart,Statisticvalue,Snapshotdate)'

Set @strExec=@strExec+'Select'+' '+''''+@Database+''''+','+

+''''+@SchemaName+''''+','

+''''+@TableName+''''+','

+''''+@ColumnName+''''+','

+'''CountDistinct'''+','

+'Convert(varChar(100), '+@ColumnName+')'+','

+'Count(0)'+','

+'Getdate()'

Set @strExec=@strExec+' '+'From'+' '+@Database+'.'+@SchemaName+'.'+@TableName

+'group by' +' '+ 'Convert(varChar(100), '+@ColumnName+')'

Exec(@strExec)

END

FetchNext

FromcsrDatabaseTableColumnstat

Into @Database

,@SchemaName

,@TableName

,@ColumnName

,@Statisticvalue

End

Close csrDatabaseTableColumnstat

DeallocatecsrDatabaseTableColumnstat

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

So this needs a cursor?

If you would provide the DDL (CREATE TABLE statement(s)) for the table(s), sample data (INSERT INTO statements), and expected results based on the sample data you may be surprised that there are other ways to solve this problem and that won't involve a cursor.