Error in while concatenating in sql query

Posted by Klbaiju under Sql Server on 1/15/2015 | Points: 10 | Views : 649 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

hi
following is a working sql query

declare @dte as datetime ='2015-01-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);

;WITH Dates AS(
    SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
    FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
         (VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query =  'SELECT m.empno ' + CHAR(13)
    + (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
        FROM Dates
        WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'
        ORDER BY Month_date
        FOR XML PATH(''),TYPE).value('.','varchar(max)')
    + '
    FROM employee m
    LEFT
    JOIN shiftdetails b ON m.empno = b.empno
    GROUP BY m.empno '


EXEC sp_executesql @Query

my requirement is i want to make a change in the following line

WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'

i want to change the above line as

WHERE Month_date BETWEEN '"+stdate+"' AND '"+endate+"'

when i try this code it showed error.
next requirement is
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno '
here i want to add one where condition as
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno where b.empno='"+empno+"' and b.shifttype='MS'
GROUP BY m.empno '

how it is possible

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 1/16/2015 [Member] [MVP] Platinum | Points: 25

0	Hi, You have to use single quotes twice instead of double quotes... WHERE Month_date BETWEEN '''+@StDt+''' AND '''+@EnDt+''' Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Klbaiju on: 1/16/2015 [Member] Starter | Points: 25

0	Hi bandi, your code will work if it is in storedprocedure. following is complete query string endate = Request.QueryString["stdate"]; string stdate=Request.QueryString["endate"]; SqlConnection con = new SqlConnection(@"Data Source=.\sqlserver;Initial Catalog=TechStreet;Integrated Security=True"); string strMsg = @"declare @dte as datetime ='2015-01-01' declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0) declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0)); DECLARE @query AS NVARCHAR(MAX); ;WITH Dates AS( SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date FROM (VALUES(0),(0),(0),(0),(0),(0))E(N), (VALUES(0),(0),(0),(0),(0),(0))E2(N) ) SELECT @Query = 'SELECT m.empno ' + CHAR(13) + (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13) FROM Dates WHERE Month_date BETWEEN '''+stdate+''' AND '''+endate+''' ORDER BY Month_date FOR XML PATH(''),TYPE).value('.','varchar(max)') + ' FROM employee m LEFT JOIN shiftdetails b ON m.empno = b.empno GROUP BY m.empno' EXEC sp_executesql @Query"; Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Hi bandi,
your code will work if it is in storedprocedure.
following is complete query
string endate = Request.QueryString["stdate"];
string stdate=Request.QueryString["endate"];
SqlConnection con = new SqlConnection(@"Data Source=.\sqlserver;Initial Catalog=TechStreet;Integrated Security=True");

string strMsg = @"declare @dte as datetime ='2015-01-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);

;WITH Dates AS(
SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
(VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query = 'SELECT m.empno ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN '''+stdate+''' AND '''+endate+'''
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)')
+ '
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno'

EXEC sp_executesql @Query";

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/16/2015 [Member] [MVP] Platinum | Points: 25

0	Can you post the exact error message which you got ? Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Klbaiju, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts