Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
somalia
Starting Member
10 Posts |
 Posted - 2009-10-10 : 05:49:09
|
| i want to pass tablename as a parameter to a stored procedure.can anybody give me an example how to do it?somalia |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-10 : 06:11:48
|
Look for dynamic sql in google
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-10-10 : 10:25:20
|
| There's little point in creating a stored procedure if you're going to use dynamic SQL to pull from an unknown table. |
|
|
|
nalnait
Starting Member
14 Posts |
Posted - 2009-10-11 : 07:31:11
|
| E.G:Create table Person(Id int primary key,FullName nvarchar(50) not null,)DECLARE @X VARCHAR(50)SET @X='Person'EXEC ('SELECT * FROM '+@X) |
|
|
|
somalia
Starting Member
10 Posts |
Posted - 2009-10-12 : 03:01:37
|
| ALTER PROCEDURE [dbo].[spmeterpop_sample] @tblname sysname,@Dte as varchar(50), @AppNo as varchar(50)asDECLARE @sql nvarchar(4000)SET @sql = 'Insert into NewTab([Appno],[saledate],[NDCS],[NDCSPoint])select AppNo,SaleDate,Case upper(NDCS)when ''YES'' then ''YES''else ''NO'' end as NDCS,Case upper(NDCS)when ''YES'' then 0.22else 0 end as NDCSPoint from '+@tblname+' where '+@tblname+'.SaleDate = '+@Dte+' and '+@tblname+'.Appno = '+@AppNo+''EXEC sp_executesql @sql This is my stored procedure.I am not getting an error while creating this procedure ,however when i am executing it by writing exec spmeterpop_sample @tblname='vwTBL_MASTER',@Dte='09/19/2009',@AppNo='A000000067'its giving an errorMsg 207, Level 16, State 1, Line 10Invalid column name 'A000000067'.I am not getting any clue where I am going wrong?somalia |
|
|
|
sanoj_av
Posting Yak Master
118 Posts |
Posted - 2009-10-12 : 04:16:44
|
| ALTER PROCEDURE [dbo].[spmeterpop_sample] @tblname sysname,@Dte as varchar(50), @AppNo as varchar(50)asDECLARE @sql nvarchar(4000)SET @sql = 'Insert into NewTab([Appno],[saledate],[NDCS],[NDCSPoint])select AppNo,SaleDate,Case upper(NDCS)when ''YES'' then ''YES''else ''NO'' end as NDCS,Case upper(NDCS)when ''YES'' then 0.22else 0 end as NDCSPoint from '+@tblname+' where '+@tblname+'.SaleDate = '''+@Dte+''' and '+@tblname+'.Appno = '''+@AppNo+''''EXEC sp_executesql @sql |
|
|
|
|
|
|
|
|
pass tablename as a parameter to a sp