Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Dynamic SQL vs. Static SQL Part 2, Code Expand / Collapse
Author
Message
Posted Wednesday, March 06, 2002 7:43 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Good article. For case 1 you could use a function in sql 2000 to simplify the static sql to...

select * from authors WHERE au_lname IN (SELECT * FROM list_to_table('white,green'))

where the function is...

create function list_to_table (@list varchar(8000))
returns @t table (list_item varchar(100))
as
begin
DECLARE @len int, @CurPos int, @PrevPos int
SET @len = LEN(@list) + 1
SET @CurPos = 1
SET @PrevPos = @CurPos

WHILE @CurPos < @len + 1
BEGIN
IF SUBSTRING(@list + ',', @CurPos, 1) = ','
BEGIN
INSERT INTO @t (list_item)
SELECT SUBSTRING(@list, @PrevPos, @CurPos - @PrevPos)
SET @PrevPos = @CurPos + 1
END
SET @CurPos = @CurPos + 1
END

return
end

Just a thought.

Ryan.




Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #29183
Posted Wednesday, March 06, 2002 8:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
Thanks!

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #29184
Posted Monday, March 18, 2002 4:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 21, 2010 7:15 AM
Points: 77, Visits: 10
In the article you mentioned a 8000 chars limit of @variable for dynamic SQL. You can extend this to several variables like this:

EXEC (@var1 + @var2 + @var3)

(see BooksOnline)

Otherwise, great article.


Edited by - jcool on 03/18/2002 04:55:54 AM



Post #29185
Posted Thursday, March 21, 2002 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 4:35 AM
Points: 1, Visits: 6
I found the articles and discussion interesting.
I have built a web based content management system where the page content is stored in XML representation in one field.
I did this because it is easy to change the data structure as needed, and each row can have a different XML structure if necessary.
The down sides to this are:
  • that the content for each page must be less than 8k (not really a problem as greater than 8k would really be too much!)

  • XML markup is mingled with content and thus performing a search may pick up matches in the markup.


What I would prefer to do is to have each structure stored in its own table, with markup elements one per field.

However, when reading the data out for display this would require something like:

select @table from pages where pageid = @pageid

select * from @table where pageid = @pageid

As far as I can tell, the solution advocated in these articles would go something like:

select @table from pages where pageid = @pageid

if @table = 'structure1'
begin
select * from structure1 where pageid = @pageid
end

if @table = 'structure2'
begin
select * from structure2 where pageid = @pageid

end

etc.

This is certainly harder to maintain than the dynamic SQL alternative.

Is there a better way, or is dynamic SQL a 'satisfactory' approach?

BTW I already have to use dynamic SQL for our distributed queries on to Oracle!





Post #29186
Posted Thursday, March 21, 2002 7:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 9:05 AM
Points: 976, Visits: 59
For performance and security the static SQL is likely the best choice. For development time the dynamic SQL would take less time. After that, the maintenance time required is almost none for both unless changes are required. However, some changes could take the same amount of time to implement as a simple find and replace could do it in both cases. For changes that can't be put in place by find and replace then there is a chance that the dynamic SQL could be easier to maintain.

There are other factors to consider in the maintenance. The more complex your dynamic SQL the longer it takes to test and debug. This is because you have to run through every possible choice to ensure that the SQL is built properly. With static SQL you simply build it and SQL Server can check it for you and point you to bugs easily.

Also the more complex the dynamic SQL is the harder it can become to read.

I don't think you should never use dynamic SQL, I do believe you should limit its use as much as possible. There are some cases when static SQL simply won't work.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #29187
Posted Tuesday, September 15, 2009 11:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 02, 2010 9:27 PM
Points: 8, Visits: 43
Hi Ryan,
I have many procedures using dynamic sqls. And my main problem comes when the IN clause is used.
A.ClOffCd IN (' + @ClOffCd + ') AND A.ClCustCd IN (' + @ClCustCd +')    



I am getting the values from table without quotes for each column value.
Will using the function won't impact the performance.


