@FromDate within SET statement

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 2005 Forums

Transact-SQL (2005)

@FromDate within SET statement

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 07:10:11

I'm declaring @FromDate and @ToDate as the basis for my query to run, because these values can vary
DECLARE
@FromDate DATETIME,
@ToDate DATETIME,
@Profile VARCHAR (50),
@Select VARCHAR (7999),
@Join VARCHAR (7999),
@Where1 VARCHAR (7999),
@Where2 VARCHAR (7999),
@Group VARCHAR (7999)

SET @FromDate = '2010-10-15 00:00:00'
SET @ToDate = '2010-11-08 23:59:59'

Select * from fgSrcDoc

SET @Where1 = 'WHERE fgSrcDoc.DueDate BETWEEN @FromDate' AND @ToDate'

This is returning an error when I try to run. Am I actually able to declare these date parameters and set them in this manner?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 07:24:38

Maybe you can explain more clear what you want to do?
It looks like you are trying to generate a dynamic sql statement, but with the given information we can't help.

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-11-09 : 07:25:18

There's a few things here. When you want to concatenate a date in a string, you have to turn it into a string. Also, the dates must be enclosed by '

DECLARE
@FromDate VARCHAR (50),
@ToDate VARCHAR (50),
@Profile VARCHAR (50),
@Select VARCHAR (7999),
@Join VARCHAR (7999),
@Where1 VARCHAR (7999),
@Where2 VARCHAR (7999),
@Group VARCHAR (7999)

SET @FromDate = '2010-10-15 00:00:00'
SET @ToDate = '2010-11-08 23:59:59'''+' or 1=1'

SET @Where1 = 'WHERE fgSrcDoc.DueDate BETWEEN ''' +@FromDate+''' AND '+ ''''+@ToDate+''

SELECT @Where1

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 08:47:43

Why are you passing where condition as a parameter. This is subject to sql injection

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 08:51:22

Webfred here's a sample of what I'm trying to do:

DECLARE
@FromDate DATETIME,
@ToDate DATETIME,
@Profile VARCHAR (50),
@Select VARCHAR (7999),
@Join VARCHAR (7999),
@Where1 VARCHAR (7999),
@Where2 VARCHAR (7999),
@Group VARCHAR (7999)

SET @FromDate = '2010-10-15 00:00:00'
SET @ToDate = '2010-11-08 23:59:59'
SET @Profile = '10-BAC-3RD-GBP'

IF @Profile = '10-BAC-3RD-GBP'
BEGIN
SET @Join = '
FROM fgSrcDoc (NOLOCK)
LEFT OUTER JOIN fgSrcDocOverFlo (NOLOCK) ON fgSrcDocOverFlo.ObjectID = fgSrcDoc.SDOverAttsObjectID
INNER JOIN fdTradingPartne (NOLOCK) ON fdTradingPartne.ObjectID = fgSrcDoc.SrcDocCrtrOID
LEFT OUTER JOIN fdBankRole (NOLOCK) ON fdBankRole.ObjectID = fdTradingPartne.BankRoleObjectID
LEFT OUTER JOIN fdBnkCodSub (NOLOCK)ON fdBnkCodSub.ParentObjectID = fdBankRole.ObjectID AND fdBnkCodSub.ParentClassID = 11303 AND fdBnkCodSub.CollectionID = 1
INNER JOIN poPurchaseOrder (NOLOCK) ON poPurchaseOrder.PO = fgSrcDoc.ProcPONum
INNER JOIN poInvoice (NOLOCK) ON poInvoice.VenInvNum = fgSrcDoc.Description
INNER JOIN fdUser (NOLOCK) ON fdUser.ObjectID = poInvoice.LastEditorObjectID
INNER JOIN inReasonCode (NOLOCK) ON inReasonCode.ReasonCode = poInvoice.HoldReason
'
SET @Where2 = '
AND fgSrcDoc.SrcDocInstType = 0
AND fgSrcDoc.SrcDocType IN (0, 1, 2, 4, 5)
AND fgSrcDoc.Status = 2
AND fgSrcDoc.LifeCycleStatus <> 1
AND fgSrcDoc.DocAmtCur = ''GBP''
AND fdBankRole.DefBankAcc IS NOT NULL
AND fgSrcDoc.Recipient IN (''10'', ''14'')
AND fgSrcDoc.DispStatus IS NULL
AND fdTradingPartne.CommentsIsEmpty = 1
AND fgSrcDoc.IsPosted = 1
AND fdTradingPartne.TPType NOT IN (''10'', ''13'', ''14'', ''23'', ''50'')
AND (fgSrcDoc.OpenAmt > 0.0 OR fgSrcDoc.OpenAmt < 4999.0)
AND fdBnkCodSub.BankValue IS NULL
'
END

IF @Profile = '10-BAC-3RD-GBP-ANW'
BEGIN
SET @Join = '
FROM fgSrcDoc (NOLOCK)
LEFT OUTER JOIN fgSrcDocOverFlo (NOLOCK) ON fgSrcDocOverFlo.ObjectID = fgSrcDoc.SDOverAttsObjectID
INNER JOIN fdTradingPartne (NOLOCK) ON fdTradingPartne.ObjectID = fgSrcDoc.SrcDocCrtrOID
LEFT OUTER JOIN fdBankRole (NOLOCK) ON fdBankRole.ObjectID = fdTradingPartne.BankRoleObjectID
LEFT OUTER JOIN fdBnkCodSub (NOLOCK)ON fdBnkCodSub.ParentObjectID = fdBankRole.ObjectID AND fdBnkCodSub.ParentClassID = 11303 AND fdBnkCodSub.CollectionID = 1
INNER JOIN poPurchaseOrder (NOLOCK) ON poPurchaseOrder.PO = fgSrcDoc.ProcPONum
INNER JOIN poInvoice (NOLOCK) ON poInvoice.VenInvNum = fgSrcDoc.Description
INNER JOIN fdUser (NOLOCK) ON fdUser.ObjectID = poInvoice.LastEditorObjectID
INNER JOIN inReasonCode (NOLOCK) ON inReasonCode.ReasonCode = poInvoice.HoldReason
'
SET @Where2 = '
AND fgSrcDoc.SrcDocInstType = 0
AND fgSrcDoc.SrcDocType IN (0, 1, 2, 4, 5)
AND fgSrcDoc.Status = 2
AND fgSrcDoc.LifeCycleStatus <> 1
AND fgSrcDoc.DocAmtCur = ''GBP''
AND fdBankRole.DefBankAcc IS NOT NULL
AND fgSrcDoc.Recipient IN (''10'', ''14'')
AND fgSrcDoc.DispStatus IS NULL
AND fdTradingPartne.CommentsIsEmpty = 1
AND fgSrcDoc.IsPosted = 1
AND fdTradingPartne.TPType NOT IN (''10'', ''13'', ''14'', ''23'', ''50'', ''23A'')
AND fgSrcDoc.OpenAmt > 0.0
AND fdBnkCodSub.BankValue = ''MPP''
'
END

SET @Select = 'SELECT fgSrcDoc.NormDocAmt AS PaymentAmount,
fgSrcDoc.NormOpnAmt AS AmountDue,
fgSrcDoc.SrcDocCreator AS PayeeTradingPartner,
fgSrcDoc.Description AS InvoiceDescription,
poInvoice.EnteredDt, --New Field
fdTradingPartne.Description AS PayeeDescription,
fgSrcDoc.SrcDoc AS SourceDocument,
fgSrcDoc.DocDate AS SourceDocDate,
fgSrcDoc.ProcPONum AS PONumber,
fdTradingPartne.Description AS CreatorDescription,
fgSrcDoc.NormOpnAmtCur AS SDCurrency,
fgSrcDoc.NormOpnAmt AS PayableSDAmount,
fdTradingPartne.Description2 AS CreatorDescription2,
fdTradingPartne.Description2 AS PayeeDescription2,
fgSrcDoc.ProteanDoc,
fgSrcDoc.SrcDocType AS SourceDocumentType,
fgSrcDoc.DueDate AS TermDueDate,
fgSrcDoc.NormOpnAmtCur AS TermLineCurrency,
fgSrcDoc.SrcDocCreator,
poPurchaseOrder.ComplDt AS POLstCompleted,
fgSrcDoc.CreatCntct,
fdUser.UserID, --New Field
poInvoice.InvoiceRecDt, --New Field
poInvoice.HoldReason, --New Field
inReasonCode.Description AS ReasonDescription, --New Field
poInvoice.HoldRsnText --New Field
'

SET @Where1 = '
WHERE fgSrcDoc.DueDate BETWEEN ''' +@FromDate+''' AND '+ ''''+@ToDate+''
-- WHERE fgSrcDoc.DueDate BETWEEN ''2010-10-15 00:00:00'' AND ''2010-11-08 23:59:59''' --@FromDate & @ToDate need to be inserted in here

SET @Group = '
GROUP BY fgSrcDoc.CreatCntct,
fgSrcDoc.NormOpnAmt,
fgSrcDoc.NormDocAmt,
fgSrcDoc.SrcDocCreator,
fgSrcDoc.Description,
fgSrcDoc.DocDate,
fdTradingPartne.Description,
fgSrcDoc.SrcDoc,
fgSrcDoc.ProcPONum,
fdTradingPartne.Description,
poInvoice.EnteredDt,
fgSrcDoc.NormOpnAmtCur,
fgSrcDoc.NormOpnAmt,
fdTradingPartne.Description2,
fdTradingPartne.Description2,
fgSrcDoc.ProteanDoc,
fgSrcDoc.SrcDocType,
fgSrcDoc.DueDate,
fgSrcDoc.NormOpnAmtCur,
fgSrcDoc.SrcDocCreator,
poPurchaseOrder.ComplDt,
fdUser.UserID,
poInvoice.InvoiceRecDt,
poInvoice.HoldReason,
inReasonCode.Description,
poInvoice.HoldRsnText'

PRINT @Select + @Join + @Where1 + @Where2 + @Group
--EXEC (@Select + @Join + @Where1 + @Where2 + @Group)

This SP will be run by a Crystal Report so the report parameters will be FromDate, ToDate, Profile

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 08:53:00

I have 14 different Profiles and the where clauses differs for each profile the only section that is constant is:
SET @Where1 = '
WHERE fgSrcDoc.DueDate BETWEEN ''' +@FromDate+''' AND '+ ''''+@ToDate+''

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 08:53:03

You can't design a crystal report whose resultset comes from a dynamic sql

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 08:57:55

Madhivanan would you care to elaborate?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 09:08:56

quote:
Originally posted by eljapo4

Madhivanan would you care to elaborate?

If you create a procedure that uses dynamic sql to return data, it cannot be used to design the report. Try it

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 09:15:57

You're right, tried it and Crystal is telling me the 'fields can not be bound'. Are there any other methods that I can try?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 09:24:17

quote:
Originally posted by eljapo4

You're right, tried it and Crystal is telling me the 'fields can not be bound'. Are there any other methods that I can try?

Only method is not to pass where clause

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 09:47:43

But if I don't pass the where clause then the data being returned will be of no relevance for whichever profile is selcted

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-09 : 10:32:43

Write specific reports for each profile.

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-11-09 : 10:54:12