concatination and case statement

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

concatination and case statement

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 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
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) + ''' '
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

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 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[/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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources