very big quesy - 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

SQL Server 2000 Forums

SQL Server Development (2000)

very big quesy

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-08 : 09:32:05

i have a very big query something like 150 lines lots of "left outer join" and so on!
the thing is that up to now i left it in aview,but things i need to add to the query is moving me to work with STORED PROCEDURE
fr example in some of the LEFT OUTER JOIN's that i do,in some cases (if a user gives it to me i need to add a search by date and in some no)
how can i build this query dynamiclly ?
so i can changes the query by the parameters that are being sent?
beacuse currently the query is too long to do :
decalre @sqlSelect as nvarchar(4000)
set @sqlSelect='select.....'
exec(@sqlSeelct)
so what else can i do?
thnakd in adance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-08 : 09:35:22

beacuse currently the query is too long to do :
decalre @sqlSelect as nvarchar(4000)
set @sqlSelect='select.....'
exec(@sqlSeelct)

no it isn't - it might not be a good idea but exec takes a text datatype which can be very very long.

for your conditional searches you could use a series of parameters input which are null if the user doesn't want to filter on them and:

where (dte = @date or @date is null)
and (id = @id or @id is null)
and (name = @name or @name is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-08 : 09:51:30

You can use multiple variables in an EXEC.

declare @sql_1 nvarchar(4000)
declare @sql_2 nvarchar(4000)
declare @sql_3 nvarchar(4000)
...
declare @sql_20 nvarchar(4000)

... build statements as needed ...

exec(
@sql_1+
@sql_2+
@sql_3+
@sql_4+
...
@sql_20
)

CODO ERGO SUM

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-05-08 : 10:21:43

what is more correct to use nvarchar ro varchar?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-08 : 11:21:48

Depends on your characterset.
varchar is usually ok.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources