February 19, 2004 at 3:06 pm
Sorry... I'm still working my way through The Guru's Guide to Transact SQL and I'm stuck on page 218. What' wrong with this query?
SET QUOTED_IDENTIFIER ON -- Added by me in attempt to get this to work
DECLARE @arrayvar varchar(8000),
@select_stmnt varchar(8000)
DECLARE @k int,
@i int,
@l int,
@c int
SET @select_stmnt = 'SELECT '
SET @C = 0
DECLARE c CURSOR FOR
SELECT arraycol
FROM Array
OPEN c
FETCH c INTO @ArrayVar
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @i = 0
SET @l = DATALENGTH(@ArrayVar)/16 -- length of data element within array
WHILE (@i < @l) BEGIN
SELECT @select_stmnt = @select_stmnt
+ 'Guitarist'
+ CAST(@c as varchar)
+ ' = '
+ QUOTENAME(RTRIM(SUBSTRING(@arrayvar,(@i * 16) + 1, 16)),'"')
+ ','
SET @i = @i + 1
END
FETCH c INTO @k, @arrayvar
END
CLOSE c
DEALLOCATE c
SELECT @select_stmnt = LEFT(@select_stmnt,DATALENGTH(@select_stmnt)-1)
EXEC @select_stmnt
SET QUOTED_IDENTIFIER OFF -- (added by me in attempt to make it work)
==============================
As it is, it produces the following error message:
Server: Msg 16924, Level 16, State 1, Line 35
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
Server: Msg 2812, Level 16, State 62, Line 43
Could not find stored procedure 'SELECT Guitarist0 = 'Les Paul',Guitarist1 = 'Buddy Guy',Guitarist2 = 'Jeff Beck',Guitarist3 = 'Joe Satriani''.
==============================
I'm pretty certain the error centers around the QUOTENAME line, but none of my various attempts to adjust the code produces a working query.
Could someone explain what I'm doing wrong or missing? Thanks in advance for your assistance.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
February 19, 2004 at 4:29 pm
February 19, 2004 at 6:14 pm
Actually, I believe the comma should be present. Note the error message.
The Results set should read something like:
Guitarist0 Guitarist1 Guitarist3...
================ ================= ================
Les Paul Buddy Guy Jeff Beck...
So, I think the comma in the dynamic SQL concatenation is correct.
Any other ideas?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
February 19, 2004 at 6:51 pm
In your cursor you declared one column in your select list. Then you use the FETCH command to try and get the cursor to fill two variables with values from columns in the cursor when only one value is available.
Change your FETCH statement from:
FETCH c INTO @k, @arrayvar
To:
FETCH c INTO @arrayvar
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 20, 2004 at 9:41 am
That catch helped a lot! I'm almost there.
Here's what the query looks like now--with a line of debugging code added:
SET QUOTED_IDENTIFIER ON
DECLARE @arrayvar varchar(8000),
@select_stmnt varchar(8000)
DECLARE @k int,
@i int,
@l int,
@c int
SET @select_stmnt = 'SELECT '
SET @C = 0
DECLARE c CURSOR FOR
SELECT arraycol
FROM Array
OPEN c
FETCH c INTO @ArrayVar
WHILE (@@FETCH_STATUS = 0) BEGIN
SET @i = 0
SET @l = DATALENGTH(@ArrayVar)/16 -- length of data element within array
WHILE (@i < @l) BEGIN
SELECT @select_stmnt = @select_stmnt
+ 'Guitarist'
+ CAST(@c as varchar)
+ ' = '
+ QUOTENAME(RTRIM(SUBSTRING(@arrayvar,(@i * 16) + 1, 16)),'"')
+ ','
PRINT @Select_Stmnt -- Inserted for DEBUGGING purposes
SET @i = @i + 1
END
FETCH c INTO @arrayvar
END
CLOSE c
DEALLOCATE c
SELECT @select_stmnt = LEFT(@select_stmnt,DATALENGTH(@select_stmnt)-1)
EXEC @select_stmnt
SET QUOTED_IDENTIFIER OFF
=================
Here's the messages printed when the script is run (you can see the dynamic SELECt statement building through the iterations of the cursor):
SELECT Guitarist0 = "Les Paul",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",Guitarist9 = "Slash",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",Guitarist9 = "Slash",Guitarist10 = "Jimi Hendrix",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",Guitarist9 = "Slash",Guitarist10 = "Jimi Hendrix",Guitarist11 = "Jason Becker",
SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",Guitarist9 = "Slash",Guitarist10 = "Jimi Hendrix",Guitarist11 = "Jason Becker",Guitarist12 = "Michael Hartman",
Server: Msg 203, Level 16, State 2, Line 46
The name 'SELECT Guitarist0 = "Les Paul",Guitarist1 = "Buddy Guy",Guitarist2 = "Jeff Beck",Guitarist3 = "Joe Satriani",Guitarist4 = "Steve Miller",Guitarist5 = "Eddie Van Halen",Guitarist6 = "Tom Scholz",Guitarist7 = "Steve Vai",Guitarist8 = "Eric Clapton",Guitarist9 = "Slash",Guitarist10 = "Jimi Hendrix",Guitarist11 = "Jason Becker",Guitarist12 = "Michael Hartman"' is not a valid identifier.
======================
Note the error in the last line of the mssages. Something still must be amiss in my query. But where?
If I copy the final resulting SELECT statement and run it manually, it produces the proper result, but when EXEC runs it, it seems to be looking at it as an Identifer rather than a SQL statement.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
February 20, 2004 at 9:49 am
Sorry I didn't catch this the first time around.
You need to change the line that reads:
EXEC @select_stmnt
to:
EXEC (@select_stmnt)
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 20, 2004 at 9:53 am
You're right! I discovered this and was just coming back to let everyone know I had found the problem.
Now, can you tell me WHY the (...) make a difference in the EXEC statement?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
February 20, 2004 at 10:05 am
Because without the parenthesis, SQL Server thinks you are providing the name of a stored procedure and it can't find one with that name.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 20, 2004 at 10:14 am
Thanks everyone for the help. I'm learning!
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply