T-sql question

Question

Post reply

T-sql question

Betty Sun

SSCrazy

Points: 2442
More actions
September 6, 2006 at 12:45 pm

#119443

Hi everyone,
I have a store procedure and 3 parameters, @year1 char(4), @year2 char(4), and @Rflag char(1)
I want the store procedure pull different set up data based on the @Rflag(the idea is from Andrew's post the other day), when Rflag is 'D', need to pull data for agent number '12345', when Rflag is 'B' then I need to pull data for all other agents' sales except agent '12345', but display agentcode '66666' to represent all those agents. When @Rflag with any other value, I need to pull all sales from all agent (without agentCode condition), year1 and year2 just mean I need to pull those two years' sales etc.
So I test it first using the following statement:
Declare @Rflag char(1)
select @Rflag='D'
select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale,
CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End AS Agent from tis_vs
where (year(txndatetime)=2004 or year(txndatetime)=2005)
if @Rflag='D'
and agentCode='12345'
if @Rflag='B'
and agentCode<>'12345'
group by product, CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End
order by product, CASE WHEN agentcode = '12345' THEN agentCode ELSE '66666' End
but it keeps display the following error message:
Incorrect syntax near the keyword 'group'.
I guess I did the wrong way. Can you help?
Thank you.
Betty

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ganeshc27 SSC Eights! Points: 972 More actions · Answer 1

Try the below query...

DECLARE @RFLAG CHAR(1)

SET @RFLAG = 'D'

IF @RFLAG = 'D'

BEGIN

SELECT COUNT(PRODUCT) NUMPOL, PRODUCT,

SUM(CAST((ORDER_TOTAL/100.00) AS DECIMAL(12,2))) TXNSALE, AGENTCODE

FROM TIS_VS

WHERE (YEAR(TXNDATETIME)=2004 OR YEAR(TXNDATETIME)=2005)

AND AGENTCODE = '12345'

GROUP BY PRODUCT, NUMPOL, AGENTCODE

ORDER BY PRODUCT

END

ELSE IF @RFLAG = 'B'

BEGIN

SELECT COUNT(PRODUCT) NUMPOL, PRODUCT,

SUM(CAST((ORDER_TOTAL/100.00) AS DECIMAL(12,2))) TXNSALE, '66666' AS AGENTCODE

FROM TIS_VS

WHERE (YEAR(TXNDATETIME)=2004 OR YEAR(TXNDATETIME)=2005)

AND AGENTCODE <> '12345'

GROUP BY PRODUCT, NUMPOL, PRODUCT, AGENTCODE

ORDER BY PRODUCT

END

ELSE

BEGIN

SELECT COUNT(PRODUCT) NUMPOL, PRODUCT,

SUM(CAST((ORDER_TOTAL/100.00) AS DECIMAL(12,2))) TXNSALE, AGENTCODE

FROM TIS_VS

WHERE (YEAR(TXNDATETIME)=2004 OR YEAR(TXNDATETIME)=2005)

GROUP BY PRODUCT, NUMPOL, AGENTCODE

ORDER BY PRODUCT

END

Regards,

Ganesh

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 2

Use the following query in a dynamic way

Declare

@Rflag char(1)

select

@Rflag='B'

Declare

@sql nvarchar(4000)

set

@sql=N'select count(product) numPol, product, sum(Cast((order_total/100.00) As Decimal(12,2))) txnSale, ' +

'CASE WHEN agentcode = ''12345'' THEN agentCode ELSE ''66666'' End AS Agent ' +

'from tis_vs ' +

'where (year(txndatetime)=2004 or year(txndatetime)=2005) '+

'and ' + case @Rflag when 'D' then 'AgentCode =''12345''' when 'B' then 'AgentCode <> ''12345''' end +

' group by product, CASE WHEN agentcode = ''12345'' THEN agentCode ELSE ''66666'' End ' +

' order by product, CASE WHEN agentcode = ''12345'' THEN agentCode ELSE ''66666'' End '

exec

sp_executesql @sql

Betty Sun SSCrazy Points: 2442 More actions · Answer 3

Gopi,

Thank you so much for your post. I learned from you.

By the way, what is N for at the very beginning of your @sql variable.

I have been seen this N before and never know what it is.

Betty

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

N stands for unicode in a literal string.

Kind of like doing CAST('this text to ' AS nVarchar(4000))

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

Prefixing N is necessary in our problem

From BOL

Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. This is true even if the column being referenced is already defined as Unicode. Without the N prefix, the string is converted to the default code page of the database

Betty Sun SSCrazy Points: 2442 More actions · Answer 6

Thank you both. No wonder I saw some database setup script with a lot of N.

But I never figured out when we do need it and when we don't need it.

Betty

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I'm reading it but I don't see how this can be bad. Care to explain some more?

Betty Sun SSCrazy Points: 2442 More actions · Answer 8

GRG'us,

for example, does that make any difference if @sql declare as varchar(4000) instead of nvarchar(4000), when you use

exec sp_executesql @sql

will this dynamic sql statement works the same way as nvarchar(4000)?

Betty

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 9

It will not compile at all as sp_executesql expects 'N' in Parameter

Please read earlier reply (extract from BOL)

Betty Sun SSCrazy Points: 2442 More actions · Answer 10

Gopi,

got you. I didn't read the sp_executesql definition.

I am very appreciated your help.

Betty