I want to write stored procedure in visual studio that as a parameter recieves the name of project and runs in database TT and copies data from TT.dbo.LCTemp (where the LC is the name of the project recieved 开发者_StackOverflowas a parameter) table to "TT.dbo.Points" table. both tables have 3 columns: PT_ID, Projectname and DateCreated
I think I have written it wrong, here it is:
ALTER PROCEDURE dbo.FromTmpToRegular
@project varchar(10)
AS
BEGIN
declare @ptID varchar(20)
declare @table varchar(20)
set @table = 'TT.dbo.' + @project + 'Temp'
set @ptID = @table + '.PT_ID'
Insert into TT.dbo.Points Select * from [@table] where [@ptID] Not in(Select PT_ID from TT.dbo.Points)
END
Any idea what I did wrong?
Try using
EXEC ('Insert into TT.dbo.Points Select * from ' + @table + ' where ' + @ptID ' Not in(Select PT_ID from TT.dbo.Points)');
Or use parameters like this
Declare @SQL nVarChar(1000) --N.B. string must be unicode for sp_executesql
SELECT @SQL = 'SELECT * FROM pubs.DBO.Authors WHERE au_lname = @AuthorName'
Exec sp_executesql @SQL, N'@AuthorName nVarChar(50)', @AuthorName = 'white'
精彩评论