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/