SSIS and Stored procedures using temp tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Michael Cape » SSIS and Stored procedures using temp tables

21 posts, Page 2 of 3

««1 2 3 »»

SSIS and Stored procedures using temp tables

Rate Topic

Display Mode

Topic Options

Author

Message

SJTerrill

Posted Monday, December 29, 2008 10:20 AM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 28, 2012 12:28 PM
Points: 458, Visits: 718

Raymond,

the IF block is used to place a 'unexecuted' SELECT at the beginning of the stored procedure. Placing the conditional test in a WHERE clause without the IF block will return a result... albeit an empty one. The technique described in the article is being used to 'trick' the SSIS OLE DB provider into using the first SELECT it sees, while allowing the stored procedure to ignore the same SELECT when it runs.

Clear as mud?

--SJT--

Post #626659

raymond lew

Posted Monday, December 29, 2008 12:06 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 27, 2009 10:01 AM
Points: 48, Visits: 123

In Reply to STJ, you're right in that I left out the enclosing block that made the contract SQL ninja-like. I was just suggesting a way to cast off all the NULLs in the contract SQL; reading the datatypes from the db instead hardcoding them. It would be easier to maintain and easier to code for slackers like me.

FROM:
IF 1 = 2
BEGIN
SELECT CAST(NULL AS INT) AS ContactID
,CAST(NULL AS NVARCHAR(50)) AS FirstName
, CAST(NULL AS NVARCHAR(50)) AS MiddleName
, CAST(NULL AS NVARCHAR(50)) AS LastName
, CAST(NULL AS NVARCHAR(10)) AS Suffix
, CAST(NULL AS NVARCHAR(50)) AS EmailAddress
END

TO:
IF 1 = 2
BEGIN
SELECT ContactID
, FirstName
, MiddleName
, LastName
, Suffix
, EmailAddress
FROM Person.Contact
where 2 = 4
END

Post #626738

sginn

Posted Monday, December 29, 2008 7:06 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 11:04 AM
Points: 3, Visits: 52

Would this not work and be a little less wacky?

Create table #Contact
(contactID int,
firstName Nvarchar(50),
MiddleName Nvarchar(50),
Lastname Nvarchar(50),
Suffix Nvarchar(50),
EmailAddress Nvarchar(50))

INSERT INTO #Contact
Select ContactID, Firstname, MiddleName,
LastName, Suffix, EmailAddress
FROM Person.Contact

Post #626953

Michael Cape

Posted Thursday, January 01, 2009 6:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 8, Visits: 297

Yes. The alternative select statement directly from the table would work as well for this example. However, in some circumstances the CAST(NULL AS ...) may be needed. Just remember that the NULL must be cast to a desired datatype, otherwise I think SQL Server will default the datatype to INT.

Post #628546

Michael Cape

Posted Thursday, January 01, 2009 6:31 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 8, Visits: 297

For this simplistic example the alternative you suggest would probably work. However, on the job the stored proc probably won't be as simple. At my job we frequently use intermediate temp tables in the process of building a final result. This means that there are SELECT statements near the beginning of the procedure which do not echo the final SELECT statement.

Post #628548

Brian Pond

Posted Friday, January 09, 2009 5:43 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:47 PM
Points: 41, Visits: 31

Thanks, Michael. I had to apply your workaround today. I've got a parameterized stored procedure returning hundreds of thousands of rows. It needs a temporary table for performance reasons; table variables just don't cut it, due to lack of indexes. For the same reason, executing this procedure 5 times isn't very appealing either. So the 1=2 workaround is perfect. SSIS doesn't appear to know the difference, so long as the fields and datatypes are identical. Awesome!

Post #634021

mayurshendge1

Posted Tuesday, February 10, 2009 10:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 6:36 AM
Points: 2, Visits: 16

Hi,
Thanks for this good article. it really helpful for newcomer in ssis. I was facing problem with columns when I was trying to call stored procedure in ssis.
once againg thank you.

regards,
mayur

Post #654420

dujunbruce

Posted Thursday, February 19, 2009 12:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:03 AM
Points: 1, Visits: 34

Thanks for the research. This helps me to avoid having to create and drop a physical table in the database to provide the "contract" for DTSPipeline.

I prefer to using a table variable for the output of procedure. For me the first thing to do in this sort of stored procedure is to declare table a table viarable for output following by your "Wacky" solution. After this setup, you can still use temp tables in your procedure.

Declare @tblOutput Table (
...
)

If 1=2
Begin
Select * from @tblOutput
End

Post #660698

Jeff Moden

Posted Tuesday, February 02, 2010 7:46 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 PM
Points: 32,910, Visits: 26,800

Micheal,

I know you wrote this article a while ago but I wanted to say "thanks". Today is my first "real" day with SSIS and it's nice to see that SSIS has some "whacky" features like T-SQL does. Neat stuff and a nicely written article. Thanks!

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #858369

dindonsquat

Posted Tuesday, November 23, 2010 3:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 16, 2011 8:41 AM
Points: 1, Visits: 2

Thanks for this nice article, it solve my problem !
I used variable tables in place of temp tables => do not forget "SET NOCOUNT ON" at the beginning of the stored procedure.

Thanks again !!!!

Post #1024992

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions