Stored procedure with string parameters, how to get it work?

Question

I am trying to write a stored procedure which is taking two arguments to retrieve some data from customers table.

Below is the stored procedure, it does not retrieve any data, however when I just type the select query, it works.

Can somebody help me to see where is the problem?

CREATE PROCEDURE [dbo].[RecordsByColumnSearch]
    @field VARCHAR(50),
    @search VARCHAR(50)
AS
    SELECT *
    FROM Customers
    WHERE @field = @search

Executing this stored procedure like this:

EXEC dbo.RecordsByColumnSearch @field = CustomerID, @search = ALFKI;

does not return any data, while running this query does:

SELECT * 
FROM customers 
WHERE CustomerID = 'ALFKI';

Thank you in advance !

Tag your question with the database you are using (which appears to be SQL Server). — Gordon Linoff, Jun 12 '18 at 01:31

Gordon Linoff · Answer 1 · 2018-06-12T01:56:49.360

6

You can't do what you want using regular SQL. You need dynamic SQL. I would recommend:

CREATE PROCEDURE [dbo].[RecordsByColumnSearch] (
    @field Varchar(50),
    @search Varchar(50)
) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
select c.*
from Customers c
Where @field = @search
';

    SET @sql = REPLACE(@sql, '@field', @field);
    EXEC sp_executesql @sql, N'@field nvarchar(50)', @search=@search;

END;

sp_executesql executes SQL statements and allows you to pass in parameters. That is the best way to pass parameters in. Unfortunately, that doesn't work for column names, so they still need to be passed in by munging the query string.

edited Jun 12 '18 at 01:56

answered Jun 12 '18 at 01:33

Gordon Linoff

1,242,037
58
646
786

Thanks a lot Gordon, however when I execute, it gives me an error – Zoe Jun 12 '18 at 01:51
Must declare the scalar variable "@search". – Zoe Jun 12 '18 at 01:51
@Zoe . . . That was a silly error. – Gordon Linoff Jun 12 '18 at 01:56
@GordonLinoff just a dummy question, let's say he is using SQL server, why can't he just write normal SP, why do he need to create the `@sql` – Jacky Jun 12 '18 at 02:16
@Jacky . . . Because the OP wants to pass in a column name. You need dynamic SQL to do that. – Gordon Linoff Jun 12 '18 at 02:17
@GordonLinoff Oh i see, i didn't realize he is passing a column name with `@field` Thank you – Jacky Jun 12 '18 at 02:48

Sarath · Answer 2 · 2018-06-12T13:56:56.537

0

declare @field varchar(40)
declare @search varchar(40)

declare @sql nvarchar(max)

set @sql = 'select c.* from Customers c where '+@field+' = '''+@search +''''

exec sp_executesql @sql

For Using Stored Procedure

create proc recordsbycoulmnsearch
@field varchar(50),
@search varchar(50)
as
begin
declare @sql nvarchar(max)

set @sql = 'select c.* from customer c where '+@field+' = '''+@search+''''

exec sp_executesql @sql

end

exec recordsbycoulmnsearch 'Columnname','searchid'

Try this dynamic query..

edited Jun 12 '18 at 13:56

answered Jun 12 '18 at 09:50

Sarath

61
5

While this works, it leaves open the possibility of SQL injection. Using sp_executesql with the proper parameters will avoid that. – EzLo Jun 12 '18 at 14:17

Stored procedure with string parameters, how to get it work?

2 Answers2