0

Here is a simple procedure to get column data type eg. smallint.

CREATE PROCEDURE GetDataType
    @table_name nvarchar(30),
    @column_name nvarchar(30)
AS
    RETURN 
        SELECT data_type AS 'Data Type' 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @table_name 
          AND COLUMN_NAME = @column_name
GO

EXEC GetDataType @table_name = 'Zawody', @column_name = 'ID_TOR';

I am trying to dynamically assign variable's value, but the code shown returns an error:

@ID_TOR EXEC GetDataType @table_name = 'Zawody', @column_name = 'ID_TOR',

How can I solve it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adrian Wiśniewski
  • 1
  • 3
    Stored procedures should return values via parameters. Although they can return something, that is always an integer and should be treated as a status. – Gordon Linoff Jun 03 '20 at 18:37
  • 2
    Suggested reading: [Return Data from a Stored Procedure](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15). The [`return`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15) statement only returns an `int`, typically used for a completion status. – HABO Jun 03 '20 at 19:04
  • 1
    Depending on how you're going to use the datatype, you may find that what you have so far is insufficient since you won't be returning the length of (n)(var)chars or the precision and scale of decimal/numerics. You might want to take a step back and talk about what you're trying accomplish with the sproc in the first place. – Eric Brandt Jun 03 '20 at 19:32
  • Also, you're variable assignment is using invalid syntax. Have a look at [How to assign an exec result to a sql variable?](https://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable) – Eric Brandt Jun 03 '20 at 19:42

0 Answers0