Maximum length of a Varchar Variable
-
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 -
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] -
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 -
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] -
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 -
what does
print @sql_str
print @sql_str1
print @sql_str+@sql_str1
give you ?
-
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 -
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] -
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 -
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] -
On your post the last line is :
...price' execute (@query1+@query2+@query3+Query4)
shouldn't Query4 be @query4?
* Noel -
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
-
For those missing apostrophe's try this:
WHERE pps_cust_po_master.fkey_customer =' + CONVERT(VARCHAR(100),@pkeycustomer)''
-SQLBill
-
Hey SQLBill, how did you do this formatting. Do the 'old' code tags work still?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yeah, How you could keep the code format?
I don't seem to find a way
* Noel
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply