Var types for this type of Stored Procedure

Question

Var types for this type of Stored Procedure

SQLSrvrN00b

Say Hey Kid

Points: 688
More actions
March 8, 2004 at 5:50 am

#161232

Hi all,
I have a VB app that feeds a string to a stored procedure. I am running into truncation issues when I start feeding in more than 1200 bytes to the parameter. I think that my problem may be compounded by my nested queries being a string also.
Here is an example of what I am doing:
Create Procedure U_CCBAgenda
( @strhaving nvarchar (2000) )
AS
Declare @SQLStatement nvarchar (2000)
Declare @NA nvarchar (2) = 'NA'
Declare @Date nvarchar (10) = '10/18/1999'
Set SQLStatement = 'Select * from (Select * From table) -- This is 2557 characters long.
Having' + @strHaving
Exec sp_executeSQL @SQLStatement
GO
When I try to build strHaving with more than 1200 characters, the SP truncates the string and returns an error. I tried changing the variable type to text & ntext, but this only produced another error. It said that you may not use text or ntext when declaring a local varaible.
My question, is there a variable that I can use here to take an unlimited number of characters? I do not want to limit the User's selections to a specific number.
thanks for any help.
SQLSrvrN00b

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

sp_executeSQL requires the SQL statement as unicode string. So you best bet is a nvarchar(4000). If your string is longer than this you cannot use sp_executeSQL, but must use EXEC() which is virtually not limited, but has other downsides.

However there should be several other approches to what you are trying to achieve. Some are explained here Anyway, Erland's whole site should be interesting to you.

HTH

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLSrvrN00b Say Hey Kid Points: 688 More actions · Answer 2

Thanks, I will check that site out and let you know what the outcome is.

SQLSrvrN00b Say Hey Kid Points: 688 More actions · Answer 3

Frank,

Thanks for the help. I am going to use EXEC(). This allowed me to get the majority of the selections and is acceptable. Thanks for the link.

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

About the only reason to do this with dynamic SQL is if those responsible for the front-end code are not conversant with SQL. As that is where your clauses are being built, I doubt that is the case here. I recommend not using a stored procedure but instead building the statement as a command object on the client side and then submitting it as such.

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Thanks for the help. I am going to use EXEC(). This allowed me to get the majority of the selections and is acceptable. Thanks for the link.

Oops, in that case I should feel sorry for posting this. Basically I hoped my links would keep you away from EXEC().

Or is this only for internal use?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

SQLSrvrN00b Say Hey Kid Points: 688 More actions · Answer 6

The app is for internal use only. I was being limited to a total of 21 different selections using the sp_executeSQL method. By changing to the exec() method I am able to allow the user to select a maximum of 64 different selections.

This app is a report generating app that was taking a user over 3 minutes to run before moving the querying into a SP. Now that it is a SP there has been a 1500% increase in time to generate the reports.

Through testing, the sp_ExecuteSQL and EXEC() methods run at the same speed, no performance hits.

Please don't feel sorry of posting this, I was limited to 4000 characters before and my users were limited to their selections. Now they have more than enough space to perform complex reporting.

Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Well, okay,...

Still Jonathan's reasoning is valid!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sholliday SSCrazy Points: 2100 More actions · Answer 8

declare @strSQL1 varchar(8000)

declare @strSQL2 varchar(8000)

select @strSQL1 = 'select * '

select @strSQL2 = ' FROM authors'

EXEC (@strSQl1 + @strSQL2)

You should AVOID doing dynamic sql at all costs. But above is something to think about.

precompilation is the reason stored procedures work. execution plan is the phrase to understand why this is important.

..