Using stored procedure parameters to return columns

Question

I'm trying to write a stored procedure that will return a specific value base on two input parameters. The issue is trying to make one of the parameters refer to a column.

Here's my table:

CityID      CityName             London                               Paris                                   
----------- -------------------- --------------------------------------- --
1           London               0                                       60                                      
2           Paris                50                                      0                                       
3           Berlin               90                                      60                                      
4           Madrid               150                                     90                                      
5           Dublin               30                                      80                                      
6           Rome                 75                                      88

Is it possible to write a stored procedure along the lines of:

CREATE PROCEDURE Price (@city1, @city2)
AS
    SELECT @city2 
    FROM dbo.MyTable
    WHERE CityName = @city1

What you're describing is dynamic SQL. You want to pass in the name of a column and then return the value of that column for the matching city. Right? — Yuck
– Yuck, Commented May 1, 2017 at 18:24
This question can't accurately be answered without knowing the RDBMS you're using. Seems like MS SQL but please confirm along with the version. — Yuck
– Yuck, Commented May 1, 2017 at 18:25

Yuck · Accepted Answer · 2017-05-01 18:36:04Z

1

I'd do something this like which verifies the existence of the column name before trying to run the dynamic SQL statement:

create procedure dbo.GetPrice(@city nvarchar(50), @column nvarchar(200)) as
begin

if (not exists (select *
                from sys.columns
                where object_id = object_id('dbo.MyTable') and name = @column))
    return;
else begin
    declare @sql nvarchar(max) =
        'SELECT [' + @column + '] FROM dbo.MyTable WHERE CityName = ''' + @city + '''';

    execute sp_ExecuteSQL @sql;
end;

end;

answered May 1, 2017 at 18:36

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ger Mc Over a year ago

I'm getting ''Error converting data type varchar to numeric.'' Checking the existence of the column isn't necessary for me at the moment.

Yuck Over a year ago

I just tested this locally and it's working fine when I run execute dbo.GetPrice 'test', 'London';

Yuck Over a year ago

Checking the existence of the column isn't necessary for me at the moment. It is necessary if you want to avoid people running arbitrary SQL statements.

Kevin · Accepted Answer · 2017-05-01 18:26:20Z

0

You will have to make use of dynamic sql.

CREATE PROC Price (@City1, @City2)
AS 
BEGIN
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'SELECT ' + @City2 + ' FROM dbo.MyTable WHERE CityName = ''' + 
@City1 +  ''''

EXEC sp_execute @Sql
END

answered May 1, 2017 at 18:26

Kevin

7816 silver badges13 bronze badges

1 Comment

Yuck Over a year ago

This doesn't parse. Not only that, but what do you plan to do if someone passes @City2 = '0; drop table users; select null'

Neha · Accepted Answer · 2017-05-01 18:30:21Z

0

Create PROCEDURE [dbo].[USP_Price] @city1 Varchar, ------------------------------------------------ @City2 VARCHAR(MAX) = NULL OUT

AS BEGIN

 SELECT @city2 
FROM dbo.MyTable
WHERE CityName = @city1

END

--- Create the stored procedure, and if you are using the sql server, right click on the stored procedure and click execute stored procedure enter the city1 value, while select null for the city 2 and execute

answered May 1, 2017 at 18:30

Neha

1971 silver badge12 bronze badges

1 Comment

Neha Over a year ago

Try to use the above query to create a stored procedure

Collectives™ on Stack Overflow

Using stored procedure parameters to return columns

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related