August 4, 2016 at 12:18 am
Hi ,
PROCEDURE Works
ALTER PROCEDURE [dbo].[SPGUNLUKSEVK_1]
@zaman NVARCHAR(20) ,
@ZAMAN2 NVARCHAR(20)
AS
DECLARE @baslangic DATETIME ,
@bitis DATETIME;
SET @baslangic = CONVERT(DATETIME, @zaman, 104);
SET @bitis = DATEADD(DAY, 1, CONVERT(DATETIME, @ZAMAN2, 104));
BEGIN
SELECT URUN.CODE [KODU] ,
CL.DEFINITION_ AS [CARİ_ADI] ,
URUN.NAME [ADI] ,
ST.AMOUNT [MIKTAR] ,
ST.PRICE * 1.18 [BIRIM FIYAT] ,
( ST.AMOUNT * ST.PRICE ) * 1.18 [TOPLAM]
FROM LG_016_01_STLINE ST ,
LG_016_ITEMS URUN ,
dbo.LG_016_CLCARD AS CL
WHERE ST.DATE_ >= @baslangic
AND ST.DATE_ < @bitis
AND ST.STOCKREF = URUN.LOGICALREF
AND ST.CLIENTREF = CL.LOGICALREF
AND ST.TRCODE IN ( 7, 8 )
AND URUN.CARDTYPE = 1;
END;
String Joın Procedure Error Message
Conversion failed when converting date and/or time from character string.
STORED PROCEDURE
ST.DATE_ >= ' + @baslangic + ' AND ST.DATE_ < ' + @bitis
+ ' AND
ALTER PROCEDURE [dbo].[SPGUNLUKSEVK_GENEL]
@zaman NVARCHAR(50) ,
@ZAMAN2 NVARCHAR(50) ,
@FRM NVARCHAR(3)
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) ,
@ITEMS NVARCHAR(30) ,
@CLCARD NVARCHAR(30) ,
@STLINE NVARCHAR(30);
DECLARE @baslangic DATETIME ,
@bitis DATETIME;
SET @baslangic = CONVERT(DATETIME, @zaman, 104);
SET @bitis = DATEADD(DAY, 1, CONVERT(DATETIME, @ZAMAN2, 104));
SET @STLINE = 'LG_' + @FRM + '_01_STLINE';
SET @CLCARD = 'LG_' + @FRM + '_CLCARD';
SET @ITEMS = 'LG_' + @FRM + '_01_ITEMS';
SET @SQL = N'
SELECT URUN.CODE [KODU],
CL.DEFINITION_ AS [CARİ_ADI],
URUN.NAME [ADI] ,
ST.AMOUNT [MIKTAR] ,
ST.PRICE *1.18 [BIRIM FIYAT],
(ST.AMOUNT * ST.PRICE) * 1.18 [TOPLAM]
FROM ' + @STLINE + ' ST ,
' + @ITEMS + ' URUN , ' + @ITEMS + ' AS CL
WHERE ST.DATE_ >= ' + @baslangic + ' AND ST.DATE_ < ' + @bitis
+ ' AND ST.STOCKREF = URUN.LOGICALREF AND ST.CLIENTREF = CL.LOGICALREF
AND ST.TRCODE IN ( 7, 8 ) AND URUN.CARDTYPE = 1
';
PRINT @SQL;
EXEC sp_executesql @SQL;
END;
August 4, 2016 at 1:37 am
In the second block of code, it looks like you're building a SQL query to then execute later. This is fine. However, what you're doing with the dates isn't quite right.
In a simplified form, this is what you're doing:
SET @SQL = '..... WHERE mydate = ' + @myDate + ' AND....'
What this does is generate a SQL Query that looks like:
...WHERE mydate = 8/3/2016 AND ...
What you need to do is wrap the date bits up in quotes:
SET @sql = '..... WHERE mydate = ' + QUOTENAME(@myDate, '''') + ' AND...'
which will generate a SQL Query that looks like it can be understood by SQL Server:
...WHERE mydate = '8/3/2016' AND...
I've used the QUOTENAME function as it is rather more secure than just putting extra 's in the middle of your string building bit.
Thomas Rushton
blog: https://thelonedba.wordpress.com
August 4, 2016 at 7:11 am
Don't convert your dates into strings just to convert them again back to dates.
Send the variables as parameters in the dynamic query. You just need to use sp_executesql
Here's a cleaner version of your procedure using SQL-92 joins.
ALTER PROCEDURE [dbo].[SPGUNLUKSEVK_GENEL]
@zaman NVARCHAR(50) ,
@ZAMAN2 NVARCHAR(50) ,
@FRM NVARCHAR(3)
AS
DECLARE @SQL NVARCHAR(MAX) ,
@ITEMS NVARCHAR(30) ,
@CLCARD NVARCHAR(30) ,
@STLINE NVARCHAR(30);
DECLARE @baslangic DATETIME ,
@bitis DATETIME;
SET @baslangic = CONVERT(DATETIME, @zaman, 104);
SET @bitis = DATEADD(DAY, 1, CONVERT(DATETIME, @ZAMAN2, 104));
SELECT @STLINE = QUOTENAME( 'LG_' + @FRM + '_01_STLINE'),
@CLCARD = QUOTENAME( 'LG_' + @FRM + '_CLCARD' ),
@ITEMS = QUOTENAME( 'LG_' + @FRM + '_01_ITEMS' );
SET @SQL = N'
SELECT URUN.CODE [KODU],
CL.DEFINITION_ AS [CARI_ADI],
URUN.NAME [ADI] ,
ST.AMOUNT [MIKTAR] ,
ST.PRICE *1.18 [BIRIM FIYAT],
(ST.AMOUNT * ST.PRICE) * 1.18 [TOPLAM]
FROM ' + @STLINE + ' ST
JOIN ' + @ITEMS + ' URUN ON ST.STOCKREF = URUN.LOGICALREF
JOIN ' + @ITEMS + ' CL ON ST.CLIENTREF = CL.LOGICALREF
WHERE ST.DATE_ >= @baslangic AND ST.DATE_ < @bitis
AND ST.TRCODE IN ( 7, 8 ) AND URUN.CARDTYPE = 1;';
PRINT @SQL;
EXEC sp_executesql @SQL, N'@baslangic DATETIME, @bitis DATETIME', @baslangic, @bitis;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply