Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to call a table as a variable Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 9:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
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?
Post #1442819
Posted Tuesday, April 16, 2013 10:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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)
Post #1442840
Posted Tuesday, April 16, 2013 10:35 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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





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)
Post #1442853
Posted Tuesday, April 16, 2013 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
It is also possible to make the synonym unique between invocations of the proc if needed.



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)
Post #1442856
Posted Tuesday, April 16, 2013 10:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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)
Post #1442861
Posted Tuesday, April 16, 2013 10:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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.



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)
Post #1442864
Posted Tuesday, April 16, 2013 11:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:21 PM
Points: 13,083, Visits: 11,918
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)
Post #1442874
Posted Tuesday, April 16, 2013 11:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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.



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)
Post #1442877
Posted Tuesday, April 16, 2013 11:36 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 206, Visits: 449
Thank you both. I was thinking dynamic SQL and that's the direction I'll go with.

Regards,

John
Post #1442889
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse