sfazal
asked on
SQL Server Stored Procedure.
Hi.
I am trying TO call this sp AND it dont EXECUTE the @sql STATEMENT, IF i look it IN watch its EMPTY. what could be the reaosn FOR it.
EXEC Update_Transaction '22','0',0
CREATE PROCEDURE [dbo].[Update_Transaction]
@TransactionEntryID INT ,
@UserID INT ,
@ID INT OUT
AS
BEGIN
DECLARE @sql VARCHAR(max);
SET NOCOUNT ON;
IF @UserID = 0
BEGIN
SET @UserID = NULL;
END
BEGIN
SET @sql = 'INSERT INTO + table1' +
'( TransactionEntryID ,
UserID ,
)
VALUES ('+ cast(@TransactionEntryID as varchar(100))+', '+ cast(@UserID as varchar(100)) +')'
EXEC (@sql)
END
END
I am trying TO call this sp AND it dont EXECUTE the @sql STATEMENT, IF i look it IN watch its EMPTY. what could be the reaosn FOR it.
EXEC Update_Transaction '22','0',0
CREATE PROCEDURE [dbo].[Update_Transaction]
@TransactionEntryID INT ,
@UserID INT ,
@ID INT OUT
AS
BEGIN
DECLARE @sql VARCHAR(max);
SET NOCOUNT ON;
IF @UserID = 0
BEGIN
SET @UserID = NULL;
END
BEGIN
SET @sql = 'INSERT INTO + table1' +
'( TransactionEntryID ,
UserID ,
)
VALUES ('+ cast(@TransactionEntryID as varchar(100))+', '+ cast(@UserID as varchar(100)) +')'
EXEC (@sql)
END
END
does one of the variable contains NULL?
ASKER
if i comment the if statement, it will run.
so your statement does not run when @UserId = 0?
This is because you are concatenating a NULL with a string and the NULL is propagating (turning everything to NULL).
Are you trying to send a NULL to your SP?
This is because you are concatenating a NULL with a string and the NULL is propagating (turning everything to NULL).
Are you trying to send a NULL to your SP?
ASKER
Null to be inserted in the table column. I am passing it in the INSERT statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you cant cast NULL to nvarchar.
sfazal, do you still need help with this question?