EXECUTE AS question: row

Showing posts with label row. Show all posts

Friday, March 23, 2012

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!

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,

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

Wednesday, March 21, 2012

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly Big Smile |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:
It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68