PRINT in sp with empty output

Question

Post reply

PRINT in sp with empty output

Michael Baas

Old Hand

Points: 319
More actions
November 10, 2008 at 12:59 pm

#383343

In a sp I have added a few PRINT-statements to show intermediate results. The output is empty at a few prints and I can't see why.
Here's the SP:
ALTER PROCEDURE [dbo].[PROC_VPM_new] (@theid int ,@mat int , @newid int OUTPUT)
AS
DECLARE @flds AS varchar(500)
DECLARE @sql AS varchar(600)
SET @flds=''
DECLARE @code AS varchar(20)
DECLARE @L AS int
DECLARE @CodeInt AS Int
DECLARE @NewcodeInt AS Int
SET @L = charindex('.',@code)-2
PRINT @L
SELECT @flds = @flds + ',' + c.name FROM sysobjects o JOIN syscolumns c ON o.id = c.id WHERE (o.xtype='U') AND o.name='stammvpm' and not c.name = 'id'
SET @sql = 'insert into stammvpm (' + @flds + ') select ' + @flds + ' from stammvpm where id = @theid'
EXEC(@sql)
SET @newid = ident_current('stammvpm')
DECLARE @newcode AS varchar(30)
SELECT @code = code FROM stammvpm WHERE id = @theid
PRINT 'code = ' + @code
IF @mat=1
BEGIN
SET @CodeInt = dbo.VarCode(@code,0)
PRINT 'CodeInt = ' + cast( @CodeInt As varchar(30))
SELECT @NewcodeInt = 1+max(dbo.VarCode(code,0)) FROM stammvpm WHERE left(code,@L+1) = left(@code,@L+1)
PRINT 'NewCodeInt = ' + cast(( @NewcodeInt+1) As varchar(30))
SELECT @newcode = 'L' + rtrim(cast(@NewcodeInt as char(27))) + '.00'
RINT 'newcode = ' + @newcode
END
ELSE
BEGIN
SET @CodeInt = dbo.VarCode(@code,1)
PRINT 'CodeInt = ' + cast( @CodeInt As varchar(30))
SELECT @NewcodeInt = max(dbo.VarCode(code,1)) FROM stammvpm WHERE left(code,@L+1) = left(@code,@L+1)
PRINT 'NewCodeInt = ' + cast(( @NewcodeInt+1) As varchar(30))
SELECT @newcode = left(@code,charindex('.',@code)) + cast((@NewcodeInt+1) as char(2))
PRINT 'newcode = ' + @newcode
END
SET @newid = ident_current('stammvpm')
UPDATE stammvpm SET code=Left(@newcode,@L+4) WHERE id=@newid
And then:
EXEC dbo.PROC_VPM_new @theid=22936, @mat=0, @newid=@myid OUTPUT
And the result is:
Warnungen: --->
W (1):
W (2): code = L100159.00
W (3): CodeInt = 0
W (4):
W (5):
<---
>[Fehler] Skriptzeilen: 3-6 -------------------------
Line 1: Incorrect syntax near ','.
The W(1) and 4/5 are empty and I just don't this. Maybe I've looked at it too long - but I don't see why this is happening 🙁 Would appreciate some comments...
Thanks
Michael

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

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

Remove the parenthesis from the parameter list... it's not a function...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Baas Old Hand Points: 319 More actions · Answer 2

Thanks, I fixed the header, but it didn't help with the syntax-err.

As far as the print-prob is concerned, I finally found the problem: wrong sequence of events - @code was not defined when I used it to compute @L :blush:

ALTER PROCEDURE [dbo].[PROC_VPM_new]

@theid int,

@mat int,

@newid int OUTPUT

AS

DECLARE @flds AS varchar(500)

DECLARE @sql AS varchar(600)

DECLARE @code AS varchar(20)

DECLARE @newcode AS varchar(20)

DECLARE @L AS Int

DECLARE @CodeInt AS Int

DECLARE @NewcodeInt AS Int

SET @flds=''

SELECT @flds = @flds + ',' + c.name FROM sysobjects o JOIN syscolumns c ON o.id = c.id WHERE (o.xtype='U') AND o.name='stammvpm' and not c.name = 'id'

SET @sql = 'insert into stammvpm (' + @flds + ') select ' + @flds + ' from stammvpm where id = @theid'

EXEC(@sql)

SET @newid = ident_current('stammvpm')

SELECT @code = code FROM stammvpm WHERE id = @theid

PRINT 'code = ' + @code

SET @L = charindex('.',@code)-2

PRINT @L

IF @mat=1

BEGIN

SELECT @NewcodeInt = 1+max(dbo.VarCode(code,0)) FROM stammvpm

PRINT 'NewCodeInt = ' + cast(( @NewcodeInt+1) As varchar(20))

SELECT @newcode = 'L' + rtrim(cast(@NewcodeInt as char(17))) + '.00'

PRINT 'newcode = ' + @newcode

END

ELSE

BEGIN

SET @CodeInt = dbo.VarCode(@code,1)

PRINT 'CodeInt = ' + cast( @CodeInt As varchar(20))

SELECT @NewcodeInt = max(dbo.VarCode(code,1)) FROM stammvpm WHERE left(code,@L) = left(@code,@L)

PRINT 'NewCodeInt = ' + cast(( @NewcodeInt+1) As varchar(20))

SELECT @newcode = left(@code,charindex('.',@code)) + right('00'+Ltrim(Str(@NewcodeInt+1,2,0)),2)

PRINT 'newcode = ' + @newcode

END

SET @newid = ident_current('stammvpm')

UPDATE stammvpm SET code=Left(@newcode,@L+4) WHERE id=@newid

Warnungen: --->
W (1): code = L100159.00
W (2): 6
W (3): CodeInt = 0
W (4): NewCodeInt = 1
W (5): newcode = L100159.01
<---
>[Fehler] Skriptzeilen: 3-6 -------------------------
Line 1: Incorrect syntax near ','.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

It's not obvious where these prints come from:

W (4):
W (5):
W (6): 23517
<---
>[Fehler] Skriptzeilen: 3-6 -------------------------

I guess it fails when you call the function dbo.VarCode(code,0)

Can you post the code of this function?

_____________
Code for TallyGenerator

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 4

use Isnull function in Print statement (eg: print isnull (@L, 'Not found') ). Becauase if variable have any null values, print statement won't display it.

Michael Baas Old Hand Points: 319 More actions · Answer 5

Sorry - I've been editing my previous post and was distracted while doing so, so it all took longer than expected and that has brought the discussion a bit out of context.

To recap: the print-problem is solved now (my bad) - but the 'incorrect syntax'-issue is still confusing me...

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Oh, it's so obvious!

I must've been blind! Or drunk.... 😉

SET @flds=''

....

SELECT @flds = @flds + ',' + c.name FROM sysobjects o JOIN syscolumns c ON o.id = c.id WHERE (o.xtype='U') AND o.name='stammvpm' and not c.name = 'id'

You add extra comma on the beginning on your @flds string.

Of course, it gives you error when you try to use in in dynamic SQL.

1. Remove

SET @flds=''

2. For building lists use this construction:

SELECT @flds = ISNULL(@flds + ',', '') +c.name FROM ...

For the first name in the list it does not add comma because @flds is still NULL for it.

_____________
Code for TallyGenerator

Michael Baas Old Hand Points: 319 More actions · Answer 7

Sergiy,

the VarCode-function works fine. It is used to either return the main-code or the variante-code of an existing-code:

select code,dbo.VarCode(code,0),dbo.VarCode(code,1) from stammvpm where id=22936

code column2 column3
---------------- ---------- ----------
L100159.00 100159 0

The PRINTS were all coming from the sp (except for the last one which was used to PRINT @newid after EXEC the sp) - I have removed that one in the edited post, as it was confusing...

Thanks

Michael

Michael Baas Old Hand Points: 319 More actions · Answer 8

Thanks, Sergiy, you're a star 😉

I should have added a PRINT @sql, would have saved quite a bit of headache 😉

Many thanks

Michael