August 29, 2015 at 11:59 am
Hi All,
I have a stored procedure for SSRS report. I would like to call this stored procedure inside another SQL select statement from another report. This stored procedure pulls multiple records for another report based on three input parameters. I have used EXEC (stored procedure name) with the three parameters inside the SQL select statement but doesn't work. I know I can call a function inside select statement to get values from the function. How do you achieve this calling a stored procedure? I cannot post the code here. I just need some examples how to achieve this.
Thank you.
August 29, 2015 at 3:41 pm
You could use INSERT/EXECUTE (requires the precreation of the table to store the results of the stored procedure in) or you could use sanitized inputs to create a dynamic call through OPENROWSET.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2015 at 7:34 pm
Could you provide some examples?
Thank you.
August 29, 2015 at 9:04 pm
ITU_dk2012 (8/29/2015)
Could you provide some examples?Thank you.
INSERT INTO <table> (<columnlist>)
EXECUTE <procedurename> <parameters>;
INSERT INTO <table> (<columnlist>)
SELECT <columnlist> FROM OPENROWSET (...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2015 at 7:15 pm
Thank you so much for the example. Could you please explain what comes in the OPENROWSET?
August 31, 2015 at 7:26 pm
ITU_dk2012 (8/31/2015)
Thank you so much for the example. Could you please explain what comes in the OPENROWSET?
You define a connection and execute a query. What's returned from your query depends on what you write.
It's documented at https://msdn.microsoft.com/en-us/library/ms190312%28v=sql.100%29.aspx.
August 31, 2015 at 11:15 pm
the insert into the table from the executed stored procedure will work, done it before, just make sure the returned columns match your table's columns, or you'll fail on the insert.
Regarding the OpenQuery for returning a recordset for your insert - it can get tricky I've seen where people have actually made the query from the openquery be an insert statement, if you are able to use linked servers I'd recommend it, otherwise you are going to have troubleshoot a lot more regarding the openquery
September 1, 2015 at 7:39 pm
Okay so you want to kick off an SSRS report that apparently has two datasets that you capture (are you combining these two datasets? or showing them separately?). The one is using Stored Proc and the the other is an openquery or open rowset,
I'm presuming these two data sources are separate servers, even if they aren't, the Stored Procedure seems the way to go since you know it will work - may sound like a dumb question but why not use the stored procedure for both of the dataset calls? Can you not used a stored proc for the openquery that you were trying to do?
September 2, 2015 at 3:19 am
ITU_dk2012 (9/1/2015)
@Ed WagnerThank you so much for the link.
I am not using any linked servers so not sure what to use. I recall that I have used OPENROWSET before but no luck.
Is there anything else I can use instead of OPENROWSET?
Thank you all.
Im not 100% sure this would work when querying in SSRS as I havn't wrote any reports for a long time but, using sp_executesql may give you the desired results but the syntax is a little more complex.
Below is a script i put together to call a replication validation stored procedure for every publication on a server which would give you some ideas on how to utilise it:
SET NOCOUNT ON
GO
-- Check if the temp table already exists, if it does drop it.
IF OBJECT_ID('tempdb..#MyPubs') IS NOT NULL
DROP TABLE #MyPubs
--Populate the temp table with Publications and Articles
SELECTDISTINCT p.Publication,
a.article AS Article,
a.publisher_db AS Publisher
INTO #MyPubs
FROM Distribution..msarticles a (NOLOCK)
JOIN Distribution..mssubscriptions s (NOLOCK) ON a.publication_ID = s.publication_ID
JOIN Distribution..mspublications p (NOLOCK) ON s.publication_ID = p.publication_ID
where s.subscriber_db<>'virtual'
GROUP BY a.article, a.publisher_db, s.subscriber_db, p.Publication, a.source_object, a.destination_object
ORDER BY a.article
-- Create variables
DECLARE @pub_db sysname,
@pub varchar(255),
@art varchar(max),
@cmd nvarchar(4000),
@procname varchar(255),
@Parameters nvarchar(1000),
@ReturnResult bit
-- Check if the temp table already exists, if it does drop it.
IF OBJECT_ID('tempdb..#ValidationResults') IS NOT NULL
DROP TABLE #ValidationResults
CREATE TABLE #ValidationResults (
Publisher varchar(255),
Publicationvarchar(255),
articlevarchar(255),
ValidationSuccessfull bit
)
--Declare the cursor using the temp table as an input
--!!!!not all cursors are bad!!!!, calling a procedure cannot be done
--in a set based manner, hence an iterative loop is required.
DECLARE cur_published CURSOR FOR
SELECT Publisher, Publication, Article
FROM #MyPubs
--Open the cursor to begin processing the rows added to the cursor in the code above.
OPEN cur_published
FETCH NEXT FROM cur_published INTO @pub_db, @pub, @art
WHILE @@FETCH_STATUS = 0
BEGIN
--For the current publication \ article generate the dynamic sql command for executing
-- sp_article_validation, its sp_execute SQL so the result can be returned to variable for adding to the
-- results table, the result message cannot be captured to the same table due to the design of the proc
-- by Microsoft themselves.
SELECT @procname = @pub_db + '..sp_article_validation'
-- !!!!!! IMPORTANT !!!! - In this line change the 3rd and 4th Parameters from 2,2 to 1,1
-- If performance is poor, these are the parameter values for @rowcount_only and @full_or_fast respectively.
SELECT @cmd = 'exec @ReturnResultOUT = ' + @procname + ' @pubIn, @artIn, 2,2,0,0'
SELECT @Parameters = '@pubIn varchar(255), @artIn varchar(255), @ReturnResultOUT bit OUTPUT'
EXECUTE sp_executesql @cmd, @Parameters, @pubIn = @pub, @artIn = @art, @ReturnResultOut = @ReturnResult OUTPUT
--Lastly load the entire result set to #ValidationResults for the procedure call
--Include the publisher \ publication \ article and Message so you have all the necessary info.
--AS sp_article_validation returns 0 for OK and 1 for their being issues I have flipped these for reporting in the log table.
INSERT INTO #ValidationResults
SELECT @pub_db, @pub, @art, CASE WHEN @ReturnResult = 0 THEN 1 ELSE 0 END
--Fetch the next article \ publication from the list to validate.
FETCH NEXT FROM cur_published INTO @pub_db, @pub, @art
END
CLOSE cur_published
DEALLOCATE cur_published
GO
-- Check how validation went, for failures only add WHERE ValidationSuccessFull = 0
SELECT * FROM #ValidationResults
--WHERE ValidationSuccessFull = 0
MCITP SQL 2005, MCSA SQL 2012
September 17, 2015 at 8:12 pm
Sorry for my late reply. Thank you so much for the code example. I will try to test your code and will let you know.
Thanks.
September 27, 2015 at 9:31 pm
did you try to execute that stored procedure you like to call so you can check if the stored procedure really executing?...
i'm assuming that the stored proc you like to call execute a script that put data into table and you like to put the data on to the main stored proc?
exec [other stored procname]
insert into tmptable(fields)
select fields from [other stored procname].tablename
insert into tmptable(fields)
select fields from tablename
select * from tmptable
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply