Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to call a table as a variable


Need to call a table as a variable

Author
Message
latingntlman
latingntlman
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 521
I need to pull records from a table and that would be pretty simple, however, the select statement needs to run as a sproc that passes a variable input as the table name. Let me illustrate:

create procedure dbo.sproc

(@tablename varchar(10))

As

Select cust#, fname, lname... other fields
From tableA
Where Cust# in (Select Cust# from @Tablename)

the error is telling to declare the table variable, but if I declare it as table I have to include the fields and I dont want to. Does this work better with dynamic SQL? What's the best way to do this?
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18240 Visits: 17172
latingntlman (4/16/2013)
I need to pull records from a table and that would be pretty simple, however, the select statement needs to run as a sproc that passes a variable input as the table name. Let me illustrate:

create procedure dbo.sproc

(@tablename varchar(10))

As

Select cust#, fname, lname... other fields
From tableA
Where Cust# in (Select Cust# from @Tablename)

the error is telling to declare the table variable, but if I declare it as table I have to include the fields and I dont want to. Does this work better with dynamic SQL? What's the best way to do this?


You can't do this like you have coded. Dynamic sql is one way to accomplish this. This seems a little odd that your query needs to look at a different table for the list of customers based on a parameter. Maybe you have separate tables for each customer or something like that? Seems like maybe dynamic sql is probably the easiest way to do this.

something close to this:


declare @SQL nvarchar(max)

set @SQL = 'Select cust#, fname, lname... other fields
From tableA
Where Cust# in (Select Cust# from ' + @Tablename + ')'

select @SQL

--exec sp_executesql @statement = @SQL



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26466 Visits: 38125
latingntlman (4/16/2013)
I need to pull records from a table and that would be pretty simple, however, the select statement needs to run as a sproc that passes a variable input as the table name. Let me illustrate:

create procedure dbo.sproc

(@tablename varchar(10))

As

Select cust#, fname, lname... other fields
From tableA
Where Cust# in (Select Cust# from @Tablename)

the error is telling to declare the table variable, but if I declare it as table I have to include the fields and I dont want to. Does this work better with dynamic SQL? What's the best way to do this?



Or:



create procedure dbo.sproc(
@tablename sysname -- needs to be big enough to actually hold a table name
)

As

if object_id('TheTable','SN') is not null
DROP SYNONYM TheTable;
exec ('CREATE SYNONYM TheTable for ' + @tablename);

Select cust#, fname, lname... other fields
From tableA
Where Cust# in (Select Cust# from TheTable);

if object_id('TheTable','SN') is not null
DROP SYNONYM TheTable;

GO




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26466 Visits: 38125
It is also possible to make the synonym unique between invocations of the proc if needed.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18240 Visits: 17172
Lynn Pettis (4/16/2013)
It is also possible to make the synonym unique between invocations of the proc if needed.


I was wondering about concurrency when I read your reply. I like the idea of a synonym but can't figure out how you could make it unique without resorting to dynamic sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26466 Visits: 38125
Sean Lange (4/16/2013)
Lynn Pettis (4/16/2013)
It is also possible to make the synonym unique between invocations of the proc if needed.


I was wondering about concurrency when I read your reply. I like the idea of a synonym but can't figure out how you could make it unique without resorting to dynamic sql.



Yea, your right. I was looking at some of my code where this is actually embedded in some more code. Sorry.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18240 Visits: 17172
Lynn Pettis (4/16/2013)
Sean Lange (4/16/2013)
Lynn Pettis (4/16/2013)
It is also possible to make the synonym unique between invocations of the proc if needed.


I was wondering about concurrency when I read your reply. I like the idea of a synonym but can't figure out how you could make it unique without resorting to dynamic sql.



Yea, your right. I was looking at some of my code where this is actually embedded in some more code. Sorry.


Shoot!!! I was hoping there was something I just wasn't thinking of.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26466 Visits: 38125
Sean Lange (4/16/2013)
Lynn Pettis (4/16/2013)
Sean Lange (4/16/2013)
Lynn Pettis (4/16/2013)
It is also possible to make the synonym unique between invocations of the proc if needed.


I was wondering about concurrency when I read your reply. I like the idea of a synonym but can't figure out how you could make it unique without resorting to dynamic sql.



Yea, your right. I was looking at some of my code where this is actually embedded in some more code. Sorry.


Shoot!!! I was hoping there was something I just wasn't thinking of.


I was avoiding having to nest dynamic sql. Counting the proper number of single quotes can be quite tedious to say the least.
However, the use of smoke and mirrors is quite unique.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
latingntlman
latingntlman
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 521
Thank you both. I was thinking dynamic SQL and that's the direction I'll go with.

Regards,

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search