SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 concatination and case statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

127 Posts

Posted - 02/24/2013 :  17:15:02  Show Profile  Reply with Quote
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
2869 Posts

Posted - 02/24/2013 :  18:02:24  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 02/25/2013 :  14:09:21  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3758 Posts

Posted - 02/25/2013 :  14:21:21  Show Profile  Reply with Quote
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?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/25/2013 :  15:50:01  Show Profile  Reply with Quote
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
Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 02/25/2013 :  18:30:15  Show Profile  Reply with Quote
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


Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 02/25/2013 :  18:32:26  Show Profile  Reply with Quote
ah! saw the difference. now it works. Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000