How to pass parameters to stored procedures when using dynamic sql on SQLServer 2012?

Question

I am trying to modify the views in my database based on the input parameters. This procedure is to be executed using entity framework. The Error : Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Code for Creating stored procedure using Dynamic SQL

Alter Procedure usp_Procedure_No
(
 @value VARCHAR(255),
 @constraint VARCHAR(255) = NULL
 )
AS
BEGIN
    EXEC sp_executesql @value, @constraint, N'
    If @constraint = ''Gender'' 
        BEGIN
            alter View DupView 
            as
            Select * from Personalities where Gender != @value
        END

    If @constraint = ''Place''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where Place != @value
        END     

    If @constraint = ''MaritalStatus''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where MaritalStatus != @value
        END     

    If @constraint = ''Age''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where PersonalityAge != @value
        END     

    If @constraint = ''Nationality''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where Nationality != @value
        END     

    If @constraint = NULL
        BEGIN 

            alter View DupView
            as
            Select * from Personalities where Characterstics1 != @value OR Characterstics2!= @value OR Characterstics3 != @value
        END
        '
END

I don't think this is a good pattern. A better pattern is 'optional everything'. It can suffer from parameter sniffing but there are ways around that. It's bad idea to manipulate objects from runtime code — Nick.Mc
– Nick.Mc, Commented Apr 19, 2018 at 6:53
and an even worse idea to embed hard coded values in them dynamically. What happens when two people run this at the same time. Don't do it. — Nick.Mc
– Nick.Mc, Commented Apr 19, 2018 at 6:55
Then, Could you suggest a better way to reduce the number of records in a table based on conditions without losing the original data. This process is to find a particular record from the table. — srinivas muralidharan
– srinivas muralidharan, Commented Apr 19, 2018 at 7:11
The way you do that is.. use a WHERE clause. You are doing much more than that. But why? If you are using entity framework just do it in your application code using Find! — Nick.Mc
– Nick.Mc, Commented Apr 19, 2018 at 8:03

M.Ali · Accepted Answer · 2018-04-19 07:27:56Z

2

You have the order wrong for your parameters.

For the system stored procedure sp_executesql, the SQL Statement is the first parameter. Then the variables declaration, and finally you pass values to the variables used in your dynamic sql.

Something like this.

Alter Procedure usp_Procedure_No
 @value VARCHAR(255),
 @constraint VARCHAR(255) = NULL
AS
BEGIN
  SET NOCOUNT ON;

   Declare @Sql NVARCHAR(MAX);

  SET @Sql = N'
    If @constraint = ''Gender'' 
        BEGIN
            alter View DupView 
            as
            Select * from Personalities where Gender != @value
        END

    If @constraint = ''Place''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where Place != @value
        END     

    If @constraint = ''MaritalStatus''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where MaritalStatus != @value
        END     

    If @constraint = ''Age''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where PersonalityAge != @value
        END     

    If @constraint = ''Nationality''
        BEGIN
            alter View DupView
            as
            Select * from Personalities where Nationality != @value
        END     

    If @constraint = NULL
        BEGIN 

            alter View DupView
            as
            Select * from Personalities where Characterstics1 != @value OR Characterstics2!= @value OR Characterstics3 != @value
        END
        '

    EXEC sp_executesql  @Sql
                      , N'@constraint VARCHAR(255) , @value VARCHAR(255)'   
                      , @value = @value
                      , @constraint = @constraint;
END

edited Apr 19, 2018 at 7:27

answered Apr 19, 2018 at 7:22

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

3 Comments

srinivas muralidharan Over a year ago

I could create the stored procedure, but How am I supposed to execute it?

M.Ali Over a year ago

Exec usp_Procedure_No @value = 'Some Value' , @constraint = 'Some Constraint'

srinivas muralidharan Over a year ago

I tried executing that , but I found this error : Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'View'. Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'View'. Msg 156, Level 15, State 1, Line 18 Incorrect syntax near the keyword 'View'. Msg 156, Level 15, State 1, Line 25 Incorrect syntax near the keyword 'View'. Msg 156, Level 15, State 1, Line 32 Incorrect syntax near the keyword 'View'. Msg 156, Level 15, State 1, Line 40 Incorrect syntax near the keyword 'View'.

Nick.Mc · Accepted Answer · 2018-04-19 08:02:10Z

0

This is the "optional everything" pattern.

CREATE PROC MyProc 
   @Gender CHAR(1) = NULL, 
   @Place VARCHAR(100) = NULL, 
   @Age INT = NULL
AS
BEGIN
  SET NOCOUNT ON    

  SELECT * FROM MyTable
  WHERE (Gender = @Gender OR @Gender IS NULL) 
  AND   (Place = @Place OR @Place IS NULL) 
  AND   (Place = @Age OR @Age IS NULL) 
END

Usage:

-- This returns all people who are Male
EXEC MyProc @Gender = 'M'

-- This returns all people who are 23
EXEC MyProc @Age = 'M'

-- This returns all people who are Male and 20
EXEC MyProc @Gender = 'M', @Place = NULL, @Age = 20

Passing a value of NULL (or not defining it) into any parameter means don't filter on it.

This is a stored procedure, not a view. You need to call it differently to a view.

This can suffer from parameter sniffing issues, but it's much better than dynamically changing views to user hard coded parameters.

Is that what you are trying to do? if not, please explain.

answered Apr 19, 2018 at 8:02

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

3 Comments

srinivas muralidharan Over a year ago

We're trying to pass the parameters from a linq query which in-turn gets data from other table based on the answers of users for yes/no questions framed by us. Once the view is reduced based on these answers we distinct category Id's from the table and ask questions from the question table which belong to these categories. For example if a user says yes to a question where we ask if your personality is a sportsman, we pass sports as one characteristic and get all records which have sports. Then we further wanna know what sport he/she plays. That's how the it is supposed to work.

Nick.Mc Over a year ago

I don't quite understand but I still don't see a reason to dynamically alter a view and add a hard coded value

Nick.Mc Over a year ago

Whats happens when two people use this simultaneously? They see the same view. They both see whoever altered the view last. I don't know where you got this crazy idea of altering views when you just need to use standard parameterisation, or just a basic where clause in your LINQ. What you're doing is a terrible idea. Why can't you just apply this where clause in your DAL (LINQ I assume)

cretalex · Accepted Answer · 2019-10-03 06:50:01Z

0

I realized this task for my needs as follows

set nocount on;

   declare @ToDate date = dateadd(month,datediff(month,0,getdate())-1,0)

declare @year varchar(4)  = year(@ToDate)
declare @month varchar(2) = month(@ToDate)

declare @sql nvarchar(max)
    set @sql = N'
        create or alter view dbo.wTempLogs
        as
        select * from dbo.y2019
        where
            year(LogDate) = ''_year_''
            and 
            month(LogDate) = ''_month_''    '

select @sql = replace(replace(@sql,'_year_',@year),'_month_',@month)

execute sp_executesql @sql

declare @errmsg nvarchar(max)
    set @errMsg = @sql
    raiserror (@errMsg, 0,1) with nowait

answered Oct 3, 2019 at 6:50

cretalex

11 bronze badge

Collectives™ on Stack Overflow

How to pass parameters to stored procedures when using dynamic sql on SQLServer 2012?

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related