Having trouble with variables and stored proc

[rmchung]

I am trying to create a stored procedure that outputs records to a table. Each day the sproc is run, I would like it to output it to a table name and concatenate the current date to the table name. This is what I wrote, but the error I'm getting is when it's building the SQL select statement. It's not recognizing the @TableName variable when it starts to build the SELECT statement. Any ideas? Thanks.

DECLARE @TableName varchar(50), @SQL varchar(500)

SET @TableName = (convert(varchar(100),'dbUT_open_ar_' + CONVERT(varchar(8),GETDATE(),112)))

SET @SQL = ('SELECT sap_ShipToCustomerID AS ShipToCustomer, sap_BillToCustomerID AS BillToCustomer, sap_InvoiceDate AS InvoiceDate, sap_Amount AS Amount,
sap_ReferenceFrom AS ReferenceFrom INTO ' + @TableName + ' FROM dbo.open_ar WHERE (status_ready_for_upload = ' + '''' + 'Y' + '''')

EXEC @SQL

 

[amrita418]

I am suspecting this piece of code is the problem.

status_ready_for_upload = ' + '''' + 'Y' + ''''

Try this instead:

' FROM dbo.open_ar WHERE (status_ready_for_upload = ''Y'''

Regards,
AA

 

[amrita418]

You will need to get rid of the extra brace in my code after the where clause.

 

[rmchung]

I just tried the new FROM statement and I still get the same error. Here is the error:

Could not locate entry in sysdatabases for database 'SELECT sap_ShipToCustomerID AS ShipToCustomer, sap_BillToCustomerID AS BillToCustomer, sap_InvoiceDate AS InvoiceDate, sap_Amount AS Amount,
sap_ReferenceFrom AS ReferenceFrom INTO dbo'. No entry found with that name. Make sure that the name is entered correctly.

 

[amrita418]

Before running the exec, print @sql and see what you get.

I tried your sample code and it seems to work for me.

DECLARE @TableName varchar(50), 
        @SQL varchar(500)

SET     @TableName = (convert(varchar(100),'dbo.OUT_open_ar_' + CONVERT(varchar(8),GETDATE(),112)))

--print @TableName

execute ('select col1 into ' + @TableName + ' from test where convert(varchar, col1) != ''0.0''')

 

[rmchung]

Ahhh. Figured it out, but don't know exactly why. I put parentheses around the @SQL in the EXEC statement. Thanks for the help.