Looping through

[kel1981b]

I'm relatevly new in SQL Server and may be my question is too simple for some of you. I need to retrieve data from database using sql like that
'SELECT SomeData FROM SomeTables WHERE ClienID = 12 OR ClienID = 45 OR ClienID = 23 OR ... ORDER BY ClientID'. I know how do that using ADO, recordset and embeded sql from Visual Basic App but because of some reason I have (mandatory) to use stored procedure. The question is is there way to build stored procedure looping through database. I do not need real code, the example/advice with general idea (like use statement such and such)will be fine. Thanx in advance.

 

[SQLSister]

Try

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE procedure USP_TestPRocedure(
As

begin
'SELECT SomeData FROM SomeTables WHERE ClienID = 12 OR ClienID = 45 OR ClienID = 23 OR ... ORDER BY ClientID'.
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Of course you need to fill in all the OR conditions. Also, you note that this does not loop through the data using a cursor. Looping through the data is a bad thing to be avoided wherever possible.

As to why your company might make it mandatory to use stored procedures instead of what you are used to. Stored procedures send less message traffic across your network which makes the network admin happier and the customers happier because they run faster. Also they are precompiled which can enhance performance. Third they are better from the dba standpoint for setting security and making sure the procedure is optimized. It also helps the dba know exactly what actions may be performed on the database from the user interface and therefore helps in finding out what effect a change to structure might have.

 

[kel1981b]

Thanks for your try SQLSister but this is not what I am looking for. I need build loop through database
Some thing like 'Do while/until ...'

 

[SQLSister]

NO you probably do not want a loop as this will run very slowly. What action do you want to take on each record in the loop? What conditions are you looking for in the record?

 

[kel1981b]

First of all thatks for your quick reply. Let me explain why I need loop or something else which works like loop. I need to retrieve data for particular Clients. The number of clients is very different/flexible. Let's say today we have 10 clients and we need record for 5 of them. Tomorrow we may be will have 1000 clients and need data for 100 of them (How many times I have to type OR statements in that case?) and day after tomorrow 1000 and day after that just 1 record from 100000. Can you imagine how many times I have to change stored procedure? That's why I am looking/asking for way to loop through the database. One more thing, I forgot to tell that before and it's completely my fault. ClientID is parameter(@client) coming from GUI. Again, I know how I can do that using VB code but because of some reason I must do it on server site (stored procedure).

 

[r937]

"Can you imagine how many times I have to change stored procedure?"

well, yeah, if you hardcode the client ids

pass them in as a comma-separated list

then in the stored procedure, the query will say

WHERE ClientID IN ( @ClientIDlist )

no looping required


rudy

 

[tlbroadbent]

Rudy's solution only works if you use dynamic SQL. You can use the function fn_split without requiring dynamic SQL. You can obtain a description and download code for fn_split at the following link.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/TreatYourself.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[SQLSister]

Still don't need a loop, but you do need parameters on the stored procedure.

What we do in a case like this is send the data in a big string and then use a function we created to parse it into a temp table. Then the temp table can be used to join with the regular table to get the records you want. The parse words function does use a loop but since it only looping through the input string it is not slow like looping through all the data records would be.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE procedure USP_TestPRocedure @ClientID varchar(50)
As

begin
Create table #Client
(Record nvarchar(50))

Insert into #Client (Record)
Select record from parsewords(@ClientID)

Select * from Table1 join #Client on Table1.ClientID = Cast(#Client.Record as Int)
Drop table #Client
End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I did this assuming ClientID was an int data type. If it is text you can take the cast part of the select statement out. Parsewords function is longer, I can email it to you if you want. My email is in my profile, just ask for it and it will be yours! OR you can change this as appropriate to use the fn_split function Terry describes.