Stored procedure parameter becomes blank

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 2005 Forums

Transact-SQL (2005)

Stored procedure parameter becomes blank

Author

Topic

CorkMan
Starting Member

6 Posts

Posted - 2010-04-12 : 10:58:25

Hi there, this is my first post so forgive me if this has already been covered. I tried using search but was not able to find a similar situation to mine. Anyhow in at the deep end :)

I have a stored procedure that is called by an application, where the application sends some data. The SP has two parameters that are both VarChar(20). The first is SerialNumber and the second is Lot.

For this example lets say the application is passing a unique serial number and a lot number that is common for thousands of calls.

The SP should execute and save the passed strings but for some reason one string data was not completely passed. From SQL Profiler I can see the data for Lot is passed like ' 2/100029009-123456' but when I query the record it is saved as blank.
If I run
SELECT ASCII(Lot),LEN(Lot), Lot FROM dbo.Table1 WHERE SerialNumber = '12345'
I get as a result
0 for the ASCII, 10 for the Length and a blank for the Lot.

How is this happening? Is T-SQL somehow converting ' 2/100029009-123456' to ASCII 0 or is it something else?

Many thanks,
Dave

================================================
Knowing is not enough you must apply! Willing is not enough you must do!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 11:04:14

Is it just a confusion over the column name and the parameter?

you say that the stored proc is passed params: SerialNumber, Lot

Actually they will be @SerialNumber and @Lot

Is it possible you just referenced the wrong thing (the column rather than the parameter)?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CorkMan
Starting Member

6 Posts

Posted - 2010-04-12 : 11:17:59

Sorry I should have entered it above as follows:
The SP has two parameters that are both VarChar(20). The first is @SerialNumber and the second is @Lot.

Normally the Lot is like '02/100029009-123456' where the first two characters represent the month. For some reason the Lot when passed to the SP appeared in Profiler as ' 2/100029009-123456'. I am wondering if this is processed like end of message or end of line for ASCII.

Many thanks,
Dave

================================================
Knowing is not enough you must apply! Willing is not enough you must do!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 11:19:20

Can you post the code for the stored proc?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CorkMan
Starting Member

6 Posts

Posted - 2010-04-12 : 11:36:58

Sorry but I had to change it a bit to remove any references that may be confidential such as database name, table name and so on.

USE [TestDB]
GO
Object StoredProcedure [dbo].[spSaveLotNumber]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spSaveLotNumber]
@SerialNumber VARCHAR(20),
@Lot VARCHAR(20),
AS

IF EXISTS (SELECT top 1 * FROM dbo.tblTable1 (NOLOCK)
WHERE SerialNumber = @SerialNumber
AND Lot = @Lot)
BEGIN
SELECT 121 As ErrorNumber -- Serial and lot already exists
END
ELSE
BEGIN
INSERT INTO dbo.tblTable1
(SerialNumber, Lot)
VALUES (@SerialNumber, @Lot)

SELECT 0 As ErrorNumber -- No error
END

Thanks,
Dave
================================================
Knowing is not enough you must apply! Willing is not enough you must do!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-12 : 11:45:00

how are you calling the SP? as a test, can you call it from QA? you look to have some sort of truncation of your parameteres. your planned input looks to be 20 chars, but 19 are displayed.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-12 : 11:50:25

yeah -- can't see anything fatally wrong with the sp. As Andrew suggested can you try running it standalone?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

CorkMan
Starting Member

6 Posts

Posted - 2010-04-13 : 03:43:46

Thanks for the input guys. I have a feeling that the VB application I wrote somehow sent the Lot number but with the first character as ASCII 0 so the T-SQL processed the whole string as blank.

My VB application uses an OLEDB connection and I have a function within the app to create a database connection and then call the sp and execute it. The exec...from the first post is what my app sends to SQL.

I think I better check my app to see how it could have sent ASCII 0 to SQL rather than how SQL processed it. Thanks again for your help.
Dave

================================================
Knowing is not enough you must apply! Willing is not enough you must do!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources