• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 35
  • Last Modified:

SP problem

Hi All,

I have SP with script below :

  SET @SCRIPT = @SCRIPT + ' WHERE CONVERT(Char(8), A.TglJurnal, 112) '
         SET @SCRIPT = @SCRIPT + ' BETWEEN ' + CONVERT(Char(8), @FromDate , 112)  
         SET @SCRIPT = @SCRIPT + ' AND ' + CONVERT(Char(8), @ToDate , 112)

         SET @SCRIPT = @SCRIPT + ' AND A.BuktiJurnal = ' +  @BuktiJurnal

The result :

WHERE CONVERT(Char(8), A.TglJurnal, 112)  
BETWEEN 20180101 AND 20180131
AND A.BuktiJurnal = BK 0023/YSL/2017/I/18

No apostrophe at the date and string variable.

What's wrong with the script ?

Thank you.
0
emi_sastra
Asked:
emi_sastra
  • 3
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters I don't see any text delineating single quotes..

Should be..

SET @SCRIPT = @SCRIPT + ' WHERE CONVERT(Char(8), A.TglJurnal, 112) '
SET @SCRIPT = @SCRIPT + ' BETWEEN ''' + CONVERT(Char(8), @FromDate , 112) 
SET @SCRIPT = @SCRIPT + ''' AND ''' + CONVERT(Char(8), @ToDate , 112) + ''
SET @SCRIPT = @SCRIPT + ' AND A.BuktiJurnal = '' +  @BuktiJurnal + '''

Open in new window


WHERE CONVERT(Char(8), A.TglJurnal, 112)  
BETWEEN '20180101' AND '20180131'
AND A.BuktiJurnal = 'BK 0023/YSL/2017/I/18' 

Open in new window


Also what is the data type of A.TglJurnal?  If it's a date then you won't need any of the convert functions at all, assuming both @FromDate and @ToDate are dates, which would make this much easier.  Also keep in mind that converting to text it will treat it as a text compare and not a date compare.

SET @SCRIPT = @SCRIPT + ' WHERE A.TglJurnal BETWEEN ''' + @FromDate + ''' AND ''' + @ToDate + ''
SET @SCRIPT = @SCRIPT + ' AND A.BuktiJurnal = '' +  @BuktiJurnal + '''

Open in new window


Also of course what's the point of using @script as dynamic T-SQL?  Unless there is a hard reason to use dynamic sql, better to avoid it and make it a straight SQL statement.

WHERE A.TglJurnal BETWEEN @FromDate AND @ToDate 
	AND A.BuktiJurnal =  @BuktiJurnal

Open in new window

0
 
emi_sastraAuthor Commented:
Hi Jim,

- Also of course what's the point of using @script as dynamic T-SQL?  Unless there is a hard reason to use dynamic sql, better to avoid it and make it a straight SQL statement.

Please see the attached Sql Script.

Any suggestion are welcom.

Thank you.
ERV_SP_Print_Ledger_Detail_Jurnal.sql
0
 
emi_sastraAuthor Commented:
Hi Jim,

Hi Jim,

Where to put UNION ALL from the SQL Script I attached ?


USE [YSL_TRADING]
GO

DECLARE      @return_value int

EXEC      @return_value = [dbo].[ERV_SP_Print_Ledger_Detail_Jurnal]
            @FromDate = '2018/01/01',
            @ToDate = '2018/02/28',
            @FromAccNo = NULL,
            @ToAccNo = NULL,
            @LawanAccNo = NULL,
            @ListAccNo = NULL,
            @BuktiJurnal = ' '

--SELECT      'Return Value' = @return_value

GO

Thank you.
0
 
Mark WillsTopic AdvisorCommented:
Have a look at :
	WHILE @strYearMonth <= @strToMonth

    BEGIN
	  
	   SET @SCRIPT = @SCRIPT + 'SELECT ' 
	   SET @SCRIPT = @SCRIPT + ' A.BuktiJurnal ' 
	   SET @SCRIPT = @SCRIPT + ', A.TglJurnal  ' 
	   SET @SCRIPT = @SCRIPT + ', A.KetHeadJurnal  ' 
	   SET @SCRIPT = @SCRIPT + ', A.TipeJurnal  ' 
	   SET @SCRIPT = @SCRIPT + ', A.StsClose  ' 
	   SET @SCRIPT = @SCRIPT + ', A.AutoYN  ' 
	   SET @SCRIPT = @SCRIPT + ', A.StsCheck  ' 
	   SET @SCRIPT = @SCRIPT + ', C.Noseq   ' 
	   SET @SCRIPT = @SCRIPT + ', B.AccNo  ' 
	   SET @SCRIPT = @SCRIPT + ', C.KetDetJurnal  ' 
	   SET @SCRIPT = @SCRIPT + ', C.Debet  ' 
	   SET @SCRIPT = @SCRIPT + ', C.Kredit  ' 
	   SET @SCRIPT = @SCRIPT + ', C.DetStsCheck  ' 
 
	   SET @SCRIPT = @SCRIPT + ' FROM ' + @strHeaderFileName + @strYearMonth + ' A WITH (NOLOCK)'
	   SET @SCRIPT = @SCRIPT + ' INNER JOIN '  + @strDetailFileName + @strYearMonth + ' B WITH (NOLOCK)'
	   SET @SCRIPT = @SCRIPT + ' ON A.BuktiJurnal = B.BuktiJurnal ' 

	   SET @SCRIPT = @SCRIPT + ' INNER JOIN '  + @strDetailFileName + @strYearMonth + ' C WITH (NOLOCK)'
	   SET @SCRIPT = @SCRIPT + ' ON B.BuktiJurnal = C.BuktiJurnal ' 

	   SET @SCRIPT = @SCRIPT + ' WHERE CONVERT(Char(8), A.TglJurnal, 112) '
	   SET @SCRIPT = @SCRIPT + ' BETWEEN ''' + CONVERT(Char(8), @FromDate , 112) + '''' 
	   SET @SCRIPT = @SCRIPT + ' AND ''' + CONVERT(Char(8), @ToDate , 112) + ''''
	   SET @SCRIPT = @SCRIPT + ' AND A.BuktiJurnal = ''' +  @BuktiJurnal +'''' 

	  IF @strYearMonth < @strToMonth  SET @SCRIPT = @SCRIPT + char(13)+char(10)+'UNION ALL'+char(13)+char(10)  

	   SET @strYearMonth = CONVERT(Char(6), (DATEADD(month, 1,  LEFT(@strYearMonth, 4) + '/' + RIGHT(@strYearMonth, 2) + '/01')), 112)
	END

Open in new window

Always a challenge when having to encapsulate single quotes

To encapsulate a single quote inside a single quoted string you need two single quotes each time you want a single quote to persist in the result. It is compounded a bit when adding strings and declaratives/fields because it looks like you have additional single quotes but really they are for start of string and end of string.

e.g.

declare @str varchar(max) = ' select '''+convert(char(8),getdate(),112)+''' as [date]'
print @str
0
 
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
0
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.

Join & Write a Comment

Featured Post

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.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now