ALTER Procedure [dbo].[Usp_CallStatus_GetCount]    
(
@CallFromDate Varchar(15) = null,
@CallToDate Varchar(15) = null,
@CompCode Varchar(6) = null,
@UserID Varchar(6) = null,
@CityCode Varchar(8000) = null,
@CallTypeCode VarChar(2000) = null,
@BankCode Varchar(8000) = null,
@Frequency Varchar(50) = null,
@RouteCode Varchar(8000) = null,
@Status Varchar(50) = null,
@SQLDateFormat Varchar(5) = null,
@ClOffCd Varchar(8000) = null,
@ClCustCd Varchar(8000) = null ,
@argClientList varchar(1000)=NULL--Added by smita w . on 24-feb-09 for filtering client rights wise
)
as
Begin

Exec('SELECT A.CLCALLSTATUS, COUNT(*) [Status Count]
FROM CLLOG A
INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE )
INNER JOIN CALLMASTER C ON (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION)
LEFT OUTER JOIN (
SELECT T.ATMID,T.ATMLOCCD,ATMCOMPCD,ATMOFFCD,LOCDESC
FROM MATM T
INNER JOIN MLOCATION K ON T.ATMLOCCD=K.LOCCODE AND T.ATMOFFCD=K.OFFCODE AND T.ATMCITY=K.CITYCODE
)PQR ON ( A.CLATMID=PQR.ATMID AND A.COMPCODE=PQR.ATMCOMPCD AND A.CLOFFCD=PQR.ATMOFFCD)
LEFT OUTER JOIN (
SELECT CUSTCODE,CUSTBRCODE,CUSTCUSTOMERCODE,COMPCODE,E.OFFCODE,E.CITYCODE,E.LOCCODE,LOCDESC,
CUSTCUSTNAME,CALLTYPECODE
FROM MCUSTCUSTOMER E
INNER JOIN MLOCATION G
ON E.LOCCODE =G.LOCCODE AND E.OFFCODE=G.OFFCODE AND E.CITYCODE=G.CITYCODE
)ABC ON ( A.CLCUSTCD= ABC.CUSTCODE AND A.CLCUSTBRCD=ABC.CUSTBRCODE AND A.COMPCODE=ABC.COMPCODE AND A.CLOFFCD=ABC.OFFCODE AND A.CLCUSTCUSTCD=ABC.CUSTCUSTOMERCODE)
INNER JOIN (
SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE
FROM MCUSTOMERBRANCH D
INNER JOIN MCITY F ON D.CITYCODE=F.CITYCODE
)P ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE)
LEFT OUTER JOIN (
SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD
FROM CLEXEC Q INNER JOIN MROUTE R
ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
)X ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD and A.CLCUSTCD=X.CLCUSTCD AND A.CLCUSTBRCD=X.CLCUSTBRCD AND A.CLOFFCD=X.CLOFFCD AND A.COMPCODE=X.COMPCODE AND A.CLGENDATE=X.CLGENDATE AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR A.CLCUSTCUSTCD IS NULL))

' + @argClientList + '
WHERE CAST(CONVERT(DATETIME,A.CLACTDATE,' + @SQLDateFormat + ') AS CHAR(11))
BETWEEN CONVERT(DATETIME,''' + @CallFromDate + ''',' + @SQLDateFormat + ')
AND CONVERT(DATETIME,''' + @CallToDate +''',' + @SQLDateFormat + ')
AND A.COMPCODE = ''' + @CompCode + ''' AND B.USERID=''' + @UserID + '''
AND P.CITYCODE IN (' + @CityCode + ') AND
B.CALLNAME IN (' + @CallTypeCode + ') AND A.CLCUSTBRCD IN (' + @BankCode + ')
AND A.CLNATURE IN(' + @Frequency + ') AND A.CLCALLSTATUS IN ('+ @Status + ')
AND' + @RouteCode + ' AND A.LOGINAUTHUSERID IS NOT NULL
AND A.CLCALLSTATUS <> ''CL'' AND A.ClOffCd IN (' + @ClOffCd + ') AND A.ClCustCd IN (' + @ClCustCd +')
GROUP BY A.CLCALLSTATUS ORDER BY A.CLCALLSTATUS')

End


The application has many concurrent users, please advice something in this case that would make it process faster.

Regards,
Soni
Post #788710
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse