t-sql 20082 -temp table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

t-sql 20082 -temp table

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-05-08 : 13:09:54

In an SSRS 2008 r2 report, the code currently obtains the results from the stored procedure and places the results in a temp table called #roomResults. See the sql below:

INSERT #roomResults
EXEC [StagingCA].[dbo].[spoom] @endYear

I want to only insert rows into the temp table called #roomResults where the parameter called @Customer matches the Customer
value in the temp table called #roomResults.

Basically I want to do something like

INSERT #roomResults
where @Customer= Customer --in the temp table.
EXEC [StagingCA].[dbo].[spoom] @endYear

Thus would you tell me how to modify the sql above so that the records for the @Customer value is entered?

If there is no such sql, would I need to create a second temp table to only select records where the value = @Customer?

In both cases, would you show me the sql that would solve my problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 13:37:18

You either need to modify spoom to include this feature or do as you said where you get the data from #roomresults that you want after the data has been inserted. If it's not too much data, you could delete the unwanted data.

SELECT TOP 1 @Customer = Customer FROM #roomResults

INSERT #roomResults
EXEC [StagingCA].[dbo].[spoom] @endYear

DELETE FROM #roomResults WHERE Customer <> @Customer

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-05-08 : 14:07:30

Thanks!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-08 : 14:22:21

I suggest adding an optional parameter to the "spoom" proc to allow optionally specifying the @customer along with @endYear. Then:

INSERT #roomResults
EXEC [StagingCA].[dbo].[spoom] @endYear , @customer = @customer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources