Populate table with column retrieved from a stored procedure using SET processing

Question

Populate table with column retrieved from a stored procedure using SET processing

sherrie.kubis

SSC Veteran

Points: 266
More actions
June 27, 2017 at 1:39 pm

#402220

We are in a migration from Oracle to SQL Server, so I'm learning T-SQL.
The final problem will be more complicated than this, but I'm starting from a small test case,
so I appreciate the help.
Goal: Populate a target table of 4 columns. 3 columns come from another table, 1 column is derived from a (third-party) stored procedure. For performance reasons I want to use set processing, not cursors or iterative processing.
Source Table:  county_source
          county varchar(3)
          countyname varchar(12)
Target Table: county_target
         county varchar(3)
         countyname varchar(12)
         objectid int
The objectid for each row comes from a stored procedure, like so:
EXEC sde.next_rowid 'OWNER', 'COUNTY_TARGET', @objectid OUTPUT;
I need to insert into county_target with an objectid, where one of the columns come from the stored
procedure. The insert will be a select from county_source.
------------------------------
The code that I am trying is wrong as the stored procedure doesn't fire. Can someone offer a suggestion on the best way to accomplish this?
DECLARE @objectid as INT
declare @sql as varchar(100)
declare @owner as varchar(10) = 'SDECREATOR'
declare @table as varchar(25) = 'County_Target'
set @sql = 'exec sde.next_rowid, @owner, @table, ' + convert(varchar(5),@objectid) + 'OUTPUT';
INSERT INTO [SDECREATOR].[County_Target]
(OBJECTID, COUNTY, COUNTY_NAME)
      SELECT @sql,
                  COUNTY,
      COUNTY_NAME
FROM [SDECREATOR].[County_Source];
Thank you for the time and attention in advance,
Sherrie

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

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

ryanbesko Ten Centuries Points: 1364 More actions · Answer 1

set @sql = 'exec sde.next_rowid, @owner, @table, ' + convert(varchar(5),@objectid) + 'OUTPUT';

Here you are only setting the value for the @sql variable. The exec is in quotes so SQL Server is not actually executing it. You need to move the exec out of the quotes, i.e. EXEC @sql.

sherrie.kubis SSC Veteran Points: 266 More actions · Answer 2

How do I put that in the insert statement, sort of like it's a function (but it's not really). This isn't it:

DECLARE @objectid as INT
declare @sql as varchar(100)
declare @owner as varchar(10) = 'SDECREATOR'
declare @table as varchar(25) = 'County_Target'
set @sql = ' sde.next_rowid, @owner, @table, ' + convert(varchar(5),@objectid) + 'OUTPUT';

INSERT INTO [SDECREATOR].[County_Target]
(OBJECTID, COUNTY, COUNTY_NAME)
      SELECT exec @sql, -- I want to execute the sp here
                  COUNTY,
      COUNTY_NAME
FROM [SDECREATOR].[County_Source];

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

You can't call a stored procedure from within a query like that. You won't be able to wrap the procedure call in a function either. Since you said it's a third party procedure, you probably can't change it into an inline table valued function then. Also, since it's using an OUTPUT parameter, it will only be able to return 1 value at a time, so unfortunately if you must use this stored procedure then you would need a CURSOR. 🙁

sherrie.kubis SSC Veteran Points: 266 More actions · Answer 4

That's what I thought, but hoping I was missing something.

Thank you for the insights!

Sherrie

Thom A SSC Guru Points: 99130 More actions · Answer 5

Just as a side note, you can't use SET to set the value of multiple variables. You either need to use SET, and do one at a time, or use SELECT.

For example:
SET @Name = 'Steve'; SET @Age = 27;

SELECT @Name = 'John', @Age = 24;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sherrie.kubis SSC Veteran Points: 266 More actions · Answer 6

Thanks,I'll take a look at the guide, this is my first post. I just starting to move into the SQL Server world from Oracle.

Sherrie

sgmunson SSC Guru Points: 110640 More actions · Answer 7

sherrie.kubis - Wednesday, June 28, 2017 6:06 AM
Thanks,I'll take a look at the guide, this is my first post. I just starting to move into the SQL Server world from Oracle.
Sherrie

It might actually be fairly easy to create an SSIS package that could make use of the stored procedure. It could be the query behind a Lookup transformation. Use the source table as an OLE DB Source, and tie that to a Lookup transformation, which will then bring in the output of the stored procedure, and then tie that to an OLE DB Destination that points to the target table. Might be surprisingly easy to create. It would require Visual Studio 2015 with SQL Server Data Tools (aka SSDT) installed along with it. Integration Services would have to be running locally to be able to run the package locally (meaning a local SQL Instance on your machine), and would also need to exist on at least one SQL Server instance somewhere in your environment (ideally the target server). The elements I described would appear in a "Data Flow Task".

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sherrie.kubis SSC Veteran Points: 266 More actions · Answer 8

Thanks for the suggestion, Steve. I'll add it to my list of things to investigate.

Sherrie