Select Statement In Table
-
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 '+@LGXuser
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.
I know I will get some answers that just get rid of my cursors (I am still living in a cursor world, so please, bring it on so I can learn!!!
).Does anyone have any ideas or solutions?
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
-
You asked for non-cursor. Try this (untested). If not 100%, should give you a start
INSERT INTO New_Cust_Table (UserId, Password, SelectStmt)
SELECT UserID, UserID + '1#',
'AND Account Like ''%''' + REPLACE(REPLACE(AccountNo,'& ', 'OR Account LIKE ''%'),',','OR Account LIKE ''%') + ''''
FROM Cust_Table
Your problem is with not escaping quotes. Try replacing the line that sets the statement with the one below.
SET @Statement = N'
INSERT INTO [dbo].[New_Cust_Table]
VALUES (''' + @userid + '1'',''' + @userid + '1#'',''' + REPLACE(@SelectStmt,'''','''''') + ''')'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I found the problem... not enough quote(s) around the statement. The statement I was trying to use was:
SET @SelectStmt = 'AND AccountNo LIKE ''%'+@SelectStmt+''''
I was so concerned about the format and getting it ready to put into an insert statement, I forgot the insert statement also needed double quote(s) to convert it into single quotes when it was finally in the table. So my statement now looks like:
SET @SelectStmt = 'AND AccountNo LIKE ''''%'+@SelectStmt+''''''
There are two additional single quote(s) on either side of the %+@SelectStmt+
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply