Avatar of sfazal
sfazal
Flag for Canada 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
Microsoft SQL ServerASP.NETMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Éric Moreau

does one of the variable contains NULL?
sfazal

ASKER
if i comment the if statement, it will run.
Éric Moreau

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
sfazal

ASKER
Null to be inserted in the table column. I am passing it in the INSERT statement.
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Peter Kiprop

you cant cast NULL to nvarchar.
Vitor Montalvão

sfazal, do you still need help with this question?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.