Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-24 : 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 intSET @locationID = 1DECLARE @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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-24 : 18:02:24
|
SELECT @ssql = CASE WHEN @locationID =1 and SomeColumn = 0 THEN @ssql + ' AND EmployeePersonal.EmpJobLoc = ' + CONVERT(VARCHAR(10), @locationID) + ''' ' ENDFROM someTable but this is a bit of a guess, as I couldn't follow exactly what you wantedJimEveryday I learn something that somebody else already knew |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-25 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-25 : 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
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-02-25 : 15:50:01
|
[code]DECLARE @locationID intSET @locationID = 1DECLARE @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 ''ENDSELECT @ssql[/code] |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-25 : 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
129 Posts |
Posted - 2013-02-25 : 18:32:26
|
ah! saw the difference. now it works. Thanks! |
|
|
|
|
|
|
|