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

Answer 1

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

Answer 2

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.

Answer 3

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