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)
 Dynamic SQL - using sp_executesql

Author  Topic 

thanvi
Starting Member

11 Posts
Posted - 2007-08-28 : 15:02:46
I created the following stored procedure to avoid dynamic sql and it compile fine but throws error on execution:

CREATE PROCEDURE [TestProc]
@MerchantID as varchar(8)='',
@BeginDate as varchar(25)= '',
@EndDate as varchar(25)= '',
@TransType as varchar(2)= 'CD',
@CardType as varchar(3)= '',
@Status as varchar(1)= '',
@Amount as varchar(15)= '',
@ClientID as varchar(6)= '',
@TransID as varchar(15)= '',
@RefID as varchar(50)= '',
@AccountName as varchar(30)= '',
@EncryptAccountNo as varchar(30)= '',
@Source as varchar(2)= '',
@UserID as varchar(6)= '',
@orderby as varchar(30)='',
@ascdesc as varchar(4)=''

as

SET NOCOUNT ON
declare
@sqlString as nvarchar(4000),
@ParmDefinition nvarchar(4000),
@MerchantID_1 INT,
@ClientID_1 INT,
@TransID_1 INT,
@UserID_1 INT,
@BeginDate_1 SMALLDATETIME,
@EndDate_1 SMALLDATETIME,
@Amount_1 MONEY,
@TransType_1 varchar(2),
@AccountName_1 varchar(100)

if @MerchantID = '' Set @MerchantID_1 = null else Set @MerchantID_1 = CONVERT(INT, @MerchantID)
if @BeginDate = '' Set @BeginDate_1 = null else Set @BeginDate_1 = CONVERT(SMALLDATETIME, @BeginDate)
if @EndDate = '' Set @EndDate_1 = null else Set @EndDate_1 = CONVERT(SMALLDATETIME, @EndDate)
if @Amount = '' Set @Amount_1 = null else Set @Amount_1 = CONVERT(MONEY, @Amount)
if @ClientID = '' Set @ClientID_1 = null else Set @ClientID_1 = CONVERT(INT, @ClientID)
if @TransID = '' Set @TransID_1 = null else Set @TransID_1 = CONVERT(INT, @TransID)
if @RefID = '' Set @RefID = null
if @AccountName = '' Set @AccountName_1 = null else set @AccountName_1 = '%' + @AccountName + '%'
if @EncryptAccountNo = '' Set @EncryptAccountNo = null
if @Source = '' Set @Source = null
if @UserID = '' Set @UserID_1 = null else Set @UserID_1 = CONVERT(INT, @UserID)
if @TransType = 'CD' Set @TransType_1 = null else set @TransType_1 = @TransType


set @sqlString = N'select MerchantID,TransID,UserID,ClientID,InvoiceID,PostedDate,SettledDate,NextProcessDate,Amount,TipAmount,Type,Status, '
set @sqlString = @sqlString + N'AccountName,AccountNo,RefID,ResubmitCount,WalletID, Action = CASE WHEN TYPE = ''CC'' AND STATUS = ''C'' AND (AMOUNT > '
set @sqlString = @sqlString + N'(SELECT isnull(Sum(Amount),0) FROM bDetail b with(nolock) WHERE b.TransID=a.TRANSID and amount is not null and '
set @sqlString = @sqlString + N'Status in(''P'',''C'',''A'',''S'')) THEN ''C'' END from aDetail a with (nolock) where '
set @sqlString = @sqlString + N'MerchantID = ISNULL(@pMerchantID, MerchantID) and '
set @sqlString = @sqlString + N'PostedDate BETWEEN ISNULL(@pBeginDate, PostedDate) AND ISNULL(@pEndDate, PostedDate) and '
set @sqlString = @sqlString + N'type = ISNULL(@pTransType, type) and '
set @sqlString = @sqlString + N'case when @status = ''S'' then status in (''S'',''A'') end and '
set @sqlString = @sqlString + N'Amount = ISNULL(@pAmount, Amount) and '
set @sqlString = @sqlString + N'ClientID = ISNULL(@pClientID, ClientID) and '
set @sqlString = @sqlString + N'TransID = ISNULL(@pTransID, TransID) and '
set @sqlString = @sqlString + N'RefID = ISNULL(@pRefID, RefID) and '
set @sqlString = @sqlString + N'EncryptAccountNo = ISNULL(@pEncryptAccountNo, EncryptAccountNo) and '
set @sqlString = @sqlString + N'Source = ISNULL(@pSource, Source) and '
set @sqlString = @sqlString + N'UserID = ISNULL(@pUserID, UserID) and '
set @sqlString = @sqlString + N'AccountName like ISNULL(@pAccountName, AccountName) and '
set @sqlString = @sqlString + N'Case when @CardType = 3 then left(accountNo,1) = ''3'' and len(accountno) = 15 '
set @sqlString = @sqlString + N'when @CardType = 30 then left(accountNo,1) = ''3'' and len(accountno) = 14 '
set @sqlString = @sqlString + N'when @CardType = 300 then left(accountNo,1) = ''3'' and len(accountno) = 16 '
set @sqlString = @sqlString + N'when @CardType = 45 then left(accountNo,1) in (''4'',''5'') and len(accountno) = 15 '
set @sqlString = @sqlString + N'when @CardType = 4 then left(accountNo,1) = ''4'' '
set @sqlString = @sqlString + N'when @CardType = 5 then left(accountNo,1) = ''5'' '
set @sqlString = @sqlString + N'when @CardType <> '''' then left(accountNo,1) = ''6'' end '
set @sqlString = @sqlString + N'order by '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''asc'' THEN PostalCode END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''desc'' THEN PostalCode END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''asc'' THEN clientid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''desc'' THEN clientid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''asc'' THEN transid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''desc'' THEN transid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''asc'' THEN refid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''desc'' THEN refid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''asc'' THEN AccountNo END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''desc'' THEN AccountNo END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''asc'' THEN settleddate END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''desc'' THEN settleddate END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''asc'' THEN amount END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''desc'' THEN amount END desc '

--print @sqlstring

SET @ParmDefinition = N' @pMerchantID int, @pBeginDate smalldatetime, @pEndDate smalldatetime, @pTransType varchar(2), '
SET @ParmDefinition = @ParmDefinition + N' @pAmount money, @pClientID int, @pTransID int, @pRefID int, @pEncryptAccountNo varchar(30), '
SET @ParmDefinition = @ParmDefinition + N' @pSource varchar(2), @pUserID int, @pAccountName varchar(100), @CardType varchar(3), '
SET @ParmDefinition = @ParmDefinition + N' @OrderBy varchar(30), @ascdesc varchar(4)'

EXEC sp_executesql @sqlString, @ParmDefinition, @MerchantID_1, @BeginDate_1, @EndDate_1, @TransType_1, @Amount_1, @ClientID_1, @TransID_1, @RefID,
@EncryptAccountNo, @Source, @UserID_1, @AccountName_1, @orderby, @ascdesc

SET NOCOUNT OFF
GO

I am getting the following error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'THEN'.

Any help would be appreciated in having this stored proc fixed.

Thanks in advance.
Thanvi

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-28 : 15:08:07
Do a PRINT @SqlString and see what the output gives.
It should be fairly eady to see what the error is.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

thanvi
Starting Member

11 Posts
Posted - 2007-08-28 : 15:14:18
The following is what I got:

select MerchantID,TransID,UserID,ClientID,InvoiceID,PostedDate,SettledDate,NextProcessDate,Amount,TipAmount,Type,Status, AccountName,AccountNo,RefID,ResubmitCount,WalletID, Action = CASE WHEN TYPE = 'CC' AND STATUS = 'C' AND (AMOUNT > (SELECT isnull(Sum(Amount),0) FROM bDetail b with(nolock) WHERE b.TransID=a.TRANSID and amount is not null and Status in('P','C','A','S')) THEN 'C' END from aDetail a with (nolock) where MerchantID = ISNULL(@pMerchantID, MerchantID) and PostedDate BETWEEN ISNULL(@pBeginDate, PostedDate) AND ISNULL(@pEndDate, PostedDate) and type = ISNULL(@pTransType, type) and case when @status = 'S' then status in ('S','A') end and Amount = ISNULL(@pAmount, Amount) and ClientID = ISNULL(@pClientID, ClientID) and TransID = ISNULL(@pTransID, TransID) and RefID = ISNULL(@pRefID, RefID) and EncryptAccountNo = ISNULL(@pEncryptAccountNo, EncryptAccountNo) and Source = ISNULL(@pSource, Source) and UserID = ISNULL(@pUserID, UserID) and AccountName like ISNULL(@pAccountName, AccountName) and Case when @CardType = 3 then left(accountNo,1) = '3' and len(accountno) = 15 when @CardType = 30 then left(accountNo,1) = '3' and len(accountno) = 14 when @CardType = 300 then left(accountNo,1) = '3' and len(accountno) = 16 when @CardType = 45 then left(accountNo,1) in ('4','5') and len(accountno) = 15 when @CardType = 4 then left(accountNo,1) = '4' when @CardType = 5 then left(accountNo,1) = '5' when @CardType <> '' then left(accountNo,1) = '6' end order by CASE WHEN @OrderBy = 'posteddate' AND @ascdesc = 'asc' THEN PostalCode END CASE WHEN @OrderBy = 'posteddate' AND @ascdesc = 'desc' THEN PostalCode END desc CASE WHEN @OrderBy = 'clientid' AND @ascdesc = 'asc' THEN clientid END CASE WHEN @OrderBy = 'clientid' AND @ascdesc = 'desc' THEN clientid END desc CASE WHEN @OrderBy = 'transid' AND @ascdesc = 'asc' THEN transid END CASE WHEN @OrderBy = 'transid' AND @ascdesc = 'desc' THEN transid END desc CASE WHEN @OrderBy = 'refid' AND @ascdesc = 'asc' THEN refid END CASE WHEN @OrderBy = 'refid' AND @ascdesc = 'desc' THEN refid END desc CASE WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'asc' THEN AccountNo END CASE WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'desc' THEN AccountNo END desc CASE WHEN @OrderBy = 'accountname' AND @ascdesc = 'asc' THEN accountname END CASE WHEN @OrderBy = 'accountname' AND @ascdesc = 'desc' THEN accountname END desc CASE WHEN @OrderBy = 'settleddate' AND @ascdesc = 'asc' THEN settleddate END CASE WHEN @OrderBy = 'settleddate' AND @ascdesc = 'desc' THEN settleddate END desc CASE WHEN @OrderBy = 'status' AND @ascdesc = 'asc' THEN accountname END CASE WHEN @OrderBy = 'status' AND @ascdesc = 'desc' THEN accountname END desc CASE WHEN @OrderBy = 'amount' AND @ascdesc = 'asc' THEN amount END CASE WHEN @OrderBy = 'amount' AND @ascdesc = 'desc' THEN amount END desc


quote:
Originally posted by Peso

Do a PRINT @SqlString and see what the output gives.
It should be fairly eady to see what the error is.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-08-28 : 15:24:01
And you didn't see anythig obvious?
select		MerchantID,
		TransID,
		UserID,
		ClientID,
		InvoiceID,
		PostedDate,
		SettledDate,
		NextProcessDate,
		Amount,
		TipAmount,
		Type,
		Status,
		AccountName,
		AccountNo,
		RefID,
		ResubmitCount,
		WalletID,
		Action =	CASE
					WHEN TYPE = 'CC' AND STATUS = 'C' AND (AMOUNT > (SELECT isnull(Sum(Amount),0) FROM bDetail b with(nolock) WHERE b.TransID=a.TRANSID and amount is not null and Status in('P','C','A','S')) THEN 'C'
				END
from		aDetail a with (nolock)
where		MerchantID = ISNULL(@pMerchantID, MerchantID)
		and PostedDate BETWEEN ISNULL(@pBeginDate, PostedDate) AND ISNULL(@pEndDate, PostedDate)
		and type = ISNULL(@pTransType, type)
		and	case
				when @status = 'S' then status in ('S','A')
			end
		and Amount = ISNULL(@pAmount, Amount)
		and ClientID = ISNULL(@pClientID, ClientID)
		and TransID = ISNULL(@pTransID, TransID)
		and RefID = ISNULL(@pRefID, RefID)
		and EncryptAccountNo = ISNULL(@pEncryptAccountNo, EncryptAccountNo)
		and Source = ISNULL(@pSource, Source)
		and UserID = ISNULL(@pUserID, UserID)
		and AccountName like ISNULL(@pAccountName, AccountName)
		and	Case
				when @CardType = 3 then left(accountNo,1) = '3' and len(accountno) = 15
				when @CardType = 30 then left(accountNo,1) = '3' and len(accountno) = 14
				when @CardType = 300 then left(accountNo,1) = '3' and len(accountno) = 16
				when @CardType = 45 then left(accountNo,1) in ('4','5') and len(accountno) = 15
				when @CardType = 4 then left(accountNo,1) = '4'
				when @CardType = 5 then left(accountNo,1) = '5'
				when @CardType <> '' then left(accountNo,1) = '6'
			end
order by	CASE
			WHEN @OrderBy = 'posteddate' AND @ascdesc = 'asc' THEN PostalCode
		END
		CASE
			WHEN @OrderBy = 'posteddate' AND @ascdesc = 'desc' THEN PostalCode
		END desc
		CASE
			WHEN @OrderBy = 'clientid' AND @ascdesc = 'asc' THEN clientid
		END
		CASE
			WHEN @OrderBy = 'clientid' AND @ascdesc = 'desc' THEN clientid
		END desc
		CASE
			WHEN @OrderBy = 'transid' AND @ascdesc = 'asc' THEN transid
		END
		CASE
			WHEN @OrderBy = 'transid' AND @ascdesc = 'desc' THEN transid
		END desc
		CASE
			WHEN @OrderBy = 'refid' AND @ascdesc = 'asc' THEN refid
		END
		CASE
			WHEN @OrderBy = 'refid' AND @ascdesc = 'desc' THEN refid
		END desc
		CASE
			WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'asc' THEN AccountNo
		END
		CASE
			WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'desc' THEN AccountNo
		END desc
		CASE
			WHEN @OrderBy = 'accountname' AND @ascdesc = 'asc' THEN accountname
		END
		CASE
			WHEN @OrderBy = 'accountname' AND @ascdesc = 'desc' THEN accountname
		END desc
		CASE
			WHEN @OrderBy = 'settleddate' AND @ascdesc = 'asc' THEN settleddate
		END
			CASE WHEN @OrderBy = 'settleddate' AND @ascdesc = 'desc' THEN settleddate
		END desc
		CASE
			WHEN @OrderBy = 'status' AND @ascdesc = 'asc' THEN accountname
		END
		CASE
			WHEN @OrderBy = 'status' AND @ascdesc = 'desc' THEN accountname
		END desc
		CASE
			WHEN @OrderBy = 'amount' AND @ascdesc = 'asc' THEN amount
		END
		CASE
			WHEN @OrderBy = 'amount' AND @ascdesc = 'desc' THEN amount
		END desc
1) All the CASES in the WHERE statement
2) All ORDER BY's is missing a comma to separate them


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-08-28 : 15:46:44
How exactly would this procedure that creates dynamic SQL help you avoid dynamic SQL, even if you did create a valid SQL statement?





CODO ERGO SUM
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-28 : 16:44:24
quote:
Originally posted by Michael Valentine Jones

How exactly would this procedure that creates dynamic SQL help you avoid dynamic SQL, even if you did create a valid SQL statement?


CODO ERGO SUM


Exactly. You have two choices:

1) dynamically build a clean, efficient and exact SQL statement that does exactly what you need

or

2) use a static but complicated SQL statement that uses CASE expressions to return results dynamically

Both techniques have pros and cons. Somehow, though, you have managed to combine the drawbacks of BOTH techniques by dynamically building an efficient, complicated SQL statement...

Choose option (1) or (2), don't try to do both.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

thanvi
Starting Member

11 Posts
Posted - 2007-08-28 : 17:33:37
I dont need a comma in the order by because the order by is done only on one column.

The problem that I had is with the Case statment.
case
when @status = 'S' then status in ('S','A')
end

this should be something like

status in( case when @status = 'S' then 'S','A' )

but it is giving an error.

if i try something like this
status = case when @status = 'S' then 'S'
then it works fine.

But I have to check for both 'S' and 'A' and if @status ='' it should not be included in the query.



quote:
Originally posted by Peso

And you didn't see anythig obvious?
select		MerchantID,
		TransID,
		UserID,
		ClientID,
		InvoiceID,
		PostedDate,
		SettledDate,
		NextProcessDate,
		Amount,
		TipAmount,
		Type,
		Status,
		AccountName,
		AccountNo,
		RefID,
		ResubmitCount,
		WalletID,
		Action =	CASE
					WHEN TYPE = 'CC' AND STATUS = 'C' AND (AMOUNT > (SELECT isnull(Sum(Amount),0) FROM bDetail b with(nolock) WHERE b.TransID=a.TRANSID and amount is not null and Status in('P','C','A','S')) THEN 'C'
				END
from		aDetail a with (nolock)
where		MerchantID = ISNULL(@pMerchantID, MerchantID)
		and PostedDate BETWEEN ISNULL(@pBeginDate, PostedDate) AND ISNULL(@pEndDate, PostedDate)
		and type = ISNULL(@pTransType, type)
		and	case
				when @status = 'S' then status in ('S','A')
			end
		and Amount = ISNULL(@pAmount, Amount)
		and ClientID = ISNULL(@pClientID, ClientID)
		and TransID = ISNULL(@pTransID, TransID)
		and RefID = ISNULL(@pRefID, RefID)
		and EncryptAccountNo = ISNULL(@pEncryptAccountNo, EncryptAccountNo)
		and Source = ISNULL(@pSource, Source)
		and UserID = ISNULL(@pUserID, UserID)
		and AccountName like ISNULL(@pAccountName, AccountName)
		and	Case
				when @CardType = 3 then left(accountNo,1) = '3' and len(accountno) = 15
				when @CardType = 30 then left(accountNo,1) = '3' and len(accountno) = 14
				when @CardType = 300 then left(accountNo,1) = '3' and len(accountno) = 16
				when @CardType = 45 then left(accountNo,1) in ('4','5') and len(accountno) = 15
				when @CardType = 4 then left(accountNo,1) = '4'
				when @CardType = 5 then left(accountNo,1) = '5'
				when @CardType <> '' then left(accountNo,1) = '6'
			end
order by	CASE
			WHEN @OrderBy = 'posteddate' AND @ascdesc = 'asc' THEN PostalCode
		END
		CASE
			WHEN @OrderBy = 'posteddate' AND @ascdesc = 'desc' THEN PostalCode
		END desc
		CASE
			WHEN @OrderBy = 'clientid' AND @ascdesc = 'asc' THEN clientid
		END
		CASE
			WHEN @OrderBy = 'clientid' AND @ascdesc = 'desc' THEN clientid
		END desc
		CASE
			WHEN @OrderBy = 'transid' AND @ascdesc = 'asc' THEN transid
		END
		CASE
			WHEN @OrderBy = 'transid' AND @ascdesc = 'desc' THEN transid
		END desc
		CASE
			WHEN @OrderBy = 'refid' AND @ascdesc = 'asc' THEN refid
		END
		CASE
			WHEN @OrderBy = 'refid' AND @ascdesc = 'desc' THEN refid
		END desc
		CASE
			WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'asc' THEN AccountNo
		END
		CASE
			WHEN @OrderBy = 'AccountNo' AND @ascdesc = 'desc' THEN AccountNo
		END desc
		CASE
			WHEN @OrderBy = 'accountname' AND @ascdesc = 'asc' THEN accountname
		END
		CASE
			WHEN @OrderBy = 'accountname' AND @ascdesc = 'desc' THEN accountname
		END desc
		CASE
			WHEN @OrderBy = 'settleddate' AND @ascdesc = 'asc' THEN settleddate
		END
			CASE WHEN @OrderBy = 'settleddate' AND @ascdesc = 'desc' THEN settleddate
		END desc
		CASE
			WHEN @OrderBy = 'status' AND @ascdesc = 'asc' THEN accountname
		END
		CASE
			WHEN @OrderBy = 'status' AND @ascdesc = 'desc' THEN accountname
		END desc
		CASE
			WHEN @OrderBy = 'amount' AND @ascdesc = 'asc' THEN amount
		END
		CASE
			WHEN @OrderBy = 'amount' AND @ascdesc = 'desc' THEN amount
		END desc
1) All the CASES in the WHERE statement
2) All ORDER BY's is missing a comma to separate them


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-08-28 : 18:23:59
quote:
Originally posted by thanvi

I dont need a comma in the order by because ...


What you need is to build a SQL statement with correct syntax.

You don't seem to know the correct way to construct or use a CASE statement or an ORDER BY clause or a WHERE clause. I'm not even sure what you are trying to do.




CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-08-29 : 07:09:17
You could probably make this bit easier to maintain:

set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''asc'' THEN PostalCode END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''desc'' THEN PostalCode END desc '

could become

set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''asc'' THEN PostalCode END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''desc'' THEN PostalCode END desc '

with

IF @OrderBy = 'posteddate' SET @sqlString = @sqlString + N' PostalCode ' + @ascdesc

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-29 : 11:28:47
thanvi -- why are you asking for help, yet ignoring our advice?

This entire section:

quote:

set @sqlString = @sqlString + N'order by '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''asc'' THEN PostalCode END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''desc'' THEN PostalCode END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''asc'' THEN clientid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''desc'' THEN clientid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''asc'' THEN transid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''desc'' THEN transid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''asc'' THEN refid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''desc'' THEN refid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''asc'' THEN AccountNo END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''desc'' THEN AccountNo END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''asc'' THEN settleddate END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''desc'' THEN settleddate END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''asc'' THEN amount END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''desc'' THEN amount END desc '


can be replaced with simply:

set @sqlstring = @sqlstring + N'ORDER BY ' + @OrderBy + N' ' + @ascdesc

That's it!

It's is not only shorter, easier to read and edit and work with, but also much more efficient.

You should validate that the @ascdesc and @orderby values are valid first, but otherwise that's all you need.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-08-29 : 11:34:28
"can be replaced with simply"

I think I already said that and "not quite" as @OrderBy sometimes does not contain the ColumnName, but something that defines it instead

That in itself suggests to me some head-scratching during future maintenance!

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-29 : 12:12:25
quote:
Originally posted by Kristen

"can be replaced with simply"

I think I already said that and "not quite" as @OrderBy sometimes does not contain the ColumnName, but something that defines it instead

That in itself suggests to me some head-scratching during future maintenance!

Kristen


Ah, you did ... either way, what a mess!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-08-29 : 12:17:04
Go to Top of Page

thanvi
Starting Member

11 Posts
Posted - 2007-08-29 : 13:35:51
Smith,

With this change, will it cause the stored procedure to recompile every time?

Thanks in advance.
Thanvi

quote:
Originally posted by jsmith8858

thanvi -- why are you asking for help, yet ignoring our advice?

This entire section:

quote:

set @sqlString = @sqlString + N'order by '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''asc'' THEN PostalCode END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''posteddate'' AND @ascdesc = ''desc'' THEN PostalCode END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''asc'' THEN clientid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''clientid'' AND @ascdesc = ''desc'' THEN clientid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''asc'' THEN transid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''transid'' AND @ascdesc = ''desc'' THEN transid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''asc'' THEN refid END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''refid'' AND @ascdesc = ''desc'' THEN refid END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''asc'' THEN AccountNo END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''AccountNo'' AND @ascdesc = ''desc'' THEN AccountNo END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''accountname'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''asc'' THEN settleddate END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''settleddate'' AND @ascdesc = ''desc'' THEN settleddate END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''asc'' THEN accountname END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''status'' AND @ascdesc = ''desc'' THEN accountname END desc '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''asc'' THEN amount END '
set @sqlString = @sqlString + N'CASE WHEN @OrderBy = ''amount'' AND @ascdesc = ''desc'' THEN amount END desc '


can be replaced with simply:

set @sqlstring = @sqlstring + N'ORDER BY ' + @OrderBy + N' ' + @ascdesc

That's it!

It's is not only shorter, easier to read and edit and work with, but also much more efficient.

You should validate that the @ascdesc and @orderby values are valid first, but otherwise that's all you need.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-08-29 : 14:00:03
First, read what Kristen wrote. that exact code will not work if you are not passing in actual column names.

Second, why are you worrying about re-compiling? If you don't want your SQL statement to re-compile over and over, don't use dynamic sql. If you are able to "dynamically build a static sql statement that executes dynamically" and therefore doesn't need a recompile, the performance hit of a recompile will be negligible compared to the performance hit of building and executing such a large, complicated, inefficient SQL statement. Not to mention the nightmare of debugging and maintaining such code.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-08-29 : 14:14:40
Actually I think it will help the performance. Your ORDER BY will only show the columns required, rather than a CASE statement to Compute the column(s) for the Order By.

Because you are using sp_ExecuteSQL this will lead to EACH different combination of Order By's being cached. So if there are a few common ones that is good. If there are, basically, "every combination under the sun" then that might be not so good.

Bottom line: Make some performance tests to see how it performs in your envir0onment. You might have so little memory on your server that the query plans get chucked out of memory immediately anyway!

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site