SQL Dynamic query returning date for a varchar variable

Question

This procedure should return the prior day based on @refdate.

If I only execute this select on DB it works perfectly, but something is wrong in my procedure code.

alter procedure [dbo].[SavePosition]
(@Refdate as varchar(15) =null)
as
begin try
set nocount on

declare @YdDate as varchar(15)

SELECT top 1 @YdDate = TradeDate from Trades where TradeDate < @Refdate ORDER BY TradeDate DESC;


declare @SQL AS VARCHAR(4000)

set @SQL='


Select  ('''+ @Refdate +''') AS Refdate, Prodname.name as Product, Inst.Name as Instrument,prodname.Id as prodId, Strat.Name as Strategy,Strat.Id as StratId, Pos.Tag, AssetGroup.name as AssetGroup, AssetClass.Name as AssetClass,

......[200 lines of code]..........

order by Strat.name,Prodname.Name
;'
EXEC(@SQL)
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
return

James S · Answer 1 · 2015-01-06T15:46:08.363

2

You don't need dynamic SQL for that... Converted to a function instead of a stored proc after your further comments.

CREATE FUNCTION [dbo].[teste1]
(@Refdate as varchar(15) =null)
RETURNS VARCHAR(15) AS
BEGIN
  declare @YdDate as varchar(15)

  SELECT TOP 1 @YdDate = TradeDate FROM Trades WHERE TradeDate < @Refdate 
  ORDER BY TradDate DESC;

  RETURN @YdDate
END

and then as an example usage SELECT dbo.teste('2014-12-10')

obviously this is only going to be reliable if your date strings are stored in format that can be sorted alphabetically (ie something like YYYYMMDD, or '20150106' )

edited Jan 06 '15 at 15:46

answered Jan 06 '15 at 14:56

James S

3,558
16
25

yes my date is yyyy-mm-dd. So where you are SET @YdDate? I need it to use as argument in other part of the code – Flib Jan 06 '15 at 14:59
1

I've used `SELECT TOP 1 @YdDate = ..` instead of a `SET @YdDate =`. SELECT instead of SET works just as well, and means you can simplify the statement. – James S Jan 06 '15 at 15:08
im receiving " Incorrect syntax near '2014' " when: exec [dbo].[teste1] '2014-12-10' – Flib Jan 06 '15 at 15:20
well there could easily be syntax statements later in the procedure - you only posted a stub rather than a full stored procedure. `EXEC(@YdDate)` is completely invalid for example. – James S Jan 06 '15 at 15:33
yes I did it and also tried return @YdDate , so how I finalize this Proc to return this YtDate value? tnks – Flib Jan 06 '15 at 15:35
RETURN can only be used with an INT inside a stored procedure. If you want to return a string for use by another stored procedure a function would be more appropriate than a stored procedure. I've updated the answer to show this as a function – James S Jan 06 '15 at 15:42
I think easy if I put my code more complete, I just edited. Note that '@Refdate' and '@YdDate' are both parameter of my '@SQL'. And my final Proc result is a table. – Flib Jan 06 '15 at 15:57

SQL Dynamic query returning date for a varchar variable

1 Answers1