database question

Question

Hi, I have written a cursor like bellow :

declare myCursor cursor 
for select productID, productName from products
declare @productID int 
declare @productName nvarchar(50)

open myCursor
fetch next from myCursor into @productID,@productName
print @productID
print @productName
set @productID=0
set @productName=''

while @@FETCH_STATUS=0
begin
    fetch next from myCursor into @productID,@productName
    print @productID
    print @productName
    set @productID=0
    set @productName=''
end
close myCursor
deallocate myCursor

now it print the id and name of the product under each other like:

1
Coffee
2
Apple …

But I want to have the id and name of the each product in a same line like:

1   coffee
2   apple  …

What can I do? I converted the id into the String and use +’’+ to concat id and name in a same string. But as the ids and names don’t have same lengths, it didn’t have a clean result. Is there any other way to do this?

Answer 1

try by using a TAB

print convert(nvarchar(30),@productID) + char(9) + @productName

or by using NCHAR

print convert(nvarchar(8),@productID) +  @productName

Answer 2

Depending on how long your number can be:

print convert(char(10), @productID) + ' ' + @productName

Char will right-pad the number with extra spaces, giving you a fixed with for the number.

Answer 3

I guess simpler solution would be to define formatting rules in client application, but if you really need it in database this is simple, why to use cursor as in your solution:

SELECT left(convert(varchar(20), productID) + '      ',6) + ' - ' + productName
from products

Answer 4

At the beginning you could determine the length of the longest number

DECLARE @length INT

SELECT @length = CAST(LOG10(MAX(productID)) AS INT)+1 FROM products

Then incorporate it into your print statement like

PRINT LEFT(CAST(@productID AS VARCHAR(10)) + 
    SPACE(@length),@length) + ' ' + @productName

I would just use "Results as text" in SSMS for this rather than a cursor. Hopefully it is just a learning exercise!

Answer 5

Instead of using cursors you could use a table like this...

DECLARE @products TABLE (ProductID int, ProductName nvarchar(50), RowIndex int IDENTITY(1,1))

INSERT INTO @products (ProductID, ProductName) SELECT ProductID, ProductName FROM products

DECLARE @totalRows int
DECLARE @rowIndex int

SELECT 
    @totalRows = COUNT(RowIndex), 
    @rowIndex = 1 
FROM @products

DECLARE @ProductID int
DECLARE @ProductName nvarchar(50)

WHILE(@rowIndex < @totalRows)
BEGIN

    SELECT @ProductID = ProductID, @ProductName = ProductName FROM @products WHERE RowIndex = @rowIndex

    -- Do here your stuff...
    PRINT LEFT(CAST(@productID as varchar) + '      ',6) + ' - ' + @productName 

    SET @rowIndex = @rowIndex + 1   

END

Answer 6

Why are you using a cursor for a simple fetch.. it's incredibly slow and will only process one row at a time! Stay clear of cursors at ALL costs!

You could retrieve them both as a new column with a simple select statement.

select convert(nvarchar(5),productID) + ' ' + productName as 'ID_Name' from products

The first part selects the Product ID as a string.. then it contaonates a 'space' (' ') then concatones the product name to the end of it.

You'd end up with

1 Apple

2 Banana

etc etc, and it'd be 1000x quicker than your current cursor

Hope that helps,

Wes