SQL String Getting Cut Off - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL String Getting Cut Off

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2011-06-02 : 15:25:32

I'm trying to build a string that shows a list of materials not charged to a job. Problem I'm running into is that it seems to be cutting off the string after 255 characters. I know it should be able to hold more than this, what am I doing wrong?


DECLARE @jobmatls VARCHAR(MAX)
SET @jobmatls = 'Materials have not been issued to the job:' + CHAR(13)

DECLARE db_cursor CURSOR FOR
SELECT
item,
CAST(dbo.ReqQty(@jobqty, Units, Matl_Qty, 1, 0) - qty_issued AS DECIMAL(4,2))
FROM jobmatl
WHERE jobmatl.job = @job AND dbo.ReqQty(@jobqty, Units, Matl_Qty, 1, 0) - qty_issued > 0
DECLARE @item dbo.ItemType
DECLARE @qtyreq NVARCHAR(6) 

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @item, @qtyreq;

WHILE @@FETCH_STATUS = 0
	BEGIN

	SET @jobmatls = @jobmatls + @item + ': ' + @qtyreq + ' pc(s)' + CHAR(13)

FETCH NEXT FROM db_cursor INTO @item, @qtyreq;
	END

	CLOSE db_cursor
	DEALLOCATE db_cursor

SELECT @jobmatls

Result:


Materials have not been issued to the job:
23992: 1.00 pc(s)
23869: 1.00 pc(s)
23993: 1.00 pc(s)
13042: 1.00 pc(s)
27966: 1.00 pc(s)
18220-603S: 8.00 pc(s)
18100-076C: 3.00 pc(s)
27123-1: 1.00 pc(s)
13051: 2.00 pc(s)
18100-611B: 4.00 pc(s)
18110-025T: 4.00

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 15:33:32

That is probably an artifact of SSMS. You can change the setting in Tools->Options->Query Results -> SQL Server -> Results to Grid (or Results To Text).

Also, your query can probably be more efficiently written without using cursors. If you have interest in doing that, people on this forum may be able to help.

mmccardle
Starting Member

43 Posts

Posted - 2011-06-02 : 16:11:24

Hey you're right, I had it in Results to Text so I could view it more easily, but switching back to grid brings everything through. Weird.

I'm sure there are more efficient ways, but I'm not hitting the db for that much data (usually there will be 0 rows returned, 1 or 2 at the most). I've done work on converting columns and rows to text using xml paths, and the logic escapes me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources