parameters in FROM

Question

Post reply

parameters in FROM

jthomas-472158

SSC Rookie

Points: 25
More actions
December 20, 2007 at 4:07 pm

#181453

hi
how pass parameter in the clausula FROM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Can you please explain what you want with a bit more detail?

Please read the following post, and then try again. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

jthomas-472158 SSC Rookie Points: 25 More actions · Answer 2

thanks beforehand, I explain myself better.

Attempt to pass parametro in consultations in section FROM

for example:

SELECT campo1,campo2 FROM @TTABLA ORDER BY campo1

but it says to me that the object does not exist @TTABLA

can help me.

TK

Enterprise DBA SSC-Addicted Points: 478 More actions · Answer 3

You can use dynamic sql. I wouldn't recommend it though.

Dynamic sql opens your solution up to sql injection and hinders your ability to tune performance. My preference is to create a stored proc for each table accessed and force the decision of which one to run up to the calling method.

USE msdb

GO

DECLARE @MyTable nvarchar(255)

DECLARE @Query nvarchar(1000)

SET @MyTable = N'sysjobhistory'

SET @Query = N'SELECT TOP 100 * FROM ' + @Mytable

print @Query

EXEC sp_executesql @Query

What happens if the front end passes in ';GO TRUNCATE TABLE Customers'?

Dan

jthomas-472158 SSC Rookie Points: 25 More actions · Answer 4

thanks to respond. I detail a little but what attempt to do.

I create two cursors, first it passed the name of the tables

DECLARE Tablas_cursor CURSOR FOR

SELECT TABLE_NAME From INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME Like '%COBRANZA%' AND TABLE_NAME NOT LIKE '%H%'

AND TABLE_NAME NOT LIKE '%T%' AND TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_NAME

OPEN Tablas_cursor

FETCH NEXT FROM Tablas_cursor INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TABLE_NAME = rtrim(@TABLE_NAME)

SET @TABLE_NAMET = rtrim(@TABLE_NAME + 'T')

SET @TABLE_NAMEH = rtrim(@TABLE_NAME + 'H')

DECLARE Temp_cursor CURSOR FOR

SELECT record_id, contact_info, contact_info_type

FROM @TABLE_NAME ORDER BY record_id, NumContrato

OPEN Temp_cursor

FETCH NEXT FROM Temp_cursor INTO

@record_id ,@contact_info ,@contact_info_type

..... tec......

if you follow to me

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 5

Does your table list really change that often that you need to do this dynamically? It looks like you are trying to copy data from all tables that have a certain name into the table name appended with another character (perhaps for auditing or archival reasons?)

Sometimes I'll use the Information_Schema to generate a little script for me like

Select 'Insert INTO ' + Table_Name + 'H Select * From ' + Table_Name

from information_schema.tables

Where table_type = 'base table

I just take the output of this, paste it into a new window, and I can save it again for future use. Obviously, you can make your script a little more complicated than what this is, but I don't think you need cursors to transfer all data from one table to another...