SQLCMD - passing date parameter using -v

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQLCMD - passing date parameter using -v

Author

Topic

norman8
Starting Member

2 Posts

Posted - 2010-05-26 : 16:09:18

I am trying to pass the date into the query using the –v argument as seen below. I've tried numerous date formats as well as numerous combinations of single and double-quotes.

Many thanks!

From the command line:

sqlcmd -S server1 -d databasename -U SA -P password -h -1 -i \\server1\apx$\Auto\Prices.sql -o\\server1\apx$\auto\test.txt -v maxdate="20100524"

Here’s the query in a file called ‘prices.sql’

declare

@pricetypeid int,
@maxdate datetime,
@isheld bit,
@PriceMissing bit,
@IsManuallyPriced bit,
@FilterValue nvarchar(255),
@FilterType nvarchar(255)

Select

@pricetypeid=1,
--@maxdate='2010-05-24',
@isheld=1, --1 is held
@PriceMissing=0, --1 There is no price registered, 0 There is a price, null = any
@IsManuallyPriced=null,
@FilterValue=null,
@FilterType=null

Select 'test-12','li','',SecurityType, Symbol, '12311990','','',1,'','','','','','','','',1,'','','','','','','','','','','n',65533,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','y'
--Select SecurityType, Symbol, SecurityName, map.FromDate, sec.FromDate, ThruDate, PriceValue, MinDate, MaxDate, PriceMissing, IsHeld, PriceThruCode, PriceDate
From dbo.fQbRowDefPriceHistory(@PriceTypeID, @MaxDate) sec
join dbo.AdvPriceHistoryMap map on
map.SecurityID = sec.SecurityID

Where
(@IsHeld IS NULL OR (IsHeld IS NULL OR IsHeld = @IsHeld))
AND (@PriceMissing IS NULL OR (PriceMissing = @PriceMissing))
AND (@IsManuallyPriced IS NULL OR (IsManuallyPriced = @IsManuallyPriced))
AND ((@FilterValue IS NULL)
OR
( Symbol like Case When @FilterType ='Symbol' then @FilterValue + '%' end
OR SecurityName like Case When @FilterType ='SecurityName' then @FilterValue + '%' end
OR SecurityType like Case When @FilterType ='SecurityType' then @FilterValue + '%' end
OR PriceThruCode like Case When @FilterType ='PriceThruCode' then @FilterValue + '%' end))
and map.PriceDate = @maxdate
and map.PriceDate>map.FromDate

norman8
Starting Member

2 Posts

Posted - 2010-05-27 : 13:43:15

The issue has been resolved.

I added this to the query:
@maxdate = '$(maxdate)',

and in the SQLCMD command, the variable was changed to:
maxdate="2010-05-24"

Hope this helps someone in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources