Long dynamic query - 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

General SQL Server Forums

New to SQL Server Programming

Long dynamic query

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 17:27:14

I have to break dynamic query into two as it was more than 4000 characters long.

I have to use Execute(@nsql_1 + ' ' + @nsql_2) now

how can I use two parameters to use sp_executesql?

EXECUTE @error = sp_executesql @nsql, N'@min_date SMALLDATETIME,@max_date SMALLDATETIME',@min_date,@max_date

Thanks for help....

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 17:45:23

declare @s nvarchar(max)
select @s = @nsql_1
select @s = @s + ' '
select @s = @s + @nsql_2
EXECUTE @error = sp_executesql @s, N'@min_date SMALLDATETIME,@max_date SMALLDATETIME',@min_date,@max_date

==========================================
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.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 17:49:24

The problem is nvarchar max size is 4000 and @s will be more than 4000 characters.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 17:51:09

>> The problem is nvarchar max size is 4000
Not with v2005

declare @s nvarchar(max)
select @s = 'select @c = ''' + replicate('a',3000)
select @s = @s + replicate('b',3000)
select @s = @s + replicate('c',3000) + ''''
declare @c varchar(max)
exec sp_executesql @s, N'@c varchar(max) out', @c out
select datalength(@c)

result 9000

==========================================
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.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 18:02:02

oh ok thanks nr.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 18:14:51

ah so we cannot have output parameter with execute... I am using sql 2000.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 18:26:22

not with exec (@str) only with sp_executesql.
I assumed that was why you were using it.

You can concatenate strings with exec (@str + @str1 + @str3) to go over 8000 chars so it's not a problem.

In v2000 you can't use nvarchar(max) so it's a bit more of an issue.

==========================================
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.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 18:29:19

Yeah but I cannot use @error with this statement. Thanks for you help dude.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-17 : 18:39:24

You can check @@error after the exec or set @error to @@error.
It won't pick up all errors in a batch but neither will it in sp_executesql.

==========================================
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.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 18:40:31

Great thanks a lot nr.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-04-17 : 18:46:45

I ended by by using error in the statement after table was created by checking for the table...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources