Making query in stored procedure in SQL Server but some error occurs

Question

My stored procedure looks like this:

Alter PROCEDURE [dbo].[productfilter_whatsnew]
   @query1 nvarchar(max),
   @query2 nvarchar(max),
   @date date,
   @pid varchar(5)
AS
   select * 
   FROM Productcolorimage pci  
   where entry_date > @date 
     and selectproduct = @pid + ' ' + @query1 
   order by @'@query2'
GO

My function passing value

public DataTable productfilter_whatsnew(ProductBAL objbal)
{
    DataTable dt = new DataTable();

    using (SqlConnection conn = new SqlConnection(strconn))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("productfilter_whatsnew", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter[] para = {
                               new SqlParameter("@query1","and colorid in('3')"),
                                new SqlParameter("@query2","pid ASC"),
                                new SqlParameter("@date","14-09-2013"),
                                new SqlParameter("@pid","1")
                              };
        try
        {
            cmd.Parameters.AddRange(para);
            SqlDataAdapter d = new SqlDataAdapter(cmd);
            d.Fill(dt);
            conn.Close();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
    return dt;
}

But showing different errors for placing @query1 and @query2...

@rene no idea to do so?

Mitesh Jain
– Mitesh Jain

2013-09-14 11:48:40 +00:00
Commented Sep 14, 2013 at 11:48 — Mitesh Jain
– Mitesh Jain, Commented Sep 14, 2013 at 11:48

roman · Accepted Answer · 2013-09-14 12:01:22Z

2

If you really sure you want to do this, use dynamic SQL. But be sure to read about SQL Injection first.

alter procedure [dbo].[productfilter_whatsnew]
(
    @query1 nvarchar(max),
    @query2 nvarchar(max),
    @date date,
    @pid varchar(5)
)
as
begin
    declare @stmt nvarchar(max)

    select @stmt= '
        select * 
        from Productcolorimage as pci
        where entry_date > @date and selectproduct = @pid'

    select @stmt = @stmt + ' ' + @query1
    select @stmt = @stmt + ' order by ' + @query2

    -- passing parameters into sp by names, easier to maintain in the future
    exec sp_executesql
        @stmt = @stmt,
        @params = N'@date date, @pid varchar(5)',
        @date = @date,
        @pid = @pid;
end

answered Sep 14, 2013 at 12:01

roman

118k30 gold badges205 silver badges209 bronze badges

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

Comments

rene · Accepted Answer · 2013-09-14 12:10:05Z

1

If you really need to you can change your procedure to call sp_executesql. Are you sure you can't use Entity Framework or NHibernate or any other ORM library ?

Alter PROCEDURE [dbo].[productfilter_whatsnew]
@query1 nvarchar(max),
@query2 nvarchar(max),
@date date,
@pid varchar(5)
AS
BEGIN
    declare @sql nvarchar(1000)
    declare @ParmDefinition nvarchr(1000)
    SET @ParmDefinition = N'@date date, @pid varchar(5)';

    set @sql = N'select * FROM Productcolorimage pci  where entry_date>@date and selectproduct=@pid ' + @query1 + ' order by ' + @query2

    EXECUTE sp_executesql @sql, @ParmDefinition, @date=@date, @pid=@pid;
END
GO

edited Sep 14, 2013 at 12:10

answered Sep 14, 2013 at 11:53

rene

42.6k78 gold badges122 silver badges166 bronze badges

3 Comments

roman Over a year ago

have you forgot to define and pass @params parameter?

Mitesh Jain Over a year ago

error is coming "Must declare the scalar variable "@SQLString"."

Mitesh Jain Over a year ago

how to fix this prioblem??

Collectives™ on Stack Overflow

Making query in stored procedure in SQL Server but some error occurs

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related