how to write for loop and cursor in sql server

Question

how to write for loop and cursor in sql server

Viewing 15 posts - 31 through 45 (of 47 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Read the first article I have referenced below in my signature block regarding asking for help. It will give you step by step instructions on what and how to post the information we need to help you.

manub22 Hall of Fame Points: 3163 More actions · Answer 2

Hi @Sivaganesh,

First of all explain your problem and the issue you are dealing with?

What all steps you've taken till now and where are you stuck?

Provide some test data and expected output.

Using Loops or cursors for problem solving is not a good idea.

TSQL is a set based language and you can create customized and complex queries to perform your task.

Loops/cursors should be used as a last resort.

By the way if you want check how to use them check the following blog posts:

Cursor usage: http://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/[/url]

Loops and Cursors: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/[/url]

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 3

here iam just writing a search engine for a databsae

which is having seven table

i mentioned my code here with split function

http://www.sqlservercentral.com/Forums/Topic1268181-391-2.aspx

i used cursor here

for looping

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 4

here iam just writing a search engine for a databsae

which is having seven table

i mentioned my code here with split function

http://www.sqlservercentral.com/Forums/Topic1268181-391-2.aspx

i used cursor here

for looping it shows out put correctly

for example data s

the seven tables are joined by primary key and foregin key

tblmember table1

memberid (pk) firstname lastname loginmail password

1 srt robort sert@gma 58647

tbladspaceid table 2

adspaceid(pk) adspacename memberid (fk)

1 adn 1

tbladinfo table 3

adinfoid(pk) adspaceid(fk) bussinnesname

1 1 gnd

similarly all the other table are connected

with primark key and foregin key

i just want search words as like google did in google search

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 5

here iam just writing a search engine for a databsae

which is having seven table

i mentioned my code here with split function

http://www.sqlservercentral.com/Forums/Topic1268181-391-2.aspx

i used cursor here

for looping it shows out put correctly

for example data s

the seven tables are joined by primary key and foregin key

tblmember table1

memberid (pk) firstname lastname loginmail password

1 srt robort sert@gma 58647

tbladspaceid table 2

adspaceid(pk) adspacename memberid (fk)

1 adn 1

tbladinfo table 3

adinfoid(pk) adspaceid(fk) bussinnesname

1 1 gnd

similarly all the other table are connected

with primark key and foregin key

i just want search words as like google did in google search

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 6

here iam just writing a search engine for a databsae

which is having seven table

i mentioned my code here with split function

http://www.sqlservercentral.com/Forums/Topic1268181-391-2.aspx

i used cursor here

for looping it shows out put correctly

for example data s

the seven tables are joined by primary key and foregin key

tblmember table1

memberid (pk) firstname lastname loginmail password

1 srt robort sert@gma 58647

tbladspaceid table 2

adspaceid(pk) adspacename memberid (fk)

1 adn 1

tbladinfo table 3

adinfoid(pk) adspaceid(fk) bussinnesname

1 1 gnd

similarly all the other table are connected

with primark key and foregin key

i just want search words as like google did in google search

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

You know, if you don't want our help improving your code and your skills working with SQL Server, just let us know and we will go away.

All we are trying to do is show you a better way to write code so that you can do it yourself later.

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 8

i dont say like that lynn why u feel like that

i just stated the information plz tell me if any new idea inthis store proc

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 9

if my typed word s hurts you means i am extremely sorry lynn pettis

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

er.sivaganesh (3/24/2012)
if my typed word s hurts you means i am extremely sorry lynn pettis

Because your words indicate that you really don't want to learn new methods, or to improve your code. Doesn't hurt me, just happens to hurt you in the end.

If you want to improve your code, take the time to read the first article I reference below in my signature block regarding asking for help. Follow the instruction in that article regarding what to post and how. If you don't post the information that you have been asked for so many times now, we will know that you really don't care about learning and becoming better.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

er.sivaganesh (3/24/2012)
i dont say like that lynn why u feel like that
i just stated the information plz tell me if any new idea inthis store proc

I have ideas, you said it didn't work, but you refuse to post what we need to test and debug the code. Again, you are obviously not interested in getting better code.

Sivaganesh Tamilvendhan Hall of Fame Points: 3498 More actions · Answer 12

lynn you need the script of all the table to get the solution

the only thing is if search a sentence it should go through all the table table

and it has to pick the related word from all the table

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

er.sivaganesh (3/24/2012)
lynn you need the script of all the table to get the solution the only thing is if search a sentence it should go through all the table table and it has to pick the related word from all the table

I am done. You obviously do not want any help as you have demonstrated you are not going to provide the DDL (CREATE TABLE statement(s)) for the table(s), sample data (as a series of INSERT INTO statements) for the table(s), or the expected results based on the sample data.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

er.sivaganesh (3/24/2012)
lynn you need the script of all the table to get the solution the only thing is if search a sentence it should go through all the table table and it has to pick the related word from all the table

This is your code:

alter PROCEDURE [dbo].[SP_Searchengine]

@VALUES varchar(8000)

AS

BEGIN

create table solution

(

AdSpaceId int,

AdInfoId int,

BusinessName varchar(200),

AdSpaceName varchar(200),

FileName varchar(200),

TotalBlocks int

)

declare @table table

(

items varchar(8000)

)

Insert into @table( items)

select items from [characterSplit](@VALUES,'')

DECLARE @AccountID varchar(200)

DECLARE @getAccountID CURSOR

SET @getAccountID = CURSOR FOR

SELECT

items

FROM

@table

OPEN

@getAccountID

FETCH NEXT

FROM

@getAccountID INTO @AccountID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @AccountID = REPLACE(RTRIM(LTRIM(@AccountID)), ' ', '%' );

insert into solution( AdSpaceId,AdInfoId,BusinessName,AdSpaceName,FileName,TotalBlocks)

select

s.AdSpaceId

,i.AdInfoId

,i.BusinessName

,s.AdSpaceName

,s.FileName

,s.TotalBlocks

from

tblAdSpace s

join

tblMembers m on m.MemberId=s.MemberId

join

tblAdInfo i on s.AdSpaceId=i.AdSpaceId

join

tblAdBlock b on s.AdSpaceId=b.AdSpaceId

join

tblAdContactInfo INF ON INF.AdInfoId=i .AdInfoId

join

tblApplicableCategories ACS ON ACS.AdInfoId= i .AdInfoId

join

tblAdCategory ac ON ac.CategoryId =ACS.AdInfoId

join

tblAdSubCategory sb ON sb .CategoryId = ACS.AdInfoId

WHERE

s.AdSpaceName LIKE '%'+ @AccountID + '%'

or

s.FileName LIKE '%'+ @AccountID + '%'

or

m.Mobile LIKE '%'+ @AccountID + '%'

or

m.FirstName LIKE '%'+ @AccountID + '%'

or

m.LastName LIKE '%'+ @AccountID + '%'

or

m.LastName LIKE '%'+ @AccountID + '%'

or

i.BusinessName LIKE '%'+ @AccountID + '%'

or

i.Notes LIKE '%'+ @AccountID + '%'

or

INF.Value LIKE '%'+ @AccountID + '%'

or

ac .CategoryName LIKE '%'+ @AccountID + '%'

or

sb.SubCategoryName LIKE '%'+ @AccountID + '%'

FETCH NEXT

FROM

@getAccountID INTO @AccountID

END

CLOSE

@getAccountID

DEALLOCATE

@getAccountID

select AdSpaceId,AdInfoId,BusinessName,AdSpaceName,FileName,TotalBlocks from solution

drop table solution

END

for this only i just cursor i used cursor if there is any other way just tell me

here i used split function for spliting input words

It joins specific tables together and then searches specific columns in those tables. I have been asking for the DDL for those tables, sample data for those tables, and the expected results from the procedure based on that sample data.

What don't you seem to understand? Your code is not generic and meant to search all columns of all tables in a database, IT IS SPECIFIC to your database and the tables and columns to be searched.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 15

Cursors may be up to 10 times slower than set-based solutions when compared.

Cursors are used when you need to do operations on a relatively small set of objects, e.g. update statistics for all tables in database or so...

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com