0

Im trying a lot of combination but always having errors. (the Select/insert with replacing my variable in hard code is working outside of procedure)

Basically my proc have to do a dynamic select that receives a date variable as string and return a table as result and should insert it in other existing table. code removing part of the dynamic query(too big):

alter procedure [dbo].[SavePosition]
(@Refdate as varchar(15) =null)
as
begin
set NOCOUNT on
declare @YdDate as varchar(15)
declare @SQL AS NVARCHAR(max)

--To get prior date from @refdate(standalone select working well):
set @YdDate = (SELECT distinct max(TradeDate) from Trades where TradeDate < @Refdate)

--Sample of the query:
set @SQL='Select  ('''+ @Refdate +''') AS Refdate, @('''+ @YdDate +''') as Product from...join..having...group...';

INSERT INTO [dbo].[Position]([Refdate],[Product],...)
EXEC sp_executesql @sql
end
go

when I run below I receive a lot of: "The multi-part identifier "...." could not be bound."

exec [dbo].[SavePosition] '2014-12-10'
Flib
  • 165
  • 3
  • 14

1 Answers1

0

You need to store complete insert statement in @SQL variable. Please try like this.

set @SQL='INSERT INTO [dbo].Position Select ('''+ @Refdate +''') AS Refdate, @('''+ @YdDate +''') as Product from...join..having...group...';

EXEC sp_executesql @sql

Aarbi
  • 1
  • error: "Msg 102, Level 15, State 1, Line 436 Incorrect syntax near 'as'." what puzzles me in that my script even have 436 lines :/ – Flib Jan 07 '15 at 13:50