Select Statement In Table (Valid Expressions Error).

DB_Newbie

We are currently implimenting row level security by putting part of the Customer's account information in a DB Table. I have been able to do this on a case by case (individual customer) basis, but I tried to script inserting a whole bunch of customers at once using a script. When I run the script just using print commands, everything looks fine. When I run it to actually process the data, things bomb out.

Example Customer Table being sent to me:
Name AccountNo
----- -----------
abc A5071
def 57791
ghi 8W551
jkl 07A11 & 13866
mno 015X1, 0725A, 07551

Note there can be more than one leading zero in the accountNo. So I want my new table ("New_Cust_Table") to look like:

UserId Pwd SelectStmt
------ ------ -------------------------------
abc1 abc1# And AccountNo LIKE '%A5R7'
def1 def1# And AccountNo LIKE '%5779'
ghi1 ghi1# And AccountNo LIKE '%8W55'
jkl1 jkl1# And (AccountNo Like '%07A11' OR Account LIKE '%13866')
... etc.

Ignoring the Cust Names with multiple Account numbers for the moment, my SQL Looks like this:

-- DECLARE VARIABLES
DECLARE @UserID varchar(20)
DECLARE @UserPwd varchar(20)
DECLARE @SelectStmt varchar(200)
DECLARE NewCustUser CURSOR FOR

SELECT Name, AccountNo FROM Cust_Table
OPEN NewCustUser

FETCH NEXT FROM NewCustUser INTO @UserID @SelectStmt
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (LEN(@SelectStmt) <= 6) -- ACTUALLY GET MULTIPLE ACCOUNT Nos for ONE Customer
Begin
Print 'Account is ' +@SelectStmt+' for '+@UserID
SET @SelectStmt = 'AND AccountNo LIKE ''%'+@SelectStmt+''''
Print @SelectStmt
SET @Statement = N'INSERT INTO [dbo].[New_Cust_Table] VALUES ('''+@UserID+'1'','''+@UserID+'1#'','''+@SelectStmt+''')'
Print @Statement
EXEC sp_executesql @Statement
END
FETCH NEXT FROM NewCustUser INTO @UserID @SelectStmt
END

CLOSE NewCustUser
DEALLOCATE NewCustUser

Some of the error message I get:
- Incorrect syntax near ''.
- The name "A5R7" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Perhaps there is a CAST, CONVERT or COLLATE is needed I have experimented with those and have not found anything that works just right yet...

DaleJ

I can't tell from your post what is a single quote and what is a double quote.

But, it appears that there is an imbalance in this statement

SET @Statement = N'INSERT INTO [dbo].[New_Cust_Table] VALUES ('''+@UserID+'1'','''+@UserID+'1#'','''+@SelectStmt+''')'

And the @SelectStmt variable is being read as code and not as a literal.

rh4m1ll3

hi you can actually optimize your proc into something like this

INSERT
INTO dbo.New_Cust_Table
SELECT [Name] + '1' AS UserId
, [Name] + '1#' AS Pwd
, 'And AccountNo LIKE ''%' + AccountNo + '''' AS SelectStmt
FROM Cust_Table

with the case of multiple account nos.

INSERT
INTO dbo.New_Cust_Table
SELECT [Name] + '1' AS UserId
, [Name] + '1#' AS Pwd
, dbo.ParseAccountNo(AccountNo) AS SelectStmt
FROM Cust_Table

where dbo.ParseAccountNo(@AccountNo) is a scalar user defined function that returns something like this

'And AccountNo LIKE '%A5R7'

for single account no

or

'And (AccountNo Like '%07A11' OR Account LIKE '%13866')'

for multiple account nos.

rh4m1ll3

here's the solution with the function that i've mentioned a while ago

you can get the Split Function here

Code Snippet

CREATE FUNCTION dbo.ParseAccountNo
(
@AccountNo varchar(800)
)
RETURNS varchar(800)
AS
BEGIN

DECLARE @SelectStmt varchar(800)
SET @SelectStmt = ''

-- for & delimiter
IF (SELECT COUNT(*) FROM dbo.Split(@AccountNo,'&',NULL,NULL)) > 1
BEGIN
SELECT @SelectStmt = @SelectStmt + 'AccountNo LIKE ''%' + RTRIM(LTRIM(CSV)) + ''' OR '
FROM dbo.Split(@AccountNo,'&',NULL,NULL)
END
-- for , delimiter
ELSE IF (SELECT COUNT(*) FROM dbo.Split(@AccountNo,',',NULL,NULL)) > 1
BEGIN
SELECT @SelectStmt = @SelectStmt + 'AccountNo LIKE ''%' + RTRIM(LTRIM(CSV)) + ''' OR '
FROM dbo.Split(@AccountNo,',',NULL,NULL)
END
-- you can insert other delimiter here
--
-- for single account no
ELSE
BEGIN
SELECT @SelectStmt = @SelectStmt + 'AccountNo LIKE ''%' + @AccountNo + ''' OR '
END

IF @SelectStmt <> '' SET @SelectStmt = LEFT(@SelectStmt,LEN(@SelectStmt) - 2)

SET @SelectStmt = 'And (' + LTRIM(RTRIM(@SelectStmt)) + ')'

RETURN @SelectStmt
END

your optimized proc would then be..

INSERT
INTO dbo.New_Cust_Table
SELECT [Name] + '1' AS UserId
, [Name] + '1#' AS Pwd
, dbo.ParseAccountNo(AccountNo) AS SelectStmt
FROM Cust_Table

Select Statement In Table (Valid Expressions Error).

Re: Select Statement In Table (Valid Expressions Error).

Re: Select Statement In Table (Valid Expressions Error).

Re: Select Statement In Table (Valid Expressions Error).