Posted: 12 Aug 2013 01:59 PM PDT

When setting my @SQL statement to FROMSATXFPMetricsWHERE [Date] >= CONVERT(datetime,'''+ @StartDate +''',121)group by [Date]' --select @SQL--SELECT @SQL1--exec sp_executesql EXEC(N'sp_executesql N''' + @SQL + @SQL1 + '''')for example and calling the stored procedure exec ssrsSATXDailyFPTickets '6/1/2013' I get the incorrect syntax error near 6. Something is wrong with me adding my parameter. I can't figure out for the life of me what it is. I am assuming it has something to do with the apostrophes around my parameter or my syntax. I am sure it is something simple. Any ideas?

Posted: 12 Aug 2013 07:16 AM PDT

I walked into a project where they are re-writing old SQL 2000 code from the ground up but making little changes to the database structure (which is a mess). The original programmers had most of the SQL embedded in VB code. In order to populate their grids they would build a SQL statement in VB variables and pass it to the database to execute, returning a dataset. I want to re-write all this as stored procedures. They stored column information in a table and looped through with VB and built a dynamic query. I would like to do the same in T-SQL but want to avoid cursors. What is the best way to handle this? With a CTE?

How to find exact no. of months

Posted: 12 Aug 2013 09:18 AM PDT

How can i find the exact no. of months between two dates?[code="sql"]select DATEDIFF(dd, '2013-08-05 00:00:00.000', '2014-06-01 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-05 00:00:00.000', '2014-06-01 00:00:00.000')/30.0)select DATEDIFF(dd, '2013-08-01 00:00:00.000', '2014-07-30 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-01 00:00:00.000', '2014-07-30 00:00:00.000')/30.0)select DATEDIFF(dd, '2013-08-01 00:00:00.000', '2014-08-01 00:00:00.000'), CEILING(DATEDIFF(dd, '2013-08-01 00:00:00.000', '2014-08-01 00:00:00.000')/30.0)[/code]I came up with above but 363 & 365 are resulting in 13 months which is not what i want.

Syntax error in CTE

Posted: 12 Aug 2013 07:28 AM PDT

Hi, I have this query that is giving me a Incorrect syntax near the keyword 'SELECT' error It's referring to the very last 'SELECT' at the bottom.[code="sql"];WITH cte AS (select * from (SELECT rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code],pehIsVoided AS [IS Voided],pehIsVoidingRecord AS [Voiding Record],pehIsOvertime AS [Is Overtime]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME Contract Percentage] ON [org level 2] = pehOrgLvl2 --right outer join -- WSISQL4.DASHBOARD.DBO.[OVERTIME Contract Percentage] ob -- on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' AND pehearncode = '0001')SELECT * ,SUM([Current Hours]) OVER (PARTITION BY [Org Level 2]) AS [Org 2 Group]FROM cteorder by [current hours][/code]Works fine without the CTE part. For the life of me can't figure out what I'm doing wrong here.

Sql samples, sql tips

Tuesday, August 13, 2013

[T-SQL] Incorrect syntax near when using parameter in SET @SQL statement

[T-SQL] Incorrect syntax near when using parameter in SET @SQL statement

No comments:

Post a Comment

Search This Blog