Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
15.2k views
in Technique[技术] by (71.8m points)

sql - Syntax error when using input parameter to create procedure

I'm getting this error

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 33, column: 15: Incorrect syntax near '@sas_token'

For reference here are the guides I am following:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/quickstart-bulk-load-copy-tsql-examples#b-shared-access-signatures-sas-with-crlf-as-the-row-terminator-windows-style-new-line

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-stored-procedures#stored-procedures-in-synapse-sql

I tried the commented part because I thought it didn't recognize the quotes but it still didn't work

CREATE PROCEDURE dbo.bulk_load 
    @sas_token VARCHAR(500)
AS
BEGIN
    --DECLARE @sas varchar(500)
    --SET @sas = ''''+ @sas_token + ''''
    IF OBJECT_ID('dbo.ITEM_INFORMATION') IS NOT NULL
        DROP TABLE dbo.ITEM_INFORMATION;

    CREATE TABLE dbo.ITEM_INFORMATION 
    (
        model VARCHAR(20),
        col1 VARCHAR(15),
        col2 VARCHAR(3),
        col3 VARCHAR(255)
    );

    COPY INTO dbo.ITEM_INFORMATION
    FROM 'https://server.blob.core.windows.net/container/subfolder/file.csv' 
         WITH (FILE_TYPE = 'CSV',
               FIELDTERMINATOR = ',',
               FIRSTROW = 2,
               ROWTERMINATOR = '
',
               CREDENTIAL = (
                             IDENTITY = 'Shared Access Signature',
                             SECRET = @sas_token --@sas also tried '''' + @sas_token + ''''
                            )
            );
END

This procedure will be called from Python like so:

def update_database(sas_token):

    #Runs the stored procedure to load the data from Azure storage to table.
    #Uses the password stored in user environment variable.
    
    server = 'server.database.windows.net'
    database = 'database'
    username = 'username'
    if 'server_password' not in os.environ:
        print("Missing environment variable 'server_password' containing database password.")
    password = os.getenv('server_password') 
    driver= '{ODBC Driver 17 for SQL Server}'
    sql = f'EXEC dbo.bulk_load, {sas_token};'
    connection = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
    connection.autocommit = True
    connection.execute(sql)
    connection.close()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...