?
Solved

How to concatenate a sql string statement which contains a parameter within it ?

Posted on 2005-05-12
12
Medium Priority
?
249 Views
Last Modified: 2008-02-01
In the following SELECT statement of my stored procedure, do you know how I could break up, via a concatenated string, the value for @StateF at the end of the SELECT statement as follows ? I am passing in a variable length parm string into this stored procedure
via parm @StateF.

  INNER JOIN (dbo.Split(@StateF,'','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '

CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000)

Set @SQL =
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(@StateF,'','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '
0
Comment
Question by:zimmer9
12 Comments
 
LVL 3

Expert Comment

by:poaysee
ID: 13993012
Set @SQL = 'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(' + @StateF + ','','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13994009
You will need to enclose @StateF in single quote:

Set @SQL = 'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '
0
 

Author Comment

by:zimmer9
ID: 13996431
Do you know the syntax I would use to execute this stored procedure in Query Analyzer for the @StateF parameter ?
I pass a concatenated string of 2 character state codes. For ex: 'AL','AR','AZ' to @StateF.


CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000)

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,????,NULL

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 28

Expert Comment

by:rafrancisco
ID: 13996520
Try this:

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''AL'',''AR'',''AZ''',NULL
0
 

Author Comment

by:zimmer9
ID: 13996608
I get a syntax error in Query Analyzer when I run the following:

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''AL'',''AR'',''AZ''',NULL

SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(''AL','AR','AZ'',',') AS SP ON C.State = S.Value

WHERE S.FallCycle = 1   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.StocksFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0))  OR (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30' AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'AL'.
------------------------------------------------
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000)

AS

If exists(select * from dbo.sysobjects where name = 'tblFAllStates' and type = 'U')
Drop table tblFAllStates

Set @SQL =
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + '' '' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value

WHERE S.FallCycle = 1 '

If @Prop = 'A'
   BEGIN
     SET @SQL = @SQL + '  AND ((C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND  P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '  
     SET @SQL = @SQL + '  OR    (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0)) '
   END

If @Prop = 'B'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.BondsFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) AND P.PropertyType=''FIXED INCOME'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFA'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''MUTUAL FUND'' AND P.PlanNumber <> ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'MFN'
   BEGIN
     SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''MUTUAL FUND'' AND PlanNumber = ''NETWORKED'' AND LEN(P.IraCode) = 0) '
   END

If @Prop = 'O'
   BEGIN
      SET @SQL = @SQL + ' AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.MutualFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''OTHER'' AND LEN(P.IraCode) = 0) '  
   END

If @Prop = 'E'
   BEGIN
     SET @SQL = @SQL + '  AND (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.StocksFS) AS VARCHAR) + ''-06-30''  AND P.PropertyType=''EQUITY'' AND LEN(P.IraCode) = 0) '  
   END

If @Agg = 'A' AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + '  OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30'' AND (P.CashBalance>=[S].[AggregFS] OR P.Quantity>=[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg = 'B' AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30''  AND (P.CashBalance<[S].[AggregFS] AND P.Quantity<[S].[AggregFS]) AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @Agg Is Null AND (@Prop = 'A' OR @Prop = 'C')
   BEGIN
     SET @SQL = @SQL + ' OR (C.DateLost <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.CashFS) AS VARCHAR) + ''-06-30'' AND P.PropertyType=''CASH'' AND LEN(P.IraCode) = 0) '      
   END

If @IRA Is Not Null
   BEGIN
     SET @SQL = @SQL + ' OR ((C.DateOfBirth <= CAST(('+CAST(@RptYear AS VARCHAR)+' - S.IRAFS-71) AS VARCHAR) + ''-12-31'' AND LEN(P.IRACode) > 0 ) OR (LEN(C.DateOfBirth) = 0 AND LEN(C.Zip) > 0)) '
   END

--If @StateF Is Not Null
   --BEGIN
    -- AND C..State In (Select Value From dbo.Split(@StateF,'')
   --END
PRINT @SQL
EXEC(@SQL)
GO
--------------------------------------
CREATE FUNCTION SPLIT (
 @InputText Varchar(104),
 @Delimeter Varchar(10))
 
RETURNS @Array TABLE (  
   Value Varchar(4000))
As
BEGIN
 
  DECLARE

 @Pos Int,
 @End Int,
 @TextLength Int,
 @DelimLength Int

Set @TextLength = DataLength(@InputText)


If @TextLength = 0 RETURN

Set @Pos = 1
Set @DelimLength = DataLength(@Delimeter)

IF @DelimLength = 0 BEGIN
  WHILE @Pos <= @TextLength BEGIN
            INSERT @Array (Value)  Values (SubString(@InputText,@Pos,2))
            SET @Pos = @POS + 2
  END        
END

RETURN
END
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13996630
Change this part:

Set @SQL = 'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '

to this:

Set @SQL = 'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                INNER JOIN (dbo.Split(' + @StateF + ','','') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '
0
 

Author Comment

by:zimmer9
ID: 13996837
The function SPLIT requires 2 parameters, namely the concatenated string of states plus the delimeter character as follows:  

CREATE FUNCTION SPLIT (
 @InputText Varchar(104),
 @Delimeter Varchar(10))

this line of code:
INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = SP.Value

comes out of the Query Analyzer as follows:

INNER JOIN (dbo.Split('AL','AR','AZ',',') AS SP ON C.State = SP.Value
 
Does this look correct ?

I get the following syntax error:

AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                 INNER JOIN (dbo.Split('AL','AR','AZ',',') AS SP ON C.State = SP.Value
WHERE S.FallCycle = 1   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.StocksFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0))  OR (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30' AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ON'.



0
 

Author Comment

by:zimmer9
ID: 13996891
correction:
this line of code  

 INNER JOIN (dbo.Split(' + @StateF + ','','') AS SP ON C.State = SP.Value

comes out of the Query Analyzer as follows:

INNER JOIN (dbo.Split('AL','AR','AZ',',') AS SP ON C.State = SP.Value
 
Does this look correct ?

I get the following syntax error:

AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                 INNER JOIN (dbo.Split('AL','AR','AZ',',') AS SP ON C.State = SP.Value
WHERE S.FallCycle = 1   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.StocksFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0))  OR (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30' AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ON'.


0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13997243
Ok let's try this:

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''''AL'''',''''AR'''',''''AZ''''',NULL

and put this part back to this:

                INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value
0
 

Author Comment

by:zimmer9
ID: 13997317
EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''''AL'''',''''AR'''',''''AZ''''',NULL

with

 INNER JOIN (dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value

via Query Analyzer yields:

SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State
                 INNER JOIN (dbo.Split(''AL'',''AR'',''AZ'',',') AS SP ON C.State = SP.Value
WHERE S.FallCycle = 1   AND ((C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND  P.PropertyType='OTHER' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.BondsFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='FIXED INCOME' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND PlanNumber = 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.MutualFS) AS VARCHAR) + '-06-30' AND P.PropertyType='MUTUAL FUND' AND P.PlanNumber <> 'NETWORKED' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)   OR    (C.DateLost <= CAST((2005 - S.StocksFS) AS VARCHAR) + '-06-30'  AND P.PropertyType='EQUITY' AND LEN(P.IraCode) = 0))  OR (C.DateLost <= CAST((2005 - S.CashFS) AS VARCHAR) + '-06-30' AND P.PropertyType='CASH' AND LEN(P.IraCode) = 0)
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'AL'.


I'm wondering if the problem is that the function SPLIT is expecting 2 parameters
1) a variable string of 2 character states codes
2) a delimeter value

and the Exec is not being fed the delimeter value

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''''AL'''',''''AR'''',''''AZ''''',NULL
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13997384
Yes, the first Split param looks like a problem.

If you need quotes around each individual state value, then the string should be this:

...
INNER JOIN (dbo.Split('''AL'',''AR'',''AZ''',',') AS SP ON C.State = SP.Value
...
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13997479
I think I found the problem.  There was an extra open-parenthesis in the dbo.Split:

 INNER JOIN dbo.Split(' + '''' + @StateF + '''' + ','','') AS SP ON C.State = S.Value

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question