Calling at Stored Proc (DHL)

I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)I'm confused...

You say "Have an SQL code" in your post, does that mean that you have a Transact-SQL script? This is something that you'd run using either OSQL.EXE, SQLCMD.EXE, or one of the windows tools that allows you to execute SQL scripts.

If not, what language are you using? C++ is very different from VB, and neither of them is much like Perl... The way that you access the database engine is radically different in each of these languages.

Coach us a bit, and I'm sure that someone can help you!

-PatP|||Pat, Here is part of my code:
Select
m.member
m.member_no
s.Flag

FROM member_table as M

LEFT JOIN Status_table as S
ON S.member_no = M.member_no

Before I can use the Status Table, a stored proc need to process to update the table. I want to start the stored proc to update the table then run the code. Is there a way to add in code to kick off the stored proc then when it's done updating the Status_Table finish to rest of my code.
Thanks for the help, David|||I'm obviously not asking the correct question, so I'll try again. What language are you using to write the progam that the user will execute? Can you show me a snippet of code around where you think you want to put the SELECT statement from your last post?

-PatP|||Running on SQL Server 2005. Both using basic SQL code. The stored Prod process the follwoing Code:
Stored Proc:
TRUNCATE TABLE dbo.Status_table

-- Y flag
INSERT INTO dbo.Status_table

SELECT
M.Member_no,
'Y' AS 'FLAG'
FROM CMC_MEPE AS M

WHERE M.MEPE_ELIG_IND = 'Y'
AND M.MEPE_EFF_DT <= GETDATE()
AND M.MEPE_TERM_DT >= GETDATE()

This code is a stored Proc that I need to run to update table.
This stored proc is not scheduled. I need to update the Status_table before I run the report.

This is the SQL code for my report.
Select
m.member
m.member_no
s.Flag

FROM member_table as M

LEFT JOIN Status_table as S
ON S.member_no = M.member_no

Is there a way to get the Stored Proc to run so the table is updated before I run the code for the report. All this take place on the Sql Server.
Thank you,
David|||Ah, that was the point that I needed!

Yes, simply use something like:EXECUTE myProcedure...and you should be on your way!

-PatP|||Thank you, David|||Pat, Can I call (or execute) this stored prod from a view. Is there a way when you are creating the view to have the code to execute the stored prod as part of the SQL I am using for the view. Thank you David|||No, views do not support code execution for a number of reasons, although Microsoft does reserve the right to change that in future versions of the product (probably via a new type of trigger).

-PatP|||Just an observation, but a stored procedure can be a data source, so you could create a new procedure that executed whatever procedures that you needed to execute, then performed a SELECT operation to return data to the caller of your procedure. This is logically the same as a view that executes code, it is just a different way of implementing the process.

-PatPsql

call report with datas updated

Hello,
I use sql reporting services to show reports with url and to create snapshot with his web services.
My problem is the following:
- i show a report (linked to a database with data source and dataset) with an url like :
http://srv/Reportserver/BOURGOGNE/Perso&rs:Format=HTML4.0
&rs:Command=Render&annee=2004&id=0580639E
- i change data in the database
- when a recall the report with the previous url, the data has not changed, and i must click on refresh button next the export button.
- i would like that this refresh made automatically when i call a report. for more precisions, the property/execution on this report is set to 'Render this report with the most recent data' with '' 'Do not cache temporary copies of this report'... so i don't understantd..
thank to help me and sorry for my english...
floben.

It seems like you are rendering from session. On your URL above, append the following argument: rs:ClearSession=true

This will make sure the report gets reprocessed entirely.
See also: http://msdn2.microsoft.com/ms224719

-- Robert

|||thank, it was that..