JOIN with the resultset from a stored procedure?

Question

JOIN with the resultset from a stored procedure?

Veronka

Default port

Points: 1425
More actions
April 23, 2010 at 10:32 am

#220800

Hi,
I have a stored procedure with 1 input parameter, in which I am using a dynamic PIVOT table. The pivot table inside the stored procedure is built dynamically, so I don't know how many columns the stored procedure will return.
The stored procedure could return no data set, depending on the value of the input parameter.
Now, I would like to call this stored procedure inside other stored procedures and join its result, if any, with some other tables.
Is this possible? Or maybe another approach to my problem?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Typically, you could create a temp table and run an INSERT INTO <tempTable> EXEC <YourProc>, but having to account for dynamic columns makes this approach not feasable.

You may consider either changing your SP or creating a new SP that returns the recordset back using on OUTPUT parameter of type XML. This would allow you to join to the XML doc and account for a dynamic number of columns.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ed B Hall of Fame Points: 3622 More actions · Answer 2

Can you include the join to the other tables in the proc?

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 3

You can do this using OPENROWSET. Take a look at Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Veronka Default port Points: 1425 More actions · Answer 4

I ended up using OPENROWSET, like this:

.....

DECLARE @SOIDs varchar(10);

SET @SOIDs = '192502';

-- must use dynamic query because passing params

declare @s-2 varchar(1000)

SET @s-2 = 'SELECT *

FROM

OPENROWSET

(

''SQLNCLI'',

''Database=SLX_EVAL;Uid=someuser;Pwd=pwd;'',

''SET FMTONLY OFF

EXEC [sysdba].[usp_RPT_GetProductLinesBySO_ID] ''''' + @SOIDs + ''''''') q

INNER JOIN #tmp t on t.SO_ID = q.SO_ID

'

EXEC(@s)

One issue which I run into:

If you usually log in as 'sa' and then setuser 'someuser' and try to run the above query, you will get an error: Access to the remote server is denied because the current security context is not trusted. Solution is to log in as 'someuser' and run the same query. It will work.

Thank you for your help !

Veronka Default port Points: 1425 More actions · Answer 5

Jeff,

I can run/call the stored procedure from Management Studio and from external application with no problem.

Now I would like to run this stored procedure from within a SQL job. How can I do that? The owner of the job is sa; when defining the steps, it does not allow me to choose sa or other DB login user (Steps--> Run as...)

Right now I am getting this error:

<domain>\service_sql. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

<domain>\service_sql is a domain account under which SQL Server Agent is running.

this is the script which I am trying to run within a job:

DECLARE @SalesStartDate datetime

DECLARE @SalesEndDate datetime

DECLARE @s-2 varchar(MAX)

SET @SalesStartDate = DATEADD(wk, -2, getdate())

SET @SalesEndDate = GETDATE()

SET @s-2 = 'exec TESTDB.testuser.usp_RPT_GetSalesByProductLines

@ProductLines=''Aspire|||Slim Lipo|||Aspire Energize'',

@StartDate = ''' + CONVERT(varchar(10), @SalesStartDate, 101) + ''',

@EndDate = ''' + CONVERT(varchar(10), @SalesEndDate, 101) + ''''

--print @s-2

DECLARE @Subject varchar(256)

SET @Subject = 'Slim Lipo Sales Between ' +

CONVERT(varchar(10), @SalesStartDate, 101) +

' and ' + CONVERT(varchar(10), @SalesEndDate, 101)

exec msdb.dbo.sp_send_dbmail

@recipients = 'veronka@company.com',

@subject = @Subject,

@body = @Subject,

@query =@s,

@query_result_separator = '', -- this is a tab

@attach_query_result_as_file = 1,

@query_result_no_padding = 1,

@query_attachment_filename ='SlimLipoRpt.csv'

Can you help me with this?

Thanks,

- Veronka

Jeff Moden SSC Guru Points: 1003888 More actions · Answer 6

How are you passing the parameters from the job to the proc? I believe that the information about service_sql is ancillary in nature and that the real problem has been cited as...

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Veronka Default port Points: 1425 More actions · Answer 7

If I run the query from Management studio logged as a trusted user (for example sa) it runs correctly.

Paul White SSC Guru Points: 150467 More actions · Answer 8

These sorts of issues with Agent Jobs are almost always down to the account context that the job is run under, or problems with impersonation. See:

Implementing SQL Server Agent Security

Creating SQL Server Agent Proxies

Selecting an Account for the SQL Server Agent Service

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi