SQL Server SP runtime generated Insert statement parameters.

I am executing below stored procedure and want the parameters to be passed to a runtime generated INSERT statement.

EXEC dbo.Update_TransactionEntry 23,
                    NULL, NULL, 1, '2015-06-07 10:04:16.303', 'Request has been initiated.',
                    NULL, 1, '2015-06-07 10:04:16.303', '', 'sdf', '', '', 0,0,0,0,
                              'eb_prlmsctrx','eb_prlmsctrx_status',0
                              
                              Below is the definition of SP. in this SP can you please mention whats the problem of INSERT INTO statement as its not picking up parameters values?

CREATE PROCEDURE [dbo].[Update_TransactionEntry]
    @TransactionEntryID INT ,
    @MainApproverUserID INT ,
    @ApprovedByUserID INT ,
    @RequestStatusID INT ,
    @UpdateDate DATETIME ,
    @Remarks NVARCHAR(200) ,
    @WorkflowID INT ,
    @Processed BIT ,
    @ProcessedDate DATETIME ,
    @MappingType NVARCHAR(10) ,
      @transremarks NVARCHAR(300),
    @groupidd INT ,
    @groupcode NVARCHAR(50) ,
      @level INT,
      @workflowmaster INT,
      @approvingline int,
      @levelversion VARCHAR(50),
      @transtable VARCHAR(MAX),
      @statustable VARCHAR(MAX),
    @ID INT OUT
AS
    BEGIN

      DECLARE       @sql VARCHAR(max);
                  

SET @sql = 'INSERT  INTO ' + @statustable +
                '( TransactionEntryID ,
                  MainApproverUserID ,
                  ApprovedByUserID ,
                  RequestStatusID ,
                  UpdateDate ,
                  Remarks ,
                  WorkflowID ,
                  Processed ,
                  ProcessedDate ,
                  WorkflowMasterID ,
                  UserLevel ,
                  MappingType ,
                          transremarks,
                  groupidd ,
                  groupcode,
                          approvingline,
                          levelversion
                )
  VALUES  (@TransactionEntryID  + ',' +
                       @MainApproverUserID  ,
                   @ApprovedByUserID   ,
                   @RequestStatusID  ,
                   @UpdateDate  ,
                  @Remarks  ,
                   @WorkflowID  ,
                   @Processed   ,
                   @ProcessedDate   ,
                   @WorkflowMasterID   ,
                   @level   ,
                   @MappingType  ,
                           @transremarks ,
                   @groupidd  ,
                   @groupcode  ,
                           @approvingline ,
                           @levelversion ) '
                                                       EXEC (@sql)
                                                      
                                                      end
sfazalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
I think error is here ::  VALUES  (@TransactionEntryID  + ',' +
0
sfazalAuthor Commented:
whats the solution?
0
Deepak ChauhanSQL Server DBACommented:
Try like this.
EXEC dbo.Update_TransactionEntry 23,
                     'NULL', 'NULL', 1, '2015-06-07 10:04:16.303', 'Request has been initiated.',
                     'NULL', 1, '2015-06-07 10:04:16.303', '''''', 'sdf', '''''', '''''', '0','0','0','0',
                               'eb_prlmsctrx','eb_prlmsctrx_status','0'  
 
 

Open in new window


SP execution
Create PROCEDURE [dbo].[Update_TransactionEntry]
     @TransactionEntryID varchar(100) , 
     @MainApproverUserID Varchar(100) ,
     @ApprovedByUserID Varchar(100) ,
     @RequestStatusID Varchar(100) ,
     @UpdateDate varchar(100) ,
     @Remarks NVARCHAR(200) ,
     @WorkflowID Varchar(100) ,
     @Processed varchar(1) ,
     @ProcessedDate varchar(100) ,
     @MappingType NVARCHAR(10) ,
     @transremarks NVARCHAR(300),
     @groupidd Varchar(100) ,
     @groupcode NVARCHAR(50) ,
     @level Varchar(100),
     @workflowmaster Varchar(100),
       @approvingline Varchar(100),
       @levelversion VARCHAR(50),
       @transtable VARCHAR(MAX),
       @statustable VARCHAR(MAX),
     @ID Varchar(100) OUT
 AS
     BEGIN
DECLARE @sql VARCHAR(max);
declare @quot varchar(1)
set @quot=''''

SET @sql = 'INSERT  into  ' + @statustable +
                 '(TransactionEntryID ,
                   MainApproverUserID ,
                   ApprovedByUserID ,
                   RequestStatusID ,
                   UpdateDate ,
                   Remarks ,
                   WorkflowID ,
                   Processed ,
                   ProcessedDate ,
                   WorkflowMasterID ,
                   UserLevel ,
                   MappingType ,
                   transremarks,
                   groupidd ,
                   groupcode,
                   approvingline,
                   levelversion) VALUES ('+@TransactionEntryID+', '+
                     @MainApproverUserID  +','+
                    @ApprovedByUserID  +', '+
                    @RequestStatusID  +', ' +''''+
                    @UpdateDate +''''+ ','+ ''''+
                    @Remarks+'''' +', '+
                    @WorkflowID +', '+
                    @Processed +', '+''''+
                    @ProcessedDate +'''' +', '+
                    @WorkflowMaster  +', '+
                    @level+','+
                    @MappingType +', '+''''+
                    @transremarks+''''+', '+
                    @groupidd +', '+
                    @groupcode +', '+
                            @approvingline+', '+
                            @levelversion
                            
                            +')' 
                    exec (@sql)
                                                       
                     end 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sfazalAuthor Commented:
Why can't I do without changing data types?
0
Deepak ChauhanSQL Server DBACommented:
Because in this SP Varchar and INT datatype is being concatenated and SQL Server implicitly converts between datatypes on concatenation or addition. if you try to combine an int and a string it will always attempt to convert the string to an int unless you tell it otherwise explicitly.

So you can use Cast function at run time or you can change datatype on declaration.
0
sfazalAuthor Commented:
can you show me with the cast query?
0
Deepak ChauhanSQL Server DBACommented:
You can use cast like this.

EXEC dbo.Update_TransactionEntry 23,' ', ' ', 1, '2015-06-07 10:04:16.303','Request has been initiated.',
                       ' ', '1', '2015-06-07 10:04:16.303','0','sdf', '0', '0', '0','0','0','0',
                        'eb_prlmsctrx','eb_prlmsctrx_status','0' 


Alter PROCEDURE [dbo].[Update_TransactionEntry]
     @TransactionEntryID INT ,     @MainApproverUserID INT ,   @ApprovedByUserID INT ,       @RequestStatusID INT, 
     @UpdateDate DATETIME ,        @Remarks VARCHAR(200) ,    @WorkflowID INT ,              @Processed BIT ,
     @ProcessedDate DATETIME ,     @MappingType VARCHAR(10) , @transremarks VARCHAR(300),    @groupidd INT ,     
     @groupcode VARCHAR(50) ,     @level INT,                 @workflowmaster INT,           @approvingline int, 
     @levelversion VARCHAR(50),    @transtable VARCHAR(MAX),   @statustable VARCHAR(MAX),     @ID varchar(100) OUT
 AS
 BEGIN

DECLARE @sql VARCHAR(max);

SET @sql = 'INSERT  into  ' + @statustable +
                 '(TransactionEntryID , MainApproverUserID , ApprovedByUserID ,RequestStatusID ,UpdateDate ,
                   Remarks ,            WorkflowID ,         Processed ,       ProcessedDate ,  WorkflowMasterID ,
                   UserLevel ,          MappingType ,        transremarks,     groupidd ,       groupcode,
                   approvingline,       levelversion
                   
) VALUES ('+ cast(@TransactionEntryID as varchar(100))+', '+ cast(@MainApproverUserID as varchar(100))  +','+
             cast(@ApprovedByUserID as varchar(100))  +', '+ cast(@RequestStatusID as varchar(100))  +', ' +''''+
             cast(@UpdateDate as varchar(100)) +''''+ ','+ ''''+cast(@Remarks as varchar(100))+'''' +', '+
             cast(@WorkflowID as varchar(100)) +', '+           cast(@Processed as varchar(100)) +', '+''''+
             cast(@ProcessedDate as varchar(100)) +'''' +', '+  cast(@WorkflowMaster as varchar(100))  +', '+
             cast(@level as varchar(100))+','+                cast(@MappingType as varchar(100)) +', '+
             cast(@transremarks as varchar(100))+', '+        cast(@groupidd as varchar(100)) +', '+
             cast(@groupcode as varchar(100)) +', '+          cast(@approvingline as varchar(100))+', '+
             cast(@levelversion as varchar(100))
                            
                            
+')' 
                    Exec (@sql)
                    
                    end 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
CREATE PROCEDURE [dbo].[Update_TransactionEntry]
    @TransactionEntryID int ,
    @MainApproverUserID int ,
    @ApprovedByUserID int ,
    @RequestStatusID int ,
    @UpdateDate datetime ,
    @Remarks nvarchar(200) ,
    @WorkflowID int ,
    @Processed bit ,
    @ProcessedDate datetime ,
    @MappingType nvarchar(10) ,
    @transremarks nvarchar(300),
    @groupidd int ,
    @groupcode nvarchar(50) ,
    @level int,
    @workflowmaster int,
    @approvingline int,
    @levelversion varchar(50),
    @transtable varchar(8000),
    @statustable varchar(8000),
    @ID int OUTPUT
AS
SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

SET @sql = 'INSERT  INTO [' + @statustable + ']
            ( TransactionEntryID ,
              MainApproverUserID ,
              ApprovedByUserID ,
              RequestStatusID ,
              UpdateDate ,
              Remarks ,
              WorkflowID ,
              Processed ,
              ProcessedDate ,
              WorkflowMasterID ,
              UserLevel ,
              MappingType ,
              transremarks,
              groupidd ,
              groupcode,
              approvingline,
              levelversion
            )
   VALUES   ( @TransactionEntryID ,
              @MainApproverUserID  ,
              @ApprovedByUserID   ,
              @RequestStatusID  ,
              @UpdateDate  ,
              @Remarks  ,
              @WorkflowID  ,
              @Processed   ,
              @ProcessedDate   ,
              @WorkflowMaster   ,
              @level   ,
              @MappingType  ,
              @transremarks ,
              @groupidd  ,
              @groupcode  ,
              @approvingline ,
              @levelversion ) '

EXEC sp_executesql @sql, N'
              @TransactionEntryID int ,
              @MainApproverUserID int ,
              @ApprovedByUserID int ,
              @RequestStatusID int ,
              @UpdateDate datetime ,
              @Remarks nvarchar(200) ,
              @WorkflowID int ,
              @Processed bit ,
              @ProcessedDate datetime ,
              @workflowmaster int,
              @level int,
              @MappingType nvarchar(10) ,
              @transremarks nvarchar(300),
              @groupidd int ,
              @groupcode nvarchar(50) ,
              @approvingline int,
              @levelversion varchar(50) ',
              @TransactionEntryID,
              @MainApproverUserID,
              @ApprovedByUserID,
              @RequestStatusID,
              @UpdateDate,
              @Remarks,
              @WorkflowID,
              @Processed,
              @ProcessedDate,
              @workflowmaster,
              @level,
              @MappingType,
              @transremarks,
              @groupidd,
              @groupcode,
              @approvingline,
              @levelversion

SET @ID = SCOPE_IDENTITY()
GO --end of proc
0
sfazalAuthor Commented:
Thanks!
0
Scott PletcherSenior DBACommented:
It's a very bad idea to cast columns to character like that.  Especially if don't do it extremely carefully.

For example, you're casting @transremarks varchar(300) like this:
cast(@transremarks as varchar(100))
which will truncate anything beyond 100 bytes, without warning (because you explicitly cast it).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.