1

I am creating a stored procedure to find tables available in a database in Microsoft Parallel Datawarehouse using SSMS 14 with two inputs parameters, name of table and schema.

I tried this syntax for the SQL statement:

ALTER PROCEDURE MY_SCHEMA.FIND 
    (@TABLE VARCHAR(50) = NULL,
     @Schema VARCHAR(50) = null)
AS
BEGIN
    SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME LIKE '%' + @TABLE + '%' 
      AND TABLE_SCHEMA LIKE '%' + @Schema + '%'
END

But I got this error:

CREATE or ALTER PROCEDURE statement uses syntax or features that are not supported in SQL Server PDW.

Please suggest the correct syntax to use

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vinayak
  • 11
  • 1
  • 3
  • [You cannot default the values of stored procedures in SQL Data Warehouse](https://social.msdn.microsoft.com/Forums/en-US/b735b296-d9c3-4c64-8313-ae01dbad515c/null-parameter-for-stored-procedure?forum=AzureSQLDataWarehouse) – Arulkumar Apr 26 '19 at 10:18
  • Why would you want the value to be `NULL` anyway? `'%' + NULL + '%' = NULL`, which would therefore mean that if `@TABLE` or `@Schema` had a value of `NULL` no rows would be returned. – Thom A Apr 26 '19 at 10:20
  • Larnu, Thank you for reply. to answer your question , there are two scenarios for search 1) user executing Stored procedure know both Table name and Schema name 2) user executing stored procedure know only table name. In second scenarios user won't be able to pass second Parameter required for stored procedure to RUN. so I want to create procedure which can be executed 1) without passing any parameter 2) passing 1 parameter 3) passing both parameter – vinayak Apr 26 '19 at 12:45
  • @vinayak, I created your SP and it works perfectly on my machine - I have SSMS v17 on SQL Server 1014 Standard... – Attie Wagner Apr 26 '19 at 13:51

1 Answers1

1

This feature is not available in Azure Synapse Analytics. You can give a feedback on this to Azure feedback forum. Azure feedback - SP to accept null params

Roopkumar Akubathini
  • 29
  • 3