Problem in using where clause with dynamic sql

Other SQL Server Topics
1

Hi!

I am trying to write a query using dynamic sql in a sql stored procedure but getting error in that. Kindly see what i am doing wrong.

ALTER PROCEDURE [dbo].[ledger](@mainyear varchar(50))
AS
declare @transtb varchar(max)
declare @purtb varchar(max)
declare @query varchar(max)
declare @query2 varchar(max)
declare @query3 varchar(max)

BEGIN

if(@mainyear='')
begin
set @transtb='TRANSFER_DETAIL'
set @purtb='purchase'
end
else
begin
set @transtb='TRANSFER_DETAIL'+@mainyear
set @purtb='purchase'+@mainyear
end

set @query=N'insert into ledgertable select transferdate,voucherno,itemno,itemname,null,null,quantity,to_dept,remarks from '+@transtb+' where  TO_DEPT like '+QMSTORE+' ' 
set @query2=N'insert into ledgertable select transferdate,voucherno,itemno,itemname,null,quantity,null,to_dept,remarks from '+@transtb+' where  TO_DEPT not like '+QMSTORE+' ' 

set @query3=N'insert into ledgertable select purdt,voucherno,itemno,itemname,purqt,null,null,null,remarks from '+@purtb+''

execute(@query)
execute(@query2)
execute(@query3)
END

2

I would not recommend this type of dynamic stuff but try to change this section

where TO_DEPT like ''%QMSTORE%'''

3

I don't see where you have defined QMSTORE - I am assuming you want this as a parameter or variable. Either way - you should define that and include any wildcards.

For the mainyear parameter - set a default value to an empty string and define that as varchar(4) instead of varchar(50) - unless you can have a year designation that is 50 characters long.

With that - you can then build out your command string and pass that to sp_executeSql with parameters passed into the dynamic code. Here is an example:

Declare @mainyear varchar(4) = '2018'
      , @QMSTORE varchar(50) = '%some value%';

Declare @sqlCommand nvarchar(max) = '';

    Set @sqlCommand = '
 Insert Into dbo.ledgertable (
        transferdate
      , voucherno
      , itemno
      , itemname
      , {column name???}
      , {column name???}
      , quantity
      , to_dept
      , remarks
        )
 Select lt.transferdate
      , lt.voucherno
      , lt.itemno
      , lt.itemname
      , Null
      , Null
      , lt.quantity
      , lt.to_dept
      , lt.remarks
   From dbo.ledgertable%%%mainyear%%% lt
  Where lt.to_dept Like @qmstore;'

    Set @sqlCommand = replace(@sqlCommand, '%%%mainyear%%%', @mainyear);
  Print @sqlCommand;
--Execute sp_executeSql @sqlCommand, '@qmstore varchar(50)', @qmstore = @QMSTORE;

    Set @sqlCommand = '
 Insert Into dbo.ledgertable (
        transferdate
      , voucherno
      , itemno
      , itemname
      , {column name???}
      , {column name???}
      , quantity
      , to_dept
      , remarks
        )
 Select lt.transferdate
      , lt.voucherno
      , lt.itemno
      , lt.itemname
      , Null
      , Null
      , lt.quantity
      , lt.to_dept
      , lt.remarks
   From dbo.ledgertable%%%mainyear%%% lt
  Where lt.to_dept Not Like @qmstore;'

    Set @sqlCommand = replace(@sqlCommand, '%%%mainyear%%%', @mainyear);
  Print @sqlCommand;
--Execute sp_executeSql @sqlCommand, '@qmstore varchar(50)', @qmstore = @QMSTORE;

    Set @sqlCommand = '
 Insert Into dbo.ledgertable (
        transferdate
      , voucherno
      , itemno
      , itemname
      , {column name???}
      , quantity
      , {column name???}
      , to_dept
      , remarks
        )
 Select pt.purdt
      , pt.voucherno
      , pt.itemno
      , pt.itemname
      , pt.purqt
      , Null
      , Null
      , Null
      , pt.remarks
   From dbo.purchase%%%mainyear%%% pt'

    Set @sqlCommand = replace(@sqlCommand, '%%%mainyear%%%', @mainyear);
  Print @sqlCommand
--Execute sp_executeSql @sqlCommand;

Once the command string is created - we then update the command string to replace the main year identifier with the appropriate value that results in the specified table to be utilized.

Note: this will fail if you pass in an invalid year - or where you pass in a year that does not have an associated table. You should put some code in place to validate the year that is passed in is valid - if not, the code will fail because the expected yearly tables do not exist.

In the insert statements - it is a very good idea to name each column that you are inserting into...this way you can insure that you are putting the quantity value into the correct column depending on which statement is being executed. And - if those columns default to null you can drop them out of the insert statement completely.

And finally - even when creating dynamic SQL it is good practice to format the code for readability. Putting everything in a single line is much harder to read and debug.

4

What really helps during th edevelopment of dynamic SQL (in those cases that you really, really have to use it), is to first PRINT it before EXECUTEing it.

Replace

by

Check the generated scripts and run them on your test database. Only when they run correct and without error messages comment the PRINT statements and uncomment the EXECUTE statements.

You did not provide the error message, maybe a date or number had to be converted to a string first before including it in the string.
Do the PRINT-trick and debugging will be way faster.

With kind regards,
Wim