Looping through temp table to produce final result set

I have a temp table that is populated elsewhere in the stored proc:
@.table
(
userid,
spec1,
spec2
)
Spec1 = specialization 1, Spec2 = specialization2
Each user has two specializations, and a number of associated skills
for that specialization in a table.
now I need to loop through this table, pulling out only one userid at
a time, for my final query that displays a bunch of other things. I
have a UDF that concatenates the string of values from the different
rows from the skill table so that it displays the skills as one list:
Specialization: DBA
Skills: SQL, Data Migration, etc.
How do I loop through the @.table to pull out only one userid at a
time, then do the insert to another @.secondtable, then loop back
through to get another userid'
StacyOn Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
>I have a temp table that is populated elsewhere in the stored proc:
>@.table
> (
> userid,
> spec1,
> spec2
> )
>
>Spec1 = specialization 1, Spec2 = specialization2
>Each user has two specializations, and a number of associated skills
>for that specialization in a table.
>now I need to loop through this table, pulling out only one userid at
>a time, for my final query that displays a bunch of other things. I
>have a UDF that concatenates the string of values from the different
>rows from the skill table so that it displays the skills as one list:
>Specialization: DBA
>Skills: SQL, Data Migration, etc.
>How do I loop through the @.table to pull out only one userid at a
>time, then do the insert to another @.secondtable, then loop back
>through to get another userid'
>Stacy
Hi Stacy,
Why would you want to loop?
INSERT INTO SecondTable (UserID, ConcatSkillList)
SELECT UserID, dbo.MyUDF(UserID)
FROM FirstTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jun 1, 2:31 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALID> wrote:
> On Fri, 01 Jun 2007 13:05:47 -0700, CalgaryDataGrl wrote:
> >I have a temp table that is populated elsewhere in the stored proc:
> >@.table
> > (
> > userid,
> > spec1,
> > spec2
> > )
> >Spec1 = specialization 1, Spec2 = specialization2
> >Each user has two specializations, and a number of associated skills
> >for that specialization in a table.
> >now I need to loop through this table, pulling out only one userid at
> >a time, for my final query that displays a bunch of other things. I
> >have a UDF that concatenates the string of values from the different
> >rows from the skill table so that it displays the skills as one list:
> >Specialization: DBA
> >Skills: SQL, Data Migration, etc.
> >How do I loop through the @.table to pull out only one userid at a
> >time, then do the insert to another @.secondtable, then loop back
> >through to get another userid'
> >Stacy
> Hi Stacy,
> Why would you want to loop?
> INSERT INTO SecondTable (UserID, ConcatSkillList)
> SELECT UserID, dbo.MyUDF(UserID)
> FROM FirstTable;
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis- Hide quoted text -
> - Show quoted text -
I need to loop because the first table holds just the specializations,
and I need to concatenate the x number of skills, my UDF can only take
one userid at a time...
CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @.SkillList varchar(1000)
SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
skill.skillname + ' (' + picklist.title + ')'
FROM candidate_skillsmatrix
inner join skill on skill.skillid = candidate_skillsmatrix.skillid
inner join specialization on specialization.specializationid =candidate_skillsmatrix.specid
inner join picklist on picklist.picklistid =candidate_skillsmatrix.expid
WHERE candidate_skillsmatrix.userid = @.UserID
and candidate_skillsmatrix.specid = @.specid
order by isrole desc, picklist.var3 desc,skillname
RETURN @.SkillList
END
This turns this:
userid1, spec 1, skill1
userid1, spec1, skill2
userid1, spec1, skill 3
userid2, spec1, skill1
userid2, spec1, skill2
into this:
userid 1, 'skill1, skill2, skill3'
when spec1 = @.spec1
and userid = @.userid
it works great when I've got only one userid, but I'm being given a
comma delimited list of values...|||On Fri, 01 Jun 2007 13:53:38 -0700, CalgaryDataGrl wrote:
>On Jun 1, 2:31 pm, Hugo Kornelis
><h...@.perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
>> Why would you want to loop?
(snip)
>I need to loop because the first table holds just the specializations,
>and I need to concatenate the x number of skills, my UDF can only take
>one userid at a time...
Hi Stacy,
I still think that you don't need to loop. Looping is only very rarely
the correct solution in SQL Server. If you include a call to the UDF in
a query, the query engine will make sure that the query gets called as
often as needed.
However, for further help I reallly need to know how your tables and
data look. You have given some descriptions in English, but description
is real SQL code are much less prone to communication errors and
misunderstanding. Could you please post CREATE TABLE statements for all
tables used for this problem (irrelevant columns may be omitted, but
please do include all constraints, indexesm and properties!), some
INSERT statements with sample data, along with expected output? That
makes it much easier to help you.
>CREATE FUNCTION dbo.udf_GetSkills(@.UserID int,@.specid int)
>RETURNS VARCHAR(1000) AS
>BEGIN
> DECLARE @.SkillList varchar(1000)
> SELECT @.SkillList = COALESCE(@.SkillList + ', ','') +
>skill.skillname + ' (' + picklist.title + ')'
> FROM candidate_skillsmatrix
> inner join skill on skill.skillid = candidate_skillsmatrix.skillid
> inner join specialization on specialization.specializationid =>candidate_skillsmatrix.specid
> inner join picklist on picklist.picklistid =>candidate_skillsmatrix.expid
> WHERE candidate_skillsmatrix.userid = @.UserID
> and candidate_skillsmatrix.specid = @.specid
>order by isrole desc, picklist.var3 desc,skillname
> RETURN @.SkillList
>END
Just for the record - this method of string concatenation, though widely
used, is not documented, nor is it guaranteed to allways produce the
expected results. If you're on SQL Server 2005, you can use a trick with
the FOR XML options to concatenate the data (Google for it - let me know
if you fail to find it). In SQL Server 2000 and older versions, you have
no other choice but to use a cursor to loop over the rows to concatenate
them.
That is the main reason that most SQL Server experts will advice you to
do string concatenation on the front end instead of in the database.
>it works great when I've got only one userid, but I'm being given a
>comma delimited list of values...
As input? Containing the userids that you need to select? In that case,
you'll also need to read Erlands article:
http://www.sommarskog.se/arrays-in-sql.html
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 1 Jun, 21:53, CalgaryDataGrl <calgarydata...@.gmail.com> wrote:
> I need to loop because the first table holds just the specializations,
> and I need to concatenate the x number of skills, my UDF can only take
> one userid at a time...
>
That doesn't mean you need to loop. You just need to think about a
more set-based approach. BTW the method of concatenation you have used
in your proc is unsupported and unreliable. I don't recommend it.
Please post DDL, sample data and expected results if you need more
help. Also tell us what version of SQL Server you are using. Don't
expect a loop to be in the answer you get! :)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
> You have given some descriptions in English, but description
> is real SQL code are much less prone to communication errors and
> misunderstanding.
Hello Hugo,
An industry that expresses itself with at most grade school clarity
and you choose to reinforce it. This is why users get things to work
without the slightest understanding of what they are doing or why
things work. This is why I have called sql 'faith based'. Understanding
is replaced by faith. The ridiculous claim that sql is a religion
gains credence. Mentors should not be high priests. And this is why
I argue for application development based on science, relational.
best,
steve|||On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> I argue for application development based on science, relational.
>
Naturally I agree:
http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
Meantime SQL is what we have so there's nothing wrong with what Hugo
said.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
> On 3 Jun, 00:25, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
>> I argue for application development based on science, relational.
> Naturally I agree:
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
'Once or twice I have floated the idea that a new relational database
language should be added to SQL Server as an alternative to the T-SQL
language. In my opinion the time is right for it.
What I'm talking about would be a relational language that lives up
to Date and Darwen's description of a "D" language.'
The 'D' language is already here! It is what Alphora has implemented
in Dataphor. Sql server can be used as the data repository and you
can freely communicate with it via pass-thru queries. The result
of such a query will be treated just like any other table variable
in D(4). If you understand query chunking you already know tactily how
the Dataphor server will work with Sql Server. The idea is to get the
performance of sql server and work with the logic of D.
Why wait for tomorrow when it is here today? -:)
www.alphora.com|||On Sat, 2 Jun 2007 16:25:54 -0700, Steve Dassin wrote:
>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
>news:epm363p2bemo7f507rg2fhrsgm2chi0l77@.4ax.com...
>> You have given some descriptions in English, but description
>> is real SQL code are much less prone to communication errors and
>> misunderstanding.
>Hello Hugo,
>An industry that expresses itself with at most grade school clarity
>and you choose to reinforce it.
Hi Steve,
That's nonsense. Stacy posts a problem, and I choose to ask her to
restate it in an unambiguous language. And since both she and I use SQL
Server as our DB platform, T-SQL is the obvious choice of unambiguous
language.
Anything that you *think* I reinforce with that, is solely a figment of
your imagination. Maybe you should go back to just advertising Rac4Sql?
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:tqj6635t8lo4dqdsr670n4jpgcl8mc2cht@.4ax.com...
>.
> Anything that you *think* I reinforce with that, is solely a figment of
> your imagination. Maybe you should go back to just advertising Rac4Sql?
>
You are an big advocate of sql and an expert. I am an sql
critic from a very clear point of view. That makes us adversaries.
Not children. Deal with it.
Here is a figment of mine that might serve as a reality check -:)
http://beyondsql.blogspot.com/2007/06/dataphor-string-differences-operator.html|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1180862757.713232.103340@.q75g2000hsh.googlegroups.com...
>.
> http://blogs.conchango.com/davidportas/archive/2007/05/23/Relational-Futures.aspx
>.
'Naturally the new language would imply a new data model as well - the
relational data model instead of the SQL one - but I don't think it
would be difficult to build it on top of SQL Server's existing engine.'
Now I see where your coming from.
All your expertise, education and insight is secondary to your
allegience to MS. So much for your intellectual integrity, you
are, in the end, just another MS fanboy. MS see, David do.
Hypocrite.|||On 5 Jun, 21:01, "Steve Dassin" <s...@.nospamrac4sql.net> wrote:
> 'Naturally the new language would imply a new data model as well - the
> relational data model instead of the SQL one - but I don't think it
> would be difficult to build it on top of SQL Server's existing engine.'
> Now I see where your coming from.
> All your expertise, education and insight is secondary to your
> allegience to MS. So much for your intellectual integrity, you
> are, in the end, just another MS fanboy. MS see, David do.
> Hypocrite.
Nothing of the kind. I'm suggesting that Microsoft improve their
product by remedying some of its defficiencies. The hypocracy is
committed by those who advocate another commercial product while
accusing others who propose a Microsoft-based solution of lacking
intellectual integrity. My opinion of you just hit rock bottom I'm
afraid.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.

looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

Looping through databases in stored proc

I am trying to loop through the databases on a server (SQL 2000) and
dynamically run sp_helpfile against each database on the server. Of course
that means I need to store the name of the database as a variable or
parameter.
When I use the following code I am told "a USE database statement is not
allowed in a procedure or trigger.":
use @.dbname
go
exec sp_helpfile
When I use the following code I am told "Incorrect syntax near '.'"
exec @.dbname..sp_helpfile
Any suggestions?
Message posted via http://www.webservertalk.comLook at the "Undocumented" stored procedure sp_MSforeachdb in the Master
database.
Don't forget, use at your own risk. Since it is undocumented, it may change
or disappear in the next version or service pack. If this is a one-time
thing, go ahead, but don't use in production code.
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com|||You'll need to use dynamic SQL. Its complaining about the variable database
name in your EXEC statement. Check out this excellent article regarding
dynamic SQL:
http://www.sommarskog.se/dynamic_sql.html
Just curious... What are you using this info for? Some sort of SQL admin.
application?
Paul
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Try,
use northwind
go
create table #t (
dbn sysname,
fileid int,
filen sysname,
fileg sysname null,
size_ varchar(15),
maxsize_ varchar(15),
growth varchar(15),
usage varchar(128)
)
declare @.sql nvarchar(4000)
declare @.db sysname
declare databases_cursor cursor
local
static
read_only
for
select
[name]
from
master..sysdatabases
where
dbid > 6
order by
[name]
open databases_cursor
while 1 = 1
begin
fetch next from databases_cursor into @.db
if @.@.error <> 0 or @.@.fetch_status <> 0 break
set @.sql = N'use [' + @.db + N'] execute sp_helpfile'
insert into #t
execute sp_executesql @.sql
end
close databases_cursor
deallocate databases_cursor
select
*
from
#t
order by
dbn, fileid
drop table #t
go
AMB
"Robert Richards via webservertalk.com" wrote:

> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com
>|||Robert Richards via webservertalk.com wrote:
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of
> course that means I need to store the name of the database as a
> variable or parameter.
> When I use the following code I am told "a USE database statement is
> not allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
>
http://www.sommarskog.se/dynamic_sql.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Yuu could try declaring and executing a string within your stored procedure
(I assume that you are using a cursor):
declare @.str varchar(255)
set @.str = 'exec ' + @.dbname + '..sp_helpfile'
--optional
select @.str AS TheStringToExecute
exec (@.str)
or you can use the undocumented stored procedure that is shown below:
EXEC sp_Msforeachdb 'PRINT (''?''); EXEC sp_helpfile'
Keith
"Robert Richards via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:76dfff48350745e79fe6fe1024555bb9@.SQ
webservertalk.com...
> I am trying to loop through the databases on a server (SQL 2000) and
> dynamically run sp_helpfile against each database on the server. Of course
> that means I need to store the name of the database as a variable or
> parameter.
> When I use the following code I am told "a USE database statement is not
> allowed in a procedure or trigger.":
> use @.dbname
> go
> exec sp_helpfile
> When I use the following code I am told "Incorrect syntax near '.'"
> exec @.dbname..sp_helpfile
> Any suggestions?
> --
> Message posted via http://www.webservertalk.com

Loop thru a SQL Table in stored proc?

Hello,

Does anyone know of a way to loop thru a SQL table using code in a stored
procedure?

I need to go thru each record in a small table and build a string using
values from the fields associated with a part number, and I can't find any
way to process each record individually. The string needs to be initialized
with the data associated with the 1st record's part number, and I need to
build the string until a new part number is incurred. Once a new part number
is found in the table, the string is written to a different table and reset
for this next part number in the table. Need to repeat until all records in
the table have been processed.

I use ADO in access 2000 to work thru local recordsets, I just can't find
anyway to do this in a stored SQL procedure.

Thanks for any suggestions, Eric.Thanks Erland, that worked in the procedure.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93BDF3734C7DEYazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Eric Martin (ermartin01@.cox.net) writes:
> > Does anyone know of a way to loop thru a SQL table using code in a
stored
> > procedure?
> Most people here know that you should not do this kind of thing, but
> one should always strive for set-based solutions. Then again...
> > I need to go thru each record in a small table and build a string using
> > values from the fields associated with a part number, and I can't find
> > any way to process each record individually. The string needs to be
> > initialized with the data associated with the 1st record's part number,
> > and I need to build the string until a new part number is incurred. Once
> > a new part number is found in the table, the string is written to a
> > different table and reset for this next part number in the table. Need
> > to repeat until all records in the table have been processed.
> This sounds like it be one of the few cases where you need an iterative
> solution. Yet, then again:
> > I use ADO in access 2000 to work thru local recordsets, I just can't
find
> > anyway to do this in a stored SQL procedure.
> Doing this client-side might be just as effecient. So if there is no
> compelling reason for doing this in a stored procedure, you may keep the
> ADO solution - even if it means that the data needs to do some extra
> roundtrips.
> Here is a sample of how such a procedure would look like:
> DECLARE @.partno varchar(10),
> @.lastpartno varchar(10),
> @.otherdata varchar(10),
> @.output varchar(8000),
> @.err int
> DECLARE part_cur CURSOR LOCAL STATIC FOR
> SELECT partno, otherdata FROM tbl ORDER BY partno
> SELECT @.err = @.@.error
> IF @.err <> 0 BEGIN DEALLOCATE part_cur RETURN @.err END
> OPEN part_cur
> WHILE 1 = 1
> BEGIN
> FETCH part_cur INTO @.partno, @.otherdata
> IF @.@.fetch_status <> 0
> BREAK
> IF @.partno <> coalesce(@.lastpartno, '')
> BEGIN
> IF @.lastpartno IS NOT NULL
> BEGIN
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 BREAK
> END
> SELECT @.lastpartno = @.partno, @.output = @.partno
> END
> SELECT @.output = @.output + ', ' + @.otherdata
> END
> DEALLOCATE part_cur
> IF @.err <> 0
> RETURN @.err
> INSERT othertbl (col1) VALUES (@.output)
> SELECT @.err = @.@.error IF @.err <> 0 RETURN @.err
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

Loop through records?

I am new to stored proc.
For eg, in ASP or VB6, we usually use a Do While ...rs.MoveNext...Loop to go through all the records in a table.
What is the equivalent of doing it in stored procs?
Please advise.
Thanks.Of course you can use cursor but it is much better to use rowset statemets - it depens on what do you want to do with your data.|||Thanks snail.

I just figured out cursors through trial and error by reading the books online.

i also just finished reading thru news groups.

many says that cursors are harmful and hogs down system resources? because this script will be run once a user logs into the system. what will be the alternative to cursors? there is an alternative way which i heard is to actually pass some data into temp tables?

pls advise.

here are the scripts i wrote:

CREATE procedure usr_clearworkerdp as
declare @.wid varchar(10)
declare workercursor cursor for
select worker_id from worker where datediff(year, date_of_clearance,getdate() ) = 0 or datediff(year, date_of_clearance,getdate() ) >0
open workercursor
fetch next from workercursor into @.wid
while @.@.FETCH_STATUS = 0
begin
update violations_committed set dp = '0' where worker_id = @.wid
fetch next from workercursor into @.wid
end
close workercursor
deallocate workercursor
GO|||If your update is as simple as the code you wrote, can't you just do this?

UPDATE worker
SET dp = '0'
WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0

There is nothing about your update that would require doing anything one row at a time.

Rob|||Originally posted by rgarrison
If your update is as simple as the code you wrote, can't you just do this?

UPDATE worker
SET dp = '0'
WHERE DATEDIFF(year, date_of_clearance, GETDATE()) >= 0

There is nothing about your update that would require doing anything one row at a time.

Rob

the date_of_clearance belongs to the worker table. the logic is to check the dates, then if more than a year, update dp column in the violations_committed table.|||ur update changes to : -

update b
set b.dp = 0
from worker a,violations_committed b
where DATEDIFF(year, a.date_of_clearance, GETDATE()) >= 0
and a.worker_id = b.worker_id

;)