CODEWITHSUNDEEP
sql-serverstored-procedures
Sukhjeevan
Sukhjeevan

Reputation: 3156

Error in Creating stored procedure

Anybody tell me what's wrong with creating this stored procedure.

CREATE PROC ImportData
AS
BEGIN

    DECLARE @DatabasePath VARCHAR(MAX)
    SET @DatabasePath = 'E:\ABC.xls'

    DECLARE @sql      nvarchar(MAX)
    SET @sql = '
    INSERT INTO [dbo].[Table_1]
    SELECT  *
    FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
            ''Excel 8.0;Database=' + @DatabasePath + ',
            ''SELECT * FROM [Sheet1$]'') AS xlsTable'

    EXEC sp_executesql @sql
    GO

END

ERROR:-
Incorrect syntax near '@sql'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'END'.

Upvotes: 1

Views: 158

Answers (2)

Phil Hunt
Phil Hunt

Reputation: 8521

You cannot have a batch terminator (GO) in the body of a stored procedure.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166326

Remove the GO from within the Stored Procedure

Something like

CREATE PROC ImportData 
AS 
BEGIN 

    DECLARE @DatabasePath VARCHAR(MAX) 
    SET @DatabasePath = 'E:\ABC.xls' 

    DECLARE @sql      nvarchar(MAX) 
    SET @sql = ' 
    INSERT INTO [dbo].[Table_1] 
    SELECT  * 
    FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
            ''Excel 8.0;Database=' + @DatabasePath + ', 
            ''SELECT * FROM [Sheet1$]'') AS xlsTable' 

    EXEC sp_executesql @sql 

END

Upvotes: 2

Related Questions