CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
hmahdavi
hmahdavi

Reputation: 2354

Exec stored procedure with two string parameters

I wrote this procedure and defined two strings as parameters for it :

CREATE PROCEDURE [dbo].[spo_SelectTable1sDynamic]
    @WhereCondition nvarchar(500),
    @OrderByExpression nvarchar(250) = NULL
AS
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    DECLARE @SQL nvarchar(3250)

    SET @SQL = 'SELECT
                    [col1], [col2], [col3], [col4]
                FROM
                    [dbo].[Table1]
                WHERE
               ' + @WhereCondition

    IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
    BEGIN
        SET @SQL = @SQL + 'ORDER BY ' + @OrderByExpression
    END

    EXEC sp_executesql @SQL

I should pass two strings for @whereconditio and @OrderByExpression parameters.

How can I execute this stored procedure? I use this code but get an error:

EXECUTE spo_SelectTable1sDynamic N'col1='book'' , N'col1 '

Upvotes: 1

Views: 2551

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

First of all your code is very vulnerable to SQL Injection attack.

Second SELECT * FROM tbl WHERE @condition is curse:

If you are considering to write the procedure

CREATE PROCEDURE search_sp @condition varchar(8000) AS    
SELECT * FROM tbl WHERE @condition

Just forget it. If you are doing this, you have not completed the transition to use stored procedure and you are still assembling your SQL code in the client.

Possible solution is to use Dynamic Search Conditions and avoid dynamic-SQL at all:

CREATE PROCEDURE [dbo].[spo_SelectTable1sDynamic]
    @col1 NVARCHAR(1000),   -- depending on business cases
    @col2 INT ...,
    @sortColumn SYSNAME
AS
BEGIN
  SELECT ....
  FROM table ...
  WHERE (col1 = @col1 OR @col1 IS NULL)
    AND (col2 = @col2 OR @col2 IS NULL) ...
  ORDER BY
     CASE @sortColumn
          WHEN 'col1' THEN col1 
          WHEN 'col2' THEN col2
          ...
     END
END

User can call it as:

EXEC [dbo].[spo_SelectTable1sDynamic] @col1 = 'aaa'
EXEC [dbo].[spo_SelectTable1sDynamic] @col2 = 10
EXEC [dbo].[spo_SelectTable1sDynamic] @col1 = 'bbb', @col2 = 16
-- or even
EXEC [dbo].[spo_SelectTable1sDynamic
-- to get all possible rows without any filter

Upvotes: 3

Related Questions