April 16, 2013 at 9:44 am
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?
April 16, 2013 at 10:16 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 10:35 am
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
April 16, 2013 at 10:37 am
It is also possible to make the synonym unique between invocations of the proc if needed.
April 16, 2013 at 10:47 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 10:55 am
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.
April 16, 2013 at 11:07 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2013 at 11:10 am
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.
April 16, 2013 at 11:36 am
Thank you both. I was thinking dynamic SQL and that's the direction I'll go with.
Regards,
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply