Executing SP for all results

I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.comLooping through resultsets with cursors is usually a bad idea and there
is normally a better set-based solution. Can you post your proc, your
select query and some schema so we can advise on a better approach
(maybe some test data & expected results too)?
*mike hodgson*
http://sqlnerd.blogspot.com
Doc Parker wrote:

>I would like to have my stored procedure executed for each item returned by
>a Select query
>
>Ex.
>Declare @.file_id varchar(5)
>Select @.file_id = file_id from GEN where this_value <> ''
>execute sp_mystored_procedure @.file_id
>go
>Currently it only works for one record
>How do I get it to do it for all returned records?
>The top select statement may return 1000 records, but only one pass is made
>through the stored procedure
>Hints Please
>Thanks
>Darrell
>darrellp@.btmcpa.com
>
>
>|||The procedure in question actually runs as part of an exsiting TRIGGER. I am
installing this TRIGGER into a table of a database and want to call it in o
rder to extract data all ready in the table. Otherwise it works great for re
cords currently being added. If I were to use a CURSOR to run this SP it wou
ld be a one time deal. Nothing permanent.
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uWo%23nF6JGHA.340
8@.TK2MSFTNGP12.phx.gbl...
Looping through resultsets with cursors is usually a bad idea and there is n
ormally a better set-based solution. Can you post your proc, your select qu
ery and some schema so we can advise on a better approach (maybe some test d
ata & expected results too)?
mike hodgson
http://sqlnerd.blogspot.com
Doc Parker wrote:
I would like to have my stored procedure executed for each item returned by
a Select query
Ex.
Declare @.file_id varchar(5)
Select @.file_id = file_id from GEN where this_value <> ''
execute sp_mystored_procedure @.file_id
go
Currently it only works for one record
How do I get it to do it for all returned records?
The top select statement may return 1000 records, but only one pass is made
through the stored procedure
Hints Please
Thanks
Darrell
darrellp@.btmcpa.com

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

Executing a Stored Procedure in a loop

Hi There!

I need to execute a stored procedure for each row returned from a Select statement for now the only way for me to do it is by creating a cursor and loop thought the result and passing the parameters for the stored procedure and call it in the loop.

My question is. Is there any way I can put the 'execute [spStoredProcedure] param1, param2' with in the select statement so I won't need to create a cursor and manually call the stored procedure for each record?

Hi Lazer,

If you're using 2000, have you had a look at functions? EG:

select fn_MyFunction(a.Column1)

from MyTable

where something = something

Or, if using 2005, have you had a look at CROSS/OUTER APPLY?

select *

from MyTable a t

cross apply fn_MyFunction(t.Column1) as b

The difference being that cross/outer apply must be a table valued function...

Cheers

Rob

|||

Hi Rob,

i using 2000, i want to show a record set for user with the data only have monday date. even the date in database is not monday but i would like to take the early monday date. so can i have a sample how this loop function work?

select fn_MyFunction(a.dtanswerdate)

from answer

where something = something

if i want to update or change the date into monday date for display.

regards

terence chua

ExecuteNonQuery to count number of rows?

My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.

Does this also work when calling stored procedures and passing parameters?

I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.

What should I do?

Dim myCommandAs New SqlClient.SqlCommandmyCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@.selectDate", dateEntered)myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))myCommand.Connection = concon.Open()Dim rowCountAs Integer = myCommand.ExecuteNonQuery()numberParts.Text = rowCount.ToStringcon.Close()

Thank you.

Check yourap_Select_ModelRequests_RequestDateTimestored procedure, and if you find:

SET NOCOUNT OFF

useually at the end of SP, remove it and I hope this will work.

Good luck.

|||

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

|||

You can use @.@.ROWCOUNT in your stored procedure and assign it to an output parameter.

|||

Peter Lee:

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

Hi Pete.

What you wrote is correct, but I do not think it will work if the stored procedure has a SET NOCOUNT OFF?

Thanks.

|||

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

|||

Peter Lee:

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

You are right.

Thanks Pete.

ExecuteNonQuery in SQL 2005

I have an ASP.Net page that runs the following command which is giving me
the following SQL error:
The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The code in the page is as follows:
strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
Request.Form("cboFileTypeID") & ", 0"
cmd = New OleDbCommand(strSQL, conFileData)
lngRows = cmd.ExecuteNonQuery()
It is failing on the last statement and I don't know why. Also, below is
the stored proc code. Can anyone help? Thanks
David
CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
(
@.FileNumber int,
@.FileTypeID int,
@.Rows int output
)
AS
/* SET NOCOUNT ON */
INSERT INTO FileTasks
(FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
AssignedID)
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
RETURN @.RowsTry executing the stored procedure directly from query analyzer and see if
you get the same error?
"David" <dlchase@.lifetimeinc.com> wrote in message
news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> I have an ASP.Net page that runs the following command which is giving me
> the following SQL error:
> The statement has been terminated.
> Subquery returned more than 1 value. This is not permitted when the
subquery
> follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
> The code in the page is as follows:
> strSQL = "EXEC fd_insFileTaskDefaultsNew " & lngNextFile & ", " &
> Request.Form("cboFileTypeID") & ", 0"
> cmd = New OleDbCommand(strSQL, conFileData)
> lngRows = cmd.ExecuteNonQuery()
> It is failing on the last statement and I don't know why. Also, below is
> the stored proc code. Can anyone help? Thanks
> David
>
> CREATE PROCEDURE dbo.fd_insFileTaskDefaultsNew
> (
> @.FileNumber int,
> @.FileTypeID int,
> @.Rows int output
> )
> AS
> /* SET NOCOUNT ON */
> INSERT INTO FileTasks
> (FileNumber, TaskTitle, TaskDate, PrimaryID, NotifyDate, TaskNotes,
> AssignedID)
> SELECT @.FileNumber, TaskTitle,
> DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
> CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
> FROM FileTaskDefaults
> WHERE FileTypeID = @.FileTypeID
> RETURN @.Rows
>|||Yes, same error.
David
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Try executing the stored procedure directly from query analyzer and see if
> you get the same error?
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:uQdZVqTbGHA.1204@.TK2MSFTNGP04.phx.gbl...
> subquery
> expression.
>|||Is FileTaskDefaults a view?
Are there any triggers on FileTasks?
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
> Yes, same error.
> David
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uqtyUzTbGHA.3992@.TK2MSFTNGP05.phx.gbl...
>|||It doesn't look to me that your output parameter is ever assigned to
any value. Don't you need something like this
SELECT @.FileNumber, TaskTitle,
DATEADD(day, TaskDaysOut, GETDATE()), PrimaryID,
CONVERT(char(10), GETDATE(), 101), TaskNotes, AssignedID
FROM FileTaskDefaults
WHERE FileTypeID = @.FileTypeID
-- assign row count to @.Rows
SELECT @.Rows = @.@.Rowcount|||> RETURN @.Rows
Where does this value get populated? Do you want to use a RETURN, OUTPUT,
or both? I suggest sticking to output parameters for this kind of data, and
not using RETURN. RETURN is meant to return a status code (e.g.
success/failure), not data. This is one of the reasons they're limited to
INTeger datatypes.
A|||That was it! I removed the trigger and it worked. Thank you.
David
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23%236acDUbGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Is FileTaskDefaults a view?
> Are there any triggers on FileTasks?
> David
> "David" <dlchase@.lifetimeinc.com> wrote in message
> news:ugsFP3TbGHA.504@.TK2MSFTNGP03.phx.gbl...
>|||> That was it! I removed the trigger and it worked. Thank you.
I don't see how that is possible, unless either
(a) you didn't post all of the stored procedure code in your original post,
or
(b) you eliminated the error, but you aren't actually verifying that the
stored procedure is correctly returning the rowcount.
Anyway, it sounds like your trigger was written expecting only single-row
row modifications. You should re-visit that logic instead of just throwing
the trigger away, especially if the trigger is not yours and you are not
sure what it was doing.|||David (dlchase@.lifetimeinc.com) writes:
> That was it! I removed the trigger and it worked. Thank you.
And the trigger did not serve any purpose? Yeah, maybe it was just an
old relic, but I get nervous when I hear things like this. Just because
you did not get any error message, does not mean that it worked. If you
removed a trigger that performed some important task to maintain database
integrity, I would not call that working...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hopefully the OP removed to trigger temporarily just to confirm that was the
issue and is rewriting the trigger to correct the subquery.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B6D62F991A4Yazorman@.127.0.0.1...
> David (dlchase@.lifetimeinc.com) writes:
> And the trigger did not serve any purpose? Yeah, maybe it was just an
> old relic, but I get nervous when I hear things like this. Just because
> you did not get any error message, does not mean that it worked. If you
> removed a trigger that performed some important task to maintain database
> integrity, I would not call that working...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute stored procedure for each value returned in a query

I have a stored procedure that I am trying to execute passing it each value
returned in a query, but I cannot figure how to do it. I am trying to use
sp_executesql. Here is an example
Query:
(select distinct cust from customer_tbl)
Results:
Cust
--
Cust1
Cust2
Cust3
Cust4
Problem:
I need to execute a stored procedure for each Customer returned passing it
the Cust value. (SP_getsales @.cust='Cust1').
I cannot figure out how to do this. I can generate a script with
sp_executesql, but I cannot simply run the output directly back into isql or
sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot figur
e
it out.
Thanks,
--
JasonJasonDWilson wrote:
> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
>
If rewriting the procedure is an option and if the procedure just
executes some data manipulation code based on a parameter then just
substitute that parameter with a JOIN or IN operation (join to
customer_tbl in other words).
If you need help, please post DDL, sample data, required results.
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
--|||I would ask you to take a note before you read this example - try just
about ANY other option before using these, but in this case, it looks
like you would want a cursor:
DECLARE results cursor
FOR SELECT DISTINCT CUST FROM Customer_tbl
OPEN results
DECLARE @.CurrRow varchar(50)
FETCH NEXT FROM Results INTO @.CurrRow
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec sp_getsales @.cust = @.CurrRow
FETCH NEXT FROM Results INTO @.CurrRow
END
close results
DEALLOCATE Results
Cheers
Will|||Use a cursor...
declare blah cursor for
select distinct cust
from customer_tbl
declare @.cust varchar(50)
open blah
fetch next from blah into @.cust
while @.@.fetch_status = 0
begin
exec sp_getsales @.cust = @.cust
fetch next from blah into @.cust
end
deallocate blah
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45C58B26-57BB-4C6B-B0D1-D73473A970C1@.microsoft.com...
>I have a stored procedure that I am trying to execute passing it each value
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
> it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
> or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot
> figure
> it out.
> Thanks,
> --
> Jason|||try this
xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
varchar(20)) from customer_tbl'
hope this helps
--
"JasonDWilson" wrote:

> I have a stored procedure that I am trying to execute passing it each valu
e
> returned in a query, but I cannot figure how to do it. I am trying to use
> sp_executesql. Here is an example
> Query:
> (select distinct cust from customer_tbl)
> Results:
> Cust
> --
> Cust1
> Cust2
> Cust3
> Cust4
> Problem:
> I need to execute a stored procedure for each Customer returned passing
it
> the Cust value. (SP_getsales @.cust='Cust1').
> I cannot figure out how to do this. I can generate a script with
> sp_executesql, but I cannot simply run the output directly back into isql
or
> sqlcmd as input. Perhaps I need to rewrite my procedure, but I cannot fig
ure
> it out.
> Thanks,
> --
> Jason|||also, don't prefix your stored procedures sp_
read books online for reasons not to do this.|||Is this 2005 only?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>
value
use
passing it
isql or
figure|||small change :)
exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
database name should be in single quotes
hope this helps
--
"Omnibuzz" wrote:
> try this
> xp_execresultset 'select distinct ''SP_getsales @.cust= '' + cast(Cust1 as
> varchar(20)) from customer_tbl'
> hope this helps
> --
>
>
> "JasonDWilson" wrote:
>|||no its there in 2000. But it doesn't appear in the master database. its
undocumented and hidden :)
And after SP4, its no longer an extended stored proc, it just calls
sp_execresultset
--
"Jim Underwood" wrote:

> Is this 2005 only?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:6FBD5D4E-B4E2-4ED3-AB4E-FCEC9406990B@.microsoft.com...
> value
> use
> passing it
> isql or
> figure
>
>|||One note of caution. Don't use this proc unless you definitely have to. Cos
its undocumented. Its a definite no-no if you are planning to deploy it in
production. Typical use is for granting permissions to a set of users.
I would say its still better to use cursors if no one else comes with a
better solution.
But my suggestion would be to incorporate the looping logic inside the
stored procedure.
Hope this helps.
--
"Omnibuzz" wrote:
> small change :)
>
> exec master..xp_execresultset 'select distinct ''SP_getsales @.cust= '' +
> cast(Cust1 as varchar(20)) from customer_tbl', <database_name>
> database name should be in single quotes
> hope this helps
> --
>
>
> "Omnibuzz" wrote:
>

execute sql server agent job task - Job immediately returns success... However agent job is

when I run a package from a command window using dtexec, the job immediately says success.

DTExec: The package execution returned DTSER_SUCCESS (0).

Started:3:37:41 PM

Finished: 3:37:43 PM

Elapsed:2.719 seconds

However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.

Thanks,

Larry

Just to get this right. Your SSIS package starts a SQL Agent job. You are executing the package and it completes immediately. This is by design. There is no synchronous hook into the SQL Agent Job Scheduling, you can implement a waiting loop to check the job has completed but that is quite messy.

Execute Scalar ?

I'm trying to do something like the code below, but it's saying "specified cast is not valid"

If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.

protectedfloat getProjectHours(string project)

{

string selectCmd ="SELECT SUM(hours) FROM tasks WHERE project=@.project";

string strConnection =ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;

SqlConnection myConnection =newSqlConnection(strConnection);

SqlCommand myCommand =newSqlCommand(selectCmd, myConnection);

myCommand.Parameters.Add(newSqlParameter("@.project",SqlDbType.VarChar));myCommand.Parameters["@.project"].Value = project;

myConnection.Open();

float total = (float)myCommand.ExecuteScalar();

myConnection.Close();

return total;

}

Could you post the sql function please?

|||

what do mean? it's all up there

|||

If this is the line you are getting an error on, then try this:

double total = Convert.ToDouble(myCommand.ExecuteScalar());

|||

worked perfect! Thanks!

|||

you are welcome...

|||

Oh, sorry. Need more coffee...