Syntax problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Syntax problem

Author

Topic

yellowman
Starting Member

25 Posts

Posted - 2012-11-20 : 10:22:25

I have a small piece of code that is kicking off the error:

Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near ')'.

Maybe the Red Bull just hasn't kicked in this morning, but where did I go wrong in the code below? I know the error is on the sp_executesql line somewhere.


EXEC ('INSERT INTO ' +@DB_Name+ '.CQ_DBO.Schema_Version
      (Version_Number)            
       EXEC sp_executesql ' +@command+)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-20 : 11:08:24

the last +-sign...

Too old to Rock'n'Roll too young to die.

yellowman
Starting Member

25 Posts

Posted - 2012-11-20 : 14:16:29

Now I get an error that says: Procedure 'sp_executesql' expects parameter '@statement', which was not supplied

I think I have to throw an N in my statement somewhere.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 14:24:20

Yes, you do. Like shown below. BTW, if possible avoid dynamic SQL - because of security considerations.

EXEC ('INSERT INTO ' +@DB_Name+ '.CQ_DBO.Schema_Version
      (Version_Number)            
       EXEC sp_executesql N''' +@command+'''')

yellowman
Starting Member

25 Posts

Posted - 2012-11-21 : 11:12:32

Looks like I am closer to getting it working. I made the changes above and now it gives me an error that says:

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '%'.

I have to laugh because there is no % on line 3.....or anywhere near line 3. I have a percent being used as a wildcard in my select statement (@command), but if I PRINT that command and run the output directly it works just fine. Here is the entire script.


DECLARE @DB_Name nvarchar(200)
DECLARE @command nvarchar(500) 

DECLARE database_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE UPPER(name) LIKE 'CQ_DataComm_PROD'

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name 
WHILE @@FETCH_STATUS = 0
BEGIN 

-- Create table if it doesn't already exists
EXEC ('USE ' +@DB_Name+ ' IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Schema_Version'')
CREATE TABLE ' +@DB_Name+ '.dbo.schema_version(
	version_number [nchar](20) NULL
) ON [PRIMARY]')

--Setup SELECT statement to get the version number
SELECT @command = 'SELECT SUBSTRING(program,PATINDEX(''%Jumpstart Version%'',program)+18,PATINDEX(''%[^0-9.-]%'', SUBSTRING(program,PATINDEX(''%Jumpstart Version%'',program)+18,8000))-1) AS schema_version
   FROM ' +@DB_Name + '.dbo.scriptdef
   WHERE (program LIKE ''%sub JAT_Version%'')'
   
-- INSERT the schema version into the Schema_Version table
EXEC ('INSERT INTO ' +@DB_Name+ '.dbo.Schema_Version
       (Version_Number)            
         EXEC sp_executesql N''' +@command+'''')
         
--Get the next database in the cursor   
FETCH NEXT FROM database_cursor INTO @DB_Name
END

--Cleanup
CLOSE database_cursor
DEALLOCATE database_cursor

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 11:34:39

Substitute each pair of single quote in your @command statement with four single quotes.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-21 : 11:35:32

The fun of doing dynamic sql. If you change your EXEC statements to PRINT statements you wil begin to see the issue. Here is the output for reference:

USE DWTest IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Schema_Version')
CREATE TABLE DWTest.dbo.schema_version(
	version_number [nchar](20) NULL
) ON [PRIMARY]
INSERT INTO DWTest.dbo.Schema_Version
       (Version_Number)            
         EXEC sp_executesql N'SELECT SUBSTRING(program,PATINDEX('%Jumpstart Version%',program)+18,PATINDEX('%[^0-9.-]%', SUBSTRING(program,PATINDEX('%Jumpstart Version%',program)+18,8000))-1) AS schema_version
   FROM DWTest.dbo.scriptdef
   WHERE (program LIKE '%sub JAT_Version%')'

yellowman
Starting Member

25 Posts

Posted - 2012-11-21 : 13:38:33

That's it! Everything works now. I didn't pick up the quote problem when I initially printed @command to look at it. Thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources