Stored Procedure Dates - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure Dates

Author

Topic

Dbart
Starting Member

3 Posts

Posted - 2003-10-27 : 17:43:14

I have a stored procedure that needs to be dynamically generated. The syntax to allow @BeginDate & @EndDate to be read as a date has got me stumped. The error is "Syntax error converting datetime from character string" Please help!!

CREATE PROCEDURE GetSirSummaryM
(
@BeginDate DateTime,
@EndDate DateTime,
@Market Nvarchar(50)
)
AS

DECLARE @SQL VARCHAR(1000)
DECLARE @WHERE_CLAUSE VARCHAR(1000)
DECLARE @ORDER_CLAUSE VARCHAR(1000)

SET @SQL = 'Select Count(IncidentID) as Incidents,ShopNumber,ShopName,Market_name,Sum(LossAmount) as Losses From Capstar_sir '

SET @WHERE_CLAUSE = CASE
WHEN @Market = 'All' then
' WHERE OccuranceDate >= ' + @BeginDate + ' and OccuranceDate < ' + @EndDate
ELSE
' WHERE OccuranceDate >= ' + @BeginDate + ' and OccuranceDate < ' + @EndDate + ' and Market_name = ' + @Market
END

SET @ORDER_CLAUSE = 'Group by Market_name,ShopNumber,ShopName Order by ShopName'

SET @SQL = @SQL + @WHERE_CLAUSE + @ORDER_CLAUSE

PRINT @SQL
EXEC(@SQL)
GO

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-27 : 18:22:23

Try using convert(varchar(8), @BeginDate, 112)

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

homam
Starting Member

31 Posts

Posted - 2003-10-27 : 18:33:42

Make sure you convert to string and have single quotes around the the date in the resulting dymanic SQL string:

'Where OccurrenceDate >= ''' + convert(varchar(25), @BeginDate, 120) + ''' and OccurrenceDate < ''' + @convert(varcahr(25), @EndDate, 120) + ''' '

homam
Starting Member

31 Posts

Posted - 2003-10-27 : 18:35:10

Oops, forget the @ before convert :)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-10-28 : 08:46:58

If that is all that query is required to do, you do not need dynamic SQL, you could write both queries and test @market with an IF...ELSE. Put it in a stored procedure and execute feeding the same parameters.

Just a thought.

Dbart
Starting Member

3 Posts

Posted - 2003-10-28 : 09:28:37

Thank you. I am new to this and I appreciate your help.
Diane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources