SQL Server Get a list of @Parameters in a T-SQL script with its datatype

xfb7svmp 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(131)

From a T-SQL script like this:

Select 
    Proveedor.RazonSocial, Articulo.ArticuloId As Código, 
    Articulo.Descripcion As Descripción
From
    Articulo
Inner Join 
    Suministrador On (Articulo.OID = Suministrador.Articulo)
Inner Join 
    Proveedor On (Suministrador.Proveedor = Proveedor.OID)
Where 
    Proveedor.ProveedorId = @CodigoDeProveedor

I would like to get a list containing @CodigoDeProveedor and its datatype inferred from the database table (in this case it's int)

Are there any stored procedures or script I can use to do this?

Clarification:

The SQL is a script, not a Stored Procedure.
I want to parse the parameters and find out their data type so I can mount a GUI screen where I can ask the end user the values they want to apply to the script.
Currently, I am passing the TSQL text and resolving the parameters this way. I just didn't want to reinvent the wheel.

sql-server

来源：https://stackoverflow.com/questions/77140511/get-a-list-of-parameters-in-a-t-sql-script-with-its-datatype

1条答案

按热度按时间

mm5n2pyu1#

You can use the sp_describe_undeclared_parameters system procedure.

DECLARE @query nvarchar(max) = N'
Select 
    Proveedor.RazonSocial, Articulo.ArticuloId As Código, 
    Articulo.Descripcion As Descripción
From
    Articulo
Inner Join 
    Suministrador On (Articulo.OID = Suministrador.Articulo)
Inner Join 
    Proveedor On (Suministrador.Proveedor = Proveedor.OID)
Where 
    Proveedor.ProveedorId = @CodigoDeProveedor
';

EXEC sp_describe_undeclared_parameters @query;

But it can fail in many situations, not least when a parameter is used twice.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Get a list of @Parameters in a T-SQL script with its datatype

1条答案

相关问题

热门标签

最新问答