Maximum length of a Varchar Variable

Question

Post reply

Maximum length of a Varchar Variable

Prasad Bhogadi

SSCrazy Eights

Points: 9235
More actions
February 26, 2004 at 2:54 am

#109061

I have been facing this problem for a long time, I have stored procedures in which most of the queries are framed based on different conditions so, I am using variables to hold the dynamic queries like in the below example

Declare @sql_str varchar(1000)

set @sql_str = ' select a.name, b.name , c.name.... where a.name like %' + @name so on and so forth which runs into say more than 500 characters but when I try to run this dynamic sql by using execute method it cuts the string indefinitely, I am not sure the exact number of characters after which it is getting chopped off.

So I am declaring multiple variables and running the execute stament. .

like exec (@sql_str+@sql_str1)

Is there any restriction on the number of character that a variable declared as varchar has, it definely is not allowing the number of characters I defined the variable as. I apologize for any ambiguity. Will sp_executesql help solving this problem in any way.

Thanks

Prasad Bhogadi
www.inforaise.com

Viewing 15 posts - 1 through 15 (of 22 total)

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

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

BOL states that varchar is limited to 8,000 characters. When using sp_executesql you are bound to use nvarchar, which is limited to 4,000 characters. If that's not enough, you can only use EXEC()

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

Prasad Bhogadi

SSCrazy Eights

Points: 9235

February 26, 2004 at 3:12 am

#495774

Yeah Exactly, however my problem is that the variable is not even holding more than 500 characters even when I have defined the variable as varchar(8000). I don't know if anyone else has faced this problem.

Prasad Bhogadi
www.inforaise.com

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

Frank Kalis

SSC Guru

Points: 111183

February 26, 2004 at 3:14 am

#495775

Can you post your whole statement?

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

mccork SSC-Insane Points: 22288 More actions · Answer 4

mccork

SSC-Insane

Points: 22288

February 26, 2004 at 4:35 am

#495785

You may be SELECTing the variable in Query Analyzer but have the maximum characters per column result set to 500 chars? In QA check Tools/Options/Results.

If you're using sp_executesql then I think the limit is varchar(500) or nvarchar(500).

Cheers,
- Mark

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 5

Amit Jethva

SSCarpal Tunnel

Points: 4707

February 26, 2004 at 6:24 am

#495803

what does

print @sql_str

print @sql_str1

print @sql_str+@sql_str1

give you ?

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

Well the query is something like this, each one of the variables @query1,@query2...3..4 are declared as varchar(8000), since I am not sure after how many characters it is breaking I have split them into small pieces.SET @query1 = ' SELECT pps_products.pkey_product AS [pkey_item], pps_cust_po_dtl.quantity AS [quantity], pps_products.product_id AS [product_code], pps_cust_po_master.pkey_cust_po_master AS [fkey_order],'SET @query2=' CASE LEN(LEFT(pps_products.comments,1)) WHEN 1 THEN mytemptb.stringval + '''' + pps_products.comments + ''(comments)'' ELSE mytemptb.stringval END AS [product_desc], pps_product_price.price, (pps_cust_po_dtl.price_per_unit) AS [AMOUNT], pps_cust_po_dtl.shipping_price'SET @query3=' FROM mytemptb INNER JOIN pps_product_price ON pps_product_price.pkey_product_price=mytemptb.pkey INNER JOIN pps_products ON pps_products.pkey_product=pps_product_price.fkey_product INNER JOIN pps_cust_po_dtl ON pps_cust_po_dtl.fkey_product_price=mytemptb.pkey INNER JOIN pps_cust_po_master ON pps_cust_po_dtl.fkey_cust_po_master = pps_cust_po_master.pkey_cust_po_master WHERE pps_cust_po_master.fkey_customer =' + CONVERT(VARCHAR(100),@pkeycustomer)SET @query4= ' GROUP BY mytemptb.product_name, mytemptb.product_id, mytemptb.stringval, pps_products.is_active, pps_products.comments, pps_products.pkey_product, pps_products.product_name, pps_cust_po_dtl.quantity, pps_products.product_id, pps_cust_po_master.pkey_cust_po_master, pps_product_price.price, pps_cust_po_dtl.price_per_unit, pps_cust_po_dtl.quantity, pps_cust_po_dtl.shipping_price'execute (@query1+@query2+@query3+Query4)

Prasad Bhogadi
www.inforaise.com

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

It's hard to follow the forum formatting, but are you sure this works. Just asking because I tried

select datalength(@query1), datalength(@query2),datalength(@query3),datalength(@query4)

which returns

----------- ----------- ----------- -----------

190 270 NULL 387

(1 row(s) affected)

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 8

Prasad Bhogadi

SSCrazy Eights

Points: 9235

February 26, 2004 at 8:21 am

#495871

Yeah it does work but I am wondering is for the same query it returns me

188 264 410 379 respectively for @query1,@query2,@query3,@query4.

And it gets executed from my program without any issues with desired output.

Thanks

Prasad Bhogadi
www.inforaise.com

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

I guess I made a copy and paste error

Anyway, you're far from reaching the 8,000 character mark. Getting it all in one string and using either EXEC() or sp_executesql should work. Which error are you getting then?

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

noeld SSC Guru Points: 96590 More actions · Answer 10

noeld

SSC Guru

Points: 96590

February 26, 2004 at 11:48 am

#495954

On your post the last line is :

...price' execute (@query1+@query2+@query3+Query4)

shouldn't Query4 be @query4?

* Noel

SQLBill SSC Guru Points: 51440 More actions · Answer 11

I'm posting your code in hopfully a more readable format....

SET @query1 = ' SELECT pps_products.pkey_product AS [pkey_item],

pps_cust_po_dtl.quantity AS [quantity],

pps_products.product_id AS [product_code],

pps_cust_po_master.pkey_cust_po_master AS [fkey_order],'

SET @query2=' CASE LEN(LEFT(pps_products.comments,1)) WHEN 1 THEN mytemptb.stringval + '''' + pps_products.comments + ''(comments)'' ELSE mytemptb.stringval END AS [product_desc], pps_product_price.price,

(pps_cust_po_dtl.price_per_unit) AS [AMOUNT],

pps_cust_po_dtl.shipping_price'

SET @query3=' FROM mytemptb

INNER JOIN pps_product_price

ON pps_product_price.pkey_product_price=mytemptb.pkey

INNER JOIN pps_products

ON pps_products.pkey_product=pps_product_price.fkey_product

INNER JOIN pps_cust_po_dtl

ON pps_cust_po_dtl.fkey_product_price=mytemptb.pkey

INNER JOIN pps_cust_po_master

ON pps_cust_po_dtl.fkey_cust_po_master = pps_cust_po_master.pkey_cust_po_master

WHERE pps_cust_po_master.fkey_customer =' + CONVERT(VARCHAR(100),@pkeycustomer)

SET @query4= ' GROUP BY mytemptb.product_name,

mytemptb.product_id,

mytemptb.stringval,

pps_products.is_active,

pps_products.comments,

pps_products.pkey_product,

pps_products.product_name,

pps_cust_po_dtl.quantity,

pps_products.product_id,

pps_cust_po_master.pkey_cust_po_master,

pps_product_price.price,

pps_cust_po_dtl.price_per_unit,

pps_cust_po_dtl.quantity,

pps_cust_po_dtl.shipping_price'

execute (@query1+@query2+@query3+Query4)

There's no single apostrophe (') at the end of @query3 and there's a single quote in the WHERE statement but no 'closing' single apostrophe to go with it, so it appears @query3 is missing two separate single apostrophes. And as already pointed out the execute part has Query4 instead of @query4.

-SQLBill