| Author |
 Topic  |
|
|
tech_1
Posting Yak Master
105 Posts |
 Posted - 02/24/2013 : 17:15:02
|
How can I do a string concatination with a select case statement?
whatever the "when" condition is, I want that to be concatinated to the @ssql string.
something like...
quote:
DECLARE @locationID int
SET @locationID = 1
DECLARE @ssql varchar(3000)
SET @ssql = @ssql + ' SELECT someColumn FROM SomeTable WHERE someColumn = 0 '
SELECT CASE @locationID
WHEN 1 THEN
SET @ssql = @ssql + ' AND EmployeePersonal.EmpJobLoc = ' + CONVERT(VARCHAR(10), @locationID) + ' '
END
thoughts? |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 02/24/2013 : 18:02:24
|
SELECT @ssql = CASE
WHEN @locationID =1 and SomeColumn = 0
THEN @ssql + ' AND EmployeePersonal.EmpJobLoc = ' + CONVERT(VARCHAR(10), @locationID) + ''' '
END
FROM someTable
but this is a bit of a guess, as I couldn't follow exactly what you wanted
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 02/25/2013 : 14:09:21
|
hmm.
almost. I want to do a select case on a parameter.
In the WHEN/THEN clause, I want to concatinate some string into another string parameter. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1523 Posts |
Posted - 02/25/2013 : 14:21:21
|
| Like Jim, I didn't exactly follow what you want either. Can you post what your final select statement would look like in one or two cases? Doesn't the original query you posted work right? |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 02/25/2013 : 15:50:01
|
DECLARE @locationID int
SET @locationID = 1
DECLARE @ssql varchar(3000) = ''
SET @ssql = @ssql + ' SELECT someColumn FROM SomeTable WHERE someColumn = 0 '
SET @ssql = @ssql +
CASE
WHEN @locationID = 1
THEN ' AND EmployeePersonal.EmpJobLoc = ' + CONVERT(VARCHAR(10), @locationID) + ' '
ELSE ''
END
SELECT @ssql |
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 02/25/2013 : 18:30:15
|
thanks. that almost did it but for some reason the @ssql variable gets cleared!
quote:
DECLARE @ssql NVARCHAR(3000)
SET @ssql = 'SELECT DISTINCT OnsiteRequestHdr.OnsiteRequestID, OnsiteRequestHdr.CustID, Customer.CustShipToCompany FROM OnsiteRequestHdr INNER JOIN '
SET @ssql = @ssql + 'OnsiteRequestDtl ON OnsiteRequestHdr.OnsiteRequestID = OnsiteRequestDtl.OnsiteRequestID INNER JOIN Customer ON OnsiteRequestHdr.CustID = Customer.CustID WHERE '
SET @ssql = @ssql + '@startDate >=OnsiteRequestDtl.StartDate AND '
SET @ssql = @ssql + '@endDate <= OnsiteRequestDtl.StopDate '
IF @empID != 'company'
BEGIN
SET @ssql = @ssql + 'AND OnsiteRequestDtl.EmpID = @empID '
PRINT @ssql
END
ELSE IF @searchBy = 'techs'
BEGIN
SET @ssql = 'SELECT DISTINCT OnsiteRequestHdr.OnsiteRequestID, OnsiteRequestHdr.CustID, Customer.CustShipToCompany FROM OnsiteRequestHdr INNER JOIN '
SET @ssql = @ssql + 'OnsiteRequestDtl ON OnsiteRequestHdr.OnsiteRequestID = OnsiteRequestDtl.OnsiteRequestID INNER JOIN '
SET @ssql = @ssql + 'Customer ON OnsiteRequestHdr.CustID = Customer.CustID INNER JOIN '
SET @ssql = @ssql + 'EmployeePersonal ON OnsiteRequestDtl.EmpID=EmployeePersonal.EmpID WHERE '
SET @ssql = @ssql + '@startDate >=OnsiteRequestDtl.StartDate AND '
SET @ssql = @ssql + '@endDate <= OnsiteRequestDtl.StopDate '
SELECT @ssql = @ssql + (
SELECT CASE @locationID
WHEN 1 THEN ' AND EmployeePersonal.EmpJobLoc = @locationID '
WHEN 2 THEN ' AND EmployeePersonal.EmpJobLoc = @locationID '
WHEN 10 THEN ' AND EmployeePersonal.EmpJobLoc = @locationID '
WHEN 12 THEN ' AND EmployeePersonal.EmpJobLoc = @locationID '
WHEN 13 THEN ' AND EmployeePersonal.EmpJobLoc = @locationID '
END
)
END
ELSE IF @locationID IS NOT NULL
BEGIN
PRINT @ssql
SET @ssql = @ssql + (
SELECT CASE @locationID
WHEN 1 THEN ' AND Customer.AcctOwnedBy = ''HART'' '
WHEN 2 THEN ' AND Customer.AcctOwnedBy = ''CLEV'' '
WHEN 10 THEN ' AND Customer.AcctOwnedBy = ''CHIC'' '
WHEN 12 THEN ' AND Customer.AcctOwnedBy = ''DFTW'' '
WHEN 13 THEN ' AND Customer.AcctOwnedBy = ''HOUS'' '
END
)
END
SET @ssql = @ssql + ' AND OnsiteRequestHdr.RequestCancelled = 0 '
PRINT @ssql -- cleared!
EXEC sp_executesql @ssql, N'@startDate datetime, @endDate datetime, @empID varchar(3), @locationID int',
@startDate = @startDate, @endDate = @endDate, @empID = @empID, @locationID = @locationID
|
|
|
|
tech_1
Posting Yak Master
105 Posts |
Posted - 02/25/2013 : 18:32:26
|
| ah! saw the difference. now it works. Thanks! |
|
|
| |
Topic |
|
concatination and case statement
