Pass a variable into a stored procedures SELECT command

Question

I have a web page with a link to a stored procedure and I want to pass a variable to the stored procedures select statement. The code so far is -

ALTER procedure [dbo].[RTO]

@Weeknumber int,
@asset nvarchar(50)
AS

Begin 

SELECT @asset
FROM RTO_weeklyanalysis
Where weekNumber = @weeknumber

END

basically the @asset will be the name of the column but this will change depending on what the user selects on the page.

What language etc. are you using for your web page code? Do you have any server-side code so far you can show us to show where you're at? — PulseLab, Jun 11 '14 at 13:15

score 1 · Accepted Answer · answered Jun 11 '14 at 13:17

You will need to use Dynamic sql for this. Also use QuoteName() function when concatenating object names to your sql query. and use system stored procedure sp_executesql to execute the dynamic query the most safe and secure way of executing dynamic sql. Something as follows :

ALTER procedure [dbo].[RTO]
@Weeknumber int,
@asset sysname
AS
Begin 
 SET NOCOUNT ON;
  DECLARE @Sql NVARCHAR(MAX);

SET @Sql = N' SELECT  '+  QUOTENAME(@asset) + '
              FROM RTO_weeklyanalysis
              Where weekNumber = @weeknumber'

EXECUTE sp_executesql @Sql
                     ,N'@Weeknumber int'
                     ,@Weeknumber
END

score 0 · Answer 2 · answered Jun 11 '14 at 13:17

You cannot use a column name dynamicly in this way. The simplest way to achiewe what you want will be to exec whole query from temporary variable, i mean:

declare @query
set @query = 'SELECT ' + @asset  ' FROM RTO_weeklyanalysis Where weekNumber = @weeknumber'
exec sp_executesql @query

Pass a variable into a stored procedures SELECT command

2 Answers2