Sql-server – Dynamic Select with error in where conditon

dynamic-sqlsql server

@P_SqLCommand NVARCHAR(100) OUTPUT, --output which will be return
    @P_SqlList NVARCHAR(30),-- columns that needs to be filtered out
    @P_Table NVARCHAR(30),--Table Name needs to be use in the select statment
    @P_Input NVARCHAR(50),--the filter variable
    @P_Num varchar(5)
SET @P_Input='@P_Input'

    SET @P_SqLCommand='SELECT Top '+ @P_Num +' '+@P_SqlList+'  FROM '+
    @P_TABLE +' where '+ @P_SqlList + '=' + @P_Input;

give me an error:

Incorrect syntax near '='.

Any suggestions ?

I am using SQL Server. What am trying to do is to use stored procedure to for dynamic select statement and pass through by parameters, it is work fine till the where statement.

How I can treat @P_Input as character?

Best Answer

Whatever value of @P_SqlList the final WHERE sentence will be something like:

WHERE [SOME_FIELD] = @P_Input

due you are setting it here:

SET @P_Input='@P_Input'

To solve this, add @P_Inputto the parameters list. Or, build the whole filter outside the dynamic query and pass it as a single value:

WHERE ' + @Filter

This form allow you to build a more complicated WHERE sentence.

Related Solutions

Sql-server – sp_executesql adds statements to executed dynamic script

Prior to the sp_executesql you should see an sp_prepare statement in Profiler that would give more insight: http://msdn.microsoft.com/en-us/library/ff848808(v=sql.110).aspx

Sometimes this can be hard to find, but was made a bit easier with extended events in 2008R2 - specifically causality tracking.

http://msdn.microsoft.com/en-us/library/bb630284.aspx

Sql-server – Can’t cast a stored procedure parameter

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14

Best Answer

Related Solutions

Sql-server – sp_executesql adds statements to executed dynamic script

Sql-server – Can’t cast a stored procedure parameter

Related Question