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

Transact-SQL (2005)

dynamic sql

Author

Topic

axvictor
Starting Member

4 Posts

Posted - 2010-06-20 : 05:41:15

hi SqlTeam,

Would like to get some help in resolving the "syntax error" that I am encountering when trying to execute my sql script when the @msg_todate is used .. The aim of the script is to filter records based on combination of user input (userid, status, fromdate, uptodate)

Any help will be greatly appreciated, am also open to suggestions on how to improve my sql script.

Thank you.

Listed below is my sql script
-- begin script --
declare @dynasql nvarchar(500)
declare @dynawhr nvarchar(500)
declare @dynacon int

declare @msg_userid varchar(25)
declare @msg_status int -- from dropdown: new/read/replied
declare @msg_frdate varchar(10) -- format is 'mm/dd/yyyy'
declare @msg_todate varchar(10) -- format is 'mm/dd/yyyy'

declare @msg_fromyy int
declare @msg_frommm int
declare @msg_fromdd int

declare @msg_uptoyy int
declare @msg_uptomm int
declare @msg_uptodd int

-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_fromyy = null
set @msg_frommm = null
set @msg_fromdd = null
set @msg_uptoyy = null
set @msg_uptomm = null
set @msg_uptodd = null

-- parameters to the procedure
-- these are the parameters im working with
-- try several combinations, some works, some do not
-- im still debugging
set @msg_userid = '10PPIPHUT2A'
set @msg_status = 1
set @msg_frdate = '04/01/2010' -- format is 'mm/dd/yyyy'
set @msg_todate = '04/13/2010' -- format is 'mm/dd/yyyy'

set @dynacon = 0
set @dynasql = 'select email_id, email_dt, sender, receiver, branch_id, subject,
status, msg, attachment_flag, attached_file, status2,
receiver_deleted, sender_deleted, branch_id_receiver,
email_type
from tbl_email '
set @dynawhr = null

if (@msg_userid is not null)
begin
set @dynawhr = 'where (sender = @msg_userid) '
set @dynacon = 10
end

if (@msg_status is not null) and (@dynawhr is not null)
begin
set @dynawhr = @dynawhr + 'and (status = @msg_status) '
set @dynacon = @dynacon + 1
end
else
begin
if (@msg_status is not null)
begin
set @dynawhr = 'where (status = @msg_status) '
set @dynacon = 20
end
end

if (@msg_frdate is not null) and (@dynawhr is not null)
begin
set @msg_fromyy = datepart(yy, convert(datetime, @msg_frdate))
set @msg_frommm = datepart(mm, convert(datetime, @msg_frdate))
set @msg_fromdd = datepart(dd, convert(datetime, @msg_frdate))
set @dynawhr = @dynawhr + 'and (datepart(yy, email_dt) >= @msg_fromyy) and (datepart(mm, email_dt) >= @msg_frommm) and (datepart(dd, email_dt) >= @msg_fromdd) '
set @dynacon = @dynacon + 2
end
else
begin
if (@msg_frdate is not null)
begin
set @msg_fromyy = datepart(yy, convert(datetime, @msg_frdate))
set @msg_frommm = datepart(mm, convert(datetime, @msg_frdate))
set @msg_fromdd = datepart(dd, convert(datetime, @msg_frdate))
set @dynawhr = 'where (datepart(yy, email_dt) >= @msg_fromyy) and (datepart(mm, email_dt) >= @msg_frommm) and (datepart(dd, email_dt) >= @msg_fromdd) '
set @dynacon = 30
end
end

if (@msg_todate is not null) and (@dynawhr is not null)
begin
set @msg_uptoyy = datepart(yy, convert(datetime, @msg_todate))
set @msg_uptomm = datepart(mm, convert(datetime, @msg_todate))
set @msg_uptodd = datepart(dd, convert(datetime, @msg_todate))
set @dynawhr = @dynawhr + 'and (datepart(yy, email_dt) <= @msg_uptoyy) and (datepart(mm, email_dt) <= @msg_uptomm) and (datepart(dd, email_dt) <= @msg_uptodd) '
set @dynacon = @dynacon + 4
end
else
begin
if (@msg_todate is not null)
begin
set @msg_uptoyy = datepart(yy, convert(datetime, @msg_todate))
set @msg_uptomm = datepart(mm, convert(datetime, @msg_todate))
set @msg_uptodd = datepart(dd, convert(datetime, @msg_todate))
set @dynawhr = 'where (datepart(yy, email_dt) <= @msg_uptoyy) and (datepart(mm, email_dt) <= @msg_uptomm) and (datepart(dd, email_dt) <= @msg_uptodd) '
set @dynacon = 40
end
end

print 'Generated SQL Stmt ==> ' + @dynasql + @dynawhr

if @dynacon = 0
exec sp_executesql @dynasql
else
begin
set @dynasql = @dynasql + @dynawhr
if @dynacon < 30
begin
if @dynacon < 20
begin
if @dynacon = 10
exec sp_executesql @dynasql, N'@msg_userid varchar(25)', @msg_userid
else
if @dynacon = 11
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int', @msg_userid, @msg_status
else
if @dynacon = 13
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_userid, @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 17
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 12
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_userid, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 16
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 14
exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_userid, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
else
begin
if @dynacon = 20
exec sp_executesql @dynasql, N'@msg_status int', @msg_status
else
if @dynacon = 22
exec sp_executesql @dynasql, N'@msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 26
exec sp_executesql @dynasql, N'@msg_status int, @msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_status, @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
else
if @dynacon = 24
exec sp_executesql @dynasql, N'@msg_status int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_status, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
end
else
begin
if @dynacon < 40
begin
if @dynacon = 30
exec sp_executesql @dynasql, N'@msg_fromyy int, @msg_frommm int, @msg_fromdd int', @msg_fromyy, @msg_frommm, @msg_fromdd
else
if @dynacon = 34
exec sp_executesql @dynasql, N'@msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
else
if @dynacon = 40
exec sp_executesql @dynasql, N'@msg_uptoyy int, @msg_uptomm int, @msg_uptodd int', @msg_uptoyy, @msg_uptomm, @msg_uptodd
end
end
-- endof script --

Kristen
Test

22859 Posts

Posted - 2010-06-20 : 07:41:41

Dunno if any of this is relevant to your actual problem, which I haven't looked for carefully, but these are my observations:

Personally I wouldn't bother with different SP_ExecuteSQL statements for each parameter combination - sp_ExeuteSQL doesn't care if it is given parameters which are not used. If the "unused ones" are only going to be NULL I don't suppose it matters to performance either, but the long IF statements in your code will make code-maintenance "fragile"

convert(datetime, @SomeVariable)

is assuming a default date format. This is unreliable and subject to change (e.g. if current user changes language or server setting changes.)

I suggest you explicitly

SET DATEFORMAT MDY

or use CONVERT(datetime, @StringDateVariable, 101)

(or change the string date format to "yyyymmdd" [NOTE: no hyphens] which will be converted by SQL server unambiguously)


set @dynawhr = null

if (@msg_userid is not null)
begin
set @dynawhr = 'where (sender = @msg_userid) '
set @dynacon = 10
end

if (@msg_status is not null) and (@dynawhr is not null)
begin
set @dynawhr = @dynawhr + 'and (status = @msg_status) '
set @dynacon = @dynacon + 1
end
else
begin
if (@msg_status is not null)
begin
set @dynawhr = 'where (status = @msg_status) '
set @dynacon = 20
end
end

this strikes me as very complicate to maintain, I would do something like this


set @dynawhr = 'WHERE 1=1'

if (@msg_userid is not null)
begin
	set @dynawhr = @dynawhr + ' AND (sender = @msg_userid)'
end

if (@msg_status is not null)
begin
	set @dynawhr = @dynawhr + ' AND (status = @msg_status)'
end
..
exec sp_executesql 
	@dynasql, 
	N'@msg_fromyy int, @msg_frommm int, @msg_fromdd int, @msg_uptoyy int, @msg_uptomm int, @msg_uptodd int, ...', 
	@msg_fromyy, @msg_frommm, @msg_fromdd, @msg_uptoyy, @msg_uptomm, @msg_uptodd, ...

where the SP_ExecuteSQL call contains all possible parameters

This is also not ideal:


set @dynawhr = 'where (datepart(yy, email_dt) <= @msg_uptoyy) and (datepart(mm, email_dt) <= @msg_uptomm) and (datepart(dd, email_dt) <= @msg_uptodd) '

because the use of all the DATEPART() functions will prevent any index on [email_dt] being used

Also, if @msg_uptoyy is, say, '01 Jan 2010' and [email_dt] is '31 Dec 2009' this test will fail (which is fine if you only want Year-To-Date - but is that what you want?)

The most efficient date filtering is to use this style:


    [email_dt] >= @StartDate
AND [email_dt] <  @EndDate

as that will use an index if it is available; note that @EndDate itself is NOT included in the range, so needs to be one-day-after the actual end date.

So if the end-point is 31-Dec-2010 then set @EndDate to "01-Jan-2011"

The reason for this is that if [email_dt] includes a TIME part then

[email_dt] <= '31-Dec-2010'

will fail if [email_dt] is actually '31-Dec-2010 01:02:03'

If you only have Dates (i.e. the TIME part is "00:00:00.000" then you can safely do

[email_dt] <= '31-Dec-2010'

instead, but you have to be sure [email_dt] will never accidentally get a TIME part (personally I would only do that if using the DATE datatype available in SQL2008, but in earlier versions of SQL there was only a DATETIME datatype - so the risk that a Time was set on a column that was only intended to store a Date)

niechen861102
Starting Member

9 Posts

Posted - 2010-06-20 : 22:53:05

spam removed

axvictor
Starting Member

4 Posts

Posted - 2010-06-22 : 12:46:53

hi Kristen, thank you very much for taking the time to analyze my sql script .. kindly find my replies point-per-point =p

Personally I wouldn't bother with different SP_ExecuteSQL statements for each parameter combination - sp_ExeuteSQL doesn't care if it is given parameters which are not used. If the "unused ones" are only going to be NULL I don't suppose it matters to performance either, but the long IF statements in your code will make code-maintenance "fragile" => yes, they are a bit wieldy and have compressed them successfully into one sp_executesql passing all the possible combinations and it compiles nicely, thank you for this insight

SET DATEFORMAT MDY
or use CONVERT(datetime, @StringDateVariable, 101)
=> have also tried your suggestion and yes i do find your suggestion valid, i also am fond of using convert(varchar(10), datefield_or_datevar, 101) but it seems that in this case, this condition does not seem to work, the datetime field email_dt contains timestamp values

The most efficient date filtering is to use this style:


    [email_dt] >= @StartDate
AND [email_dt] <  @EndDate

=> have reverted back to this condition but there seems to be some confusion on mssql's part that is, since i am not able to return rows, whenever the date filter are in play because as you have pointed out, i need to ensure that the date field do not contain timestamps,

after incorporating your inputs, (will still have to revise it again to include the initial set @dynawhr = 'where 1=1 '), my script is now like this but alas, i am stuck with the datevar filters not being able to filter them, have already tried several possible fixes re: google search on "get date-part of a datetime field in mssql" to no avail


declare @dynasql nvarchar(500)
declare @dynawhr nvarchar(500)

declare @msg_userid varchar(25)
declare @msg_status int          -- from dropdown: new/read/replied
declare @msg_frdate varchar(10)  -- format is 'yyyy-mm-dd'
declare @msg_todate varchar(10)  -- format is 'yyyy-mm-dd'

-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_frdate = null
set @msg_todate = null

-- parameters to the procedure
-- these are the parameters im working with
-- try several combinations, some works, some do not
-- im still debugging
set @msg_userid = '10PPIPHUT2A'
--set @msg_status = 0
set @msg_frdate = '2010-04-10'  -- format is 'yyyy-mm-dd'
set @msg_todate = '2010-04-13'  -- format is 'yyyy-mm-dd'

set @dynasql = 'select email_id, email_dt, sender, receiver, branch_id, subject, status, msg, attachment_flag, attached_file, status2, receiver_deleted, sender_deleted, branch_id_receiver, email_type from tbl_email '
set @dynawhr = null

if (@msg_userid is not null)
   set @dynawhr = 'where (sender like @msg_userid) '


if (@msg_status is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and (status = @msg_status) '
else
   if (@msg_status is not null)
      set @dynawhr = 'where (status = @msg_status) '


if (@msg_frdate is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) >= @msg_frdate) '
else
   if (@msg_frdate is not null)
      set @dynawhr = 'where (convert(varchar(8), email_dt, 112) >= @msg_frdate) '


if (@msg_todate is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) <= @msg_todate) '
else
   if (@msg_todate is not null)
      set @dynawhr = 'where (convert(varchar(8), email_dt, 112) <= @msg_todate)'

if @dynawhr is not null
   set @dynasql = @dynasql + @dynawhr

print 'Generated SQL ==> ' + @dynasql
print 'Userid => ' + @msg_userid
print 'Status => ' + convert(varchar(10),@msg_status)
print 'FrDate => ' + @msg_frdate
print 'ToDate => ' + @msg_todate

exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_frdate varchar(10), @msg_todate varchar(10)', @msg_userid, @msg_statu, @msg_frdate, @msg_todate

Kristen
Test

22859 Posts

Posted - 2010-06-22 : 15:24:38

What does the "Generated SQL" look like?

If you run that in isolation, and fiddle about with it, can you get some data / the data you want? Then you can rework the changes you made back into the original script.

You mention TIMESTAMP and DateTime - the TIMESTAMP datatype has nothing to do with dates & times - is there any confusion here?

axvictor
Starting Member

4 Posts

Posted - 2010-06-23 : 04:29:35

hi Kristen,

good news, i have managed to fix my script and now it return data accordingly.. the problem was with the declaration portion for

declare @dynasql nvarchar(1000) => before this was nvarchar(500)
declare @dynawhr nvarchar(1000) => before this was nvarchar(500)

and it seems that the static select + the dynamic where-clause
exceeds 500, your hunch was pointing me in the correct direction ..

thanks to your patient analysis and helpful suggestions, the sql script is now streamlined as follows:


declare @dynasql nvarchar(1000)
declare @dynawhr nvarchar(1000)
declare @dynacon int

declare @msg_userid varchar(25)
declare @msg_status int          -- from dropdown: new/read/replied
declare @msg_frdate varchar(10)  -- format is 'mm/dd/yyyy'
declare @msg_todate varchar(10)  -- format is 'mm/dd/yyyy'

declare @msg_fromyy int
declare @msg_frommm int
declare @msg_fromdd int

declare @msg_uptoyy int
declare @msg_uptomm int
declare @msg_uptodd int

-- parameters to sp_executesql
set @msg_userid = null
set @msg_status = null
set @msg_fromyy = null
set @msg_frommm = null
set @msg_fromdd = null
set @msg_uptoyy = null
set @msg_uptomm = null
set @msg_uptodd = null

-- parameters to the procedure
--set @msg_userid = '10PPIPHUT2A'
--set @msg_status = 3
set @msg_frdate = '01/01/2010'  -- format is 'mm/dd/yyyy'
set @msg_todate = '04/30/2010'  -- format is 'mm/dd/yyyy'

set @dynasql = 'select email_id, email_dt, sender, receiver, branch_id, subject, status, msg, attachment_flag, attached_file, status2, receiver_deleted, sender_deleted, branch_id_receiver, email_type from tbl_email '
set @dynawhr = 'where (1 = 1) '

if (@msg_userid is not null)
   set @dynawhr = @dynawhr + 'and (sender = @msg_userid) '


if (@msg_status is not null)
   set @dynawhr = @dynawhr + 'and (status = @msg_status) '


if (@msg_frdate is not null)
    set @dynawhr = @dynawhr + 'and (convert(varchar(10), email_dt, 101) >= @msg_frdate) '


if (@msg_todate is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and (convert(varchar(10), email_dt, 101) <= @msg_todate) '


set @dynasql = @dynasql + @dynawhr + 'order by sender, status, email_dt'

exec sp_executesql @dynasql, N'@msg_userid varchar(25), @msg_status int, @msg_frdate varchar(10), @msg_todate varchar(10)', @msg_userid, @msg_status, @msg_frdate, @msg_todate

i am truly grateful, i learned something new, and hopefully this little sql script of mine will prove useful to others sql developers out there.

oh and btw, the previous conditions with the tedious datepart-wrangling for the year, month and day both for @msg_frdate and @msg_todate also works nicely, though i still prefer convert(varchar(10), date_fieldvar, 101) which i am sure works perfectly, however, i will that version just in case some uneventful misinterpretation comes my way

Kristen, you rock! thanks a million!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-06-23 : 04:40:30

EDIT : I see you've changed your code slightly but the point about passing dates to the dynamic sql call as strings is still valid. You don't need to do this with sp_executeSql, you can pass proper data types like int, datetime etc and it means you don't have to mess about with CONVERT inside the block

Can I ask why you are passing dates in to your dynamic sql as VARCHAR(10) datatypes?

Here's your EXEC sp_executeSql Call


exec sp_executesql
	@dynasql
	, N'@msg_userid varchar(25)
	, @msg_status int
	, @msg_frdate varchar(10)
	, @msg_todate varchar(10)'
	, @msg_userid
	, @msg_statu
	, @msg_frdate
	, @msg_todate

And here is where they are used inside the dynamic block


if (@msg_frdate is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) >= @msg_frdate) '
else
   if (@msg_frdate is not null)
      set @dynawhr = 'where (convert(varchar(8), email_dt, 112) >= @msg_frdate) '

This does a STRING comparison not a DATE comparison so the results will be not as you expect. So what you are actually doing is comparing:


'20100101' >= '2010-12-12'

Which evaluates to TRUE (which I'm guessing you do not want).

Change the datatype to DATETIME. Use ISO standard strings (yyyymmdd) to set the date.

In the dynamic block don't convert [email_dt] -- just use it as a DATETIME (it *is* a DATETIME right?)

this part of the block would then become:


if (@msg_frdate is not null) and (@dynawhr is not null)
   set @dynawhr = @dynawhr + 'and ( email_dt >= @msg_frdate ) '
else
   if (@msg_frdate is not null)
      set @dynawhr = 'where ( email_dt >= @msg_frdate ) '

Does that all make sense?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

axvictor
Starting Member

4 Posts

Posted - 2010-06-23 : 06:15:21

hi Charlie, my replies to your comments/questions are preceded by this marking =>

quote:
Originally posted by Transact Charlie

EDIT : I see you've changed your code slightly but the point about passing dates to the dynamic sql call as strings is still valid. You don't need to do this with sp_executeSql, you can pass proper data types like int, datetime etc and it means you don't have to mess about with CONVERT inside the block => i see your point, for simplicity's sake, i opted to pass the date parameters as string

Can I ask why you are passing dates in to your dynamic sql as VARCHAR(10) datatypes? => for convenience only, since this script will be converted into an SP to be called from the front-end

Here's your EXEC sp_executeSql Call

exec sp_executesql
@dynasql
, N'@msg_userid varchar(25)
, @msg_status int
, @msg_frdate varchar(10)
, @msg_todate varchar(10)'
, @msg_userid
, @msg_statu
, @msg_frdate
, @msg_todate

And here is where they are used inside the dynamic block

if (@msg_frdate is not null) and (@dynawhr is not null)
set @dynawhr = @dynawhr + 'and (convert(varchar(8), email_dt, 112) >= @msg_frdate) '
else
if (@msg_frdate is not null)
set @dynawhr = 'where (convert(varchar(8), email_dt, 112) >= @msg_frdate) '

This does a STRING comparison not a DATE comparison so the results will be not as you expect. So what you are actually doing is comparing:

'20100101' >= '2010-12-12'

Which evaluates to TRUE (which I'm guessing you do not want). => you are referring to a previous version, the version that i prefer uses convert(varchar(10), email_dt, 101) to producde mm/dd/yyyy which conforms to my original intention to pass 'mm/dd/yyyy' date parameters as strings

Change the datatype to DATETIME. Use ISO standard strings (yyyymmdd) to set the date.

In the dynamic block don't convert [email_dt] -- just use it as a DATETIME (it *is* a DATETIME right?) => yes, email_dt is DATETIME field which is why i needed the convert function to strip away the timestamp portion and retain only the date for comparison to the date parameters passed

this part of the block would then become:

if (@msg_frdate is not null) and (@dynawhr is not null)
set @dynawhr = @dynawhr + 'and ( email_dt >= @msg_frdate ) '
else
if (@msg_frdate is not null)
set @dynawhr = 'where ( email_dt >= @msg_frdate ) '

Does that all make sense? => yes it does, and will give it a ponder

thank you very much for your insights and your time, i do hope this little script of mine was not much of a trouble, and at the same time am quite happy i must say that everyone gains additional info on pretty much a lot of topics, such as date-comparisons, date-handling and sp_executesql.

Kristen
Test

22859 Posts

Posted - 2010-06-23 : 10:57:53

"i do hope this little script of mine was not much of a trouble"

its a lot more little now, than it was! whidch I think is a Good Thing.

"declare @dynasql nvarchar(1000) => before this was nvarchar(500)
declare @dynawhr nvarchar(1000) => before this was nvarchar(500)"

Change then to 4000 - 4000 is the limit before SQL potentially starts using more CPU intensive methods to store the data. I don;t think ti will make any difference to actual memory usage, or performance, to set these to a sensible maximum - and they are less likely to "overflow" in the future.

I'm surprised / disappointed that SQL didn't give you an error on overflow though ...

... Might be worth adding a check for

IF (DATALENGTH(@dynasql) >= 1000 OR DATALENGTH(@dynawhr) >= 1000) ... error! ...

Change the "1000" to whatever you set the size to be in the DECLARE statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources