September 17, 2003 at 1:56 pm
I know that I've seen this question posted here before but I can't find it anywhere. Can anyone point me in the right direction or simply answer the question -- AGAIN!!!
I'm creating a dynamic SQL string in a stored proc using nvarchar(4000) and then executing the SQL via sp_executeSQL. The problem is that my SQL string exceeds the maximum of 4000. How can I get around this limitation?
Using more than one variable and doing the following does not seem to work...
sp_executeSQL @mySQL1 + @mySQL2 + @mySQL3
Any help would be appreciated.
Thanks!!!
Brian
September 17, 2003 at 2:34 pm
Here try some thing like this.
declare @x nvarchar(4000)
declare @y nvarchar(4000)
declare @z nvarchar(4000)
set @x = 'SELECT ''Place first 4000 characters here, '
set @y = 'second 4000 here,'
set @z = ' third 4000 here, and so on '', * FROM SYSOBJECTS'
exec sp_executesql N'EXEC (@x + @y + @z)',N'@x nvarchar(4000),@y nvarchar(4000),@z nvarchar(4000) ',@x=@x,@y=@y,@z=@z
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 2:53 pm
Have you tried Exec() function? It allows concatenation.
J. Moseley
[font="Courier New"]ZenDada[/font]
September 17, 2003 at 3:13 pm
Are you saying that you can do something as simple as the following...
declare @x nvarchar(4000)
declare @y nvarchar(4000)
declare @z nvarchar(4000)
set @x = 'Place first 4000 characters'
set @y = 'second 4000 here'
set @z = 'third 4000 here'
EXECUTE (@x + @y + @z)
Are there any differences in performance of using EXECUTE versus sp_ExecuteSQL? What are the advantages of doing something like Greg suggests over the example above?
Thanks everyone for your help!!!
September 17, 2003 at 3:18 pm
Yes you can also do what J. Moseley
is suggesting. One advantage I can see in using sp_executesql is you can pass and return parameters from the dynamic SQL. With Exec () the final T-SQL string can't contain variables.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 17, 2003 at 3:41 pm
But if you build the input parameters into the SQL string in the variables, then can't you get the same result?
Say for example, you have a stored proc with...
create procedure [usp_myStoredProc]
@input char(20) = 'TEST'
as
declare @x nvarchar(4000)
declare @y nvarchar(4000)
declare @z nvarchar(4000)
set @x = 'SELECT * '
set @y = 'FROM tablename '
set @z = 'WHERE column = ' + @input
EXECUTE (@x + @y + @z)
Wouldn't this produce the same thing? Does both methods allow for output parameters?
September 17, 2003 at 3:44 pm
sp_executesql is faster and more feature laden than EXEC(). Use it when you are exectuting a dynamic string multiple times in succession where only the parameters change because query optimizer will reuse the execution plan and because the parameters will be specified in their native data format, not first converted to string.
J. Moseley
[font="Courier New"]ZenDada[/font]
September 17, 2003 at 3:49 pm
Thanks to all for your responses.
The information provided has been very helpful.
Thanks!!!
September 17, 2003 at 3:57 pm
When I mean is you can pass parmaters like this:
declare @x nvarchar(4000)
set @x = 'SELECT * FROM sysobjects where name = @name'
exec sp_executesql @x ,N'@x nvarchar(4000), @name nvarchar(100)',@x=@x,@name=N'sysobjects'
exec sp_executesql @x ,N'@x nvarchar(4000), @name nvarchar(100)',@x=@x,@name=N'sysindexes'
And Return Parms like this, don't think you can do this with Exec ():
declare @x nvarchar(4000)
declare @cnt int
set @cnt = 0
set @x = 'SELECT @cnt=count(*) FROM sysobjects where name like @name'
exec sp_executesql @x ,N'@x nvarchar(4000), @name nvarchar(100), @cnt int out',@x=@x,@name=N'sysobjects',@cnt=@cnt out
print @cnt
exec sp_executesql @x ,N'@x nvarchar(4000), @name nvarchar(100), @cnt int out',@x=@x,@name=N'sys%',@cnt=@cnt out
print @cnt
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply