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 stored procedure (with parameters) with an "exec" command

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@.id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@.parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.It's DBNull.Value|||Try:

sqlPar = new SqlParameter("@.parent_id", DBNull.Value)|||Perfect, it works, but now I have another problem.
I get this error:
This SqlTransaction has completed; it is no longer usable.

When I run Commit or Rollback code.|||Are you committing the transaction twice accidentally?|||No, I'm sure.
The only "strange" thing I do it's to call 2 different functions in my class to begin and commit/rollback transaction.|||Then you will have to show some code...|||'CConn is my class
CConn.BeginTransaction()

Dim arParameters as new ArrayList()

Dim sqlParOutput = new SqlParameter("@.id", SqlDbType.Int)
sqlParOutput.Direction = ParameterDirection.Output
arParameters.Add(sqlParOutput)

Dim sqlPar = new SqlParameter("@.parent_id", DBNull.Value)
arParameters.Add(sqlPar)

objTransaction = CConn.ExecuteNonQuery("sp", arParameters, false)

if objTransaction is nothing then objTransaction = CConn.ExecuteNonQuery("...")

if objTransaction is nothing then
CConn.CommitTransaction()
else
CConn.RollbackTransaction()
end if

The Begin, Commit and Rollback functions in CConn class simply call the same functions of SqlConnection object.
The ExecuteNonQuery function override standard function.|||This helps sort of not at all. We do not know what your class is doing. Is the return from CConn.ExecuteNonQuery a transaction object? What is the significance of objTransaction being nothing?|||Sorry, you're right.
Here the class methods:

 Public function BeginTransaction() as SqlTransaction
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 'Start a local transaction
 Me.myTransaction = Conn.BeginTransaction()
 end function
 Public sub CommitTransaction()
 Me.myTransaction.Commit()
 Me.Conn.Close()
 end sub
 Public function RollbackTransaction() 'transaction as SqlTransaction)
 Me.myTransaction.Rollback()
 Me.Conn.Close()
 end function
 Public function ExecuteNonQuery(ByVal SQL As String, optional sqlParameters as ArrayList = nothing, optional toClose as boolean = true) as object
 Dim objReturn as object
 if Me.Conn is nothing then
 Me.Conn = New SqlConnection(Me.sConnStr)
 Me.Conn.Open()
 else
 if Me.Conn.State <> ConnectionState.Open then Me.Conn.Open()
 end if
 Dim sqlCmd As New SqlCommand(SQL, Me.Conn)
 'Must assign transaction object to Command object for a pending local transaction
 if not Me.myTransaction is nothing then sqlCmd.Transaction = Me.myTransaction
 Try
 if not sqlParameters is nothing then
 sqlCmd.CommandType = CommandType.StoredProcedure
 Dim sqlPar as SqlParameter
 for each sqlPar in sqlParameters
 sqlCmd.Parameters.Add(sqlPar)
 next
 else
 sqlCmd.CommandType = CommandType.Text
 end if
 sqlCmd.ExecuteNonQuery()
 Catch e As Exception
 objReturn = e
 End Try
 'must be clean up?
 if toClose then
 Me.Conn.Close()
 sqlCmd.Dispose()
 Me.Conn.Dispose()
 end if
 return objReturn
 End function

Execute SQL Task passing parameters to a restore command

Hi,

I'm very new to SSIS and I’m trying to do the following in a SQL task

RESTORE DATABASE @.DatabaseName FROM DISK = @.Backup WITH FILE = 1, MOVE @.OldMDFName TO @.NewMDFPath, MOVE @.OldLDFName TO @.NewLDFPath, NOUNLOAD, REPLACE, STATS = 10

I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@.DatabaseName". How can i get my values to be substituted into the command?

Many thanks

Martin

The best way is to build your SQL statement in a variable via expressions and then use that variable as the SQL source for the Execute SQL task.|||I don't think you can parameterize a RESTORE DATABASE command like that.

The way I'd do it is to create a new parameter named SqlStatement or something of similar meaning. This parameter's EvaluateAsExpression property will be True and the Expression property will be: "RESTORE DATABASE " + @.[User::DatabaseName] + " FROM DISK = ...". Then set the SQLSourceType of your Execute SQL Task to Variable and specify SqlStatement as the variable.

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.

Execute SQL Task - Passing Variables

how can you pass variables from one 'Execute SQL Task' to another?

Not sure I'm getting your whole scenario, but I'll take a stab at it.

Define a variable in SSIS at the package level. It will be accessible from all Execute SQL tasks.

If you are asking how to retrieve a value in one Execute SQL Task and use it in another, you still need to do the step above. In the first Execute SQL, set the Resultset type to Single Row on the General page of the task, and on the Result Set page, specify 0 as the Result Name (assuming you want the first column of your SQL statement), and specify the variable you created under Variable name.

In the second Execute SQL, you can map the variable to a parameter in the SQL statement by using the Parameter Mapping page.

Let me know if that answered your question.

|||

Using SQL Server 2000:

In a DTS Pkg...

Execute SQL Task:

I'm doing an update query.

I would like to define a couple variables, say, @.error and @.Pkg

Then run an insert query, catch an error (if there is one)

...

if @.@.Error then

set @.Error = @.@.Error

...

Then, have a second Execute SQL Task that runs on failure

Exec xp_sendmail

...

@.message = 'there was an error' & @.Error & ' in pkg' & @.Pkg

or something to that effect.

I can do all this in an activex script, but was wondering if there is a simpler way (e.g. through the execute sql task)

|||Wish I could help you, but it's been a while since I have done any DTS. This is an SSIS forum - you might have better results posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

If you were doing this in SSIS, you would do this through precedence constraints, an user variable to hold the error code, and the system:: PackageName variable.

|||

I tried your scenario (in SSIS) and receive the error message in the second Execute SQL Task:

Executing the query "DELETE FROM [Order] WHERE (order_date > @.StartDate)" failed with the following error: "Must declare the scalar variable "@.StartDate".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

@.StartDate is a Date derived from the variable User :: StartDate which is a DateTime

Could you give a step by step example of how to do this.

|||

If you are using an OLEDB connection, use ? instead of @.StartDate in the query.

Here's more info on using the Execute SQL Task - http://www.sqlis.com/58.aspx

|||

That worked...how do I access mulitple variables?

|||

Use multiple ?. Like:

Code Snippet

INSERT INTO table VALUES(?, ?, ?)

Then map each parameter on the Parameter Mapping page. Use 0 for the first param, 1 for the second, etc.