SQL behaving wrongfull - 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

General SQL Server Forums

New to SQL Server Programming

SQL behaving wrongfull

Author

Topic

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-09-29 : 15:12:50

This SQL is supposed to return a week of data so that the Report will display one week. Instead, the Report is showing 2 columms. The first column shows one day before the start-date that I enter, and the second column shows one day before the end date that I enter, i.e. if I enter 04/01/09 - 04/07/09 the Report is displaying 0ne column with a heading of 03/31/09 and the second of 04/06/09, where it should be displaying one column for the week, the heading can be the first date shown. I have it as SET because it will be a stored procedure, that is used by the Interface:

SET @SQL = 'SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity, DATEADD(WK, DATEDIFF(WK, 0, SOP10100.DOCDATE), 0) AS ID,

SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSC

FROM dbo.SOP10200 AS SOP10200 INNER JOIN

dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN

dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN

dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN

dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE

Where SOP10100.DOCDATE between ''' + @StartDate + ''' and ''' + @EndDate + ''''

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 17:06:15

you shouldn't be using dynamic sql

Read my hint in my sig and post what it ask for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Adam West
Constraint Violating Yak Guru

261 Posts

Posted - 2009-09-29 : 17:07:39

Have using this because it is in a stored proc.

This way, i can update it easily. Also it is used both by the REport Manager Reports as well as by the interface that is tied in to the RM.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-29 : 17:27:12

sounds like you are operating under some misunderstandings. A stored procedure does not need to use dynamic sql, you can modify an SP not using dynamic sql as readily as you can one that uses dynamic sql, and any SP, whether it uses dynamic sql or not, can be used by multiple applications and/or reporting systems.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